Oracle View Sequence Test

Oracle View Sequence Test

Get Social!
CREATE SEQUENCE seq_test 
  MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20
/
CREATE OR REPLACE FUNCTION func_seq_test (p_sequence in VARCHAR2) RETURN NUMBER IS
   l_nextval NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_sequence || '.nextval from dual'
         INTO l_nextval;

   RETURN l_nextval;
END;
/
CREATE OR REPLACE VIEW view_seq_test AS 
SELECT   func_seq_test('seq_test') seq_id
,        dummy
FROM     dual
/
SELECT * FROM view_seq_test
/
DROP SEQUENCE seq_test
/
DROP FUNCTION func_seq_test
/
DROP VIEW view_seq_test
/

 


Explain Why An Oracle Database Materialized View Does Not Rewrite

Get Social!

Using Oracle Database materialized views for query rewrite, when used in the right way, can really help aid performance with specific queries. It’s one of my favorite ways to quickly help increase performance of specific queries – often queries issued by front end reporting tools.

Materialized views, however, can be finicky in which queries they re-write, if any and the optimiser output doesn’t give away many clues.

Luckily, help is at hand with a small amount of setup and the DBMS_MVIEW.EXPLAIN_REWRITE package.

Setup

First, create the table to store the output created by the DBMS_MVIEW.EXPLAIN_REWRITE package:

CREATE TABLE REWRITE_TABLE(
  statement_id          VARCHAR2(30),   -- id for the query
  mv_owner              VARCHAR2(30),   -- owner of the MV
  mv_name               VARCHAR2(30),   -- name of the MV
  sequence              INTEGER,        -- sequence no of the msg
  query                 VARCHAR2(2000), -- user query
  query_block_no        INTEGER,        -- block no of the current subquery
  rewritten_txt         VARCHAR2(2000), -- rewritten query
  message               VARCHAR2(512),  -- EXPLAIN_REWRITE msg
  pass                  VARCHAR2(3),    -- rewrite pass no
  mv_in_msg             VARCHAR2(30),   -- MV in current message 
  measure_in_msg        VARCHAR2(30),   -- Measure in current message 
  join_back_tbl         VARCHAR2(30),   -- Join back table in message 
  join_back_col         VARCHAR2(30),   -- Join back column in message
  original_cost         INTEGER,        -- Cost of original query
  rewritten_cost        INTEGER,        -- Cost of rewritten query
  flags                 INTEGER,        -- associated flags
  reserved1             INTEGER,        -- currently not used 
  reerved2              VARCHAR2(10))   -- currently not used;

Rewrite output

Once the output table is in place we can run the DBMS_MVIEW.EXPLAIN_REWRITE package to begin to understand why queries are/ are not being rewritten.

Run the package and substitute the following parameters:

  • arg0 is the query you’re trying to re-write with the materialized view.
  • arg1 is the materialized view itself.
declare
begin  
    DBMS_MVIEW.EXPLAIN_REWRITE (
      query=>q'[SELECT SUM(value) val FROM performance.bill_detail]',
      mv=>'performance_mvs.mv_bills');
end;
/

Finally, view the result

SELECT * FROM REWRITE_TABLE

Oracle Database INTERVAL Partitioning and Virtual Columns

Get Social!

oracle-logoI bumped into this issue recently which prevents you INSERTING a date of 31-12-9999 into an INTERVAL partitioned table due to the fact that the LESS THAN partition value would be larger than an Oracle Date.

After speaking with Jeff Moss he mentioned that we might be able to get round the problem by using a virtual column on the table and partition on that. I didn’t find a satisfactory solution using this method, but understanding some of the optimisers quirks and abilities when using partitions and virtual columns was rather interesting.

Starting with the following table structure:

CREATE TABLE interval_test
(
  data_from_date   DATE NOT NULL
, data_to_date     DATE
, business_key     NUMBER
)
PARTITION BY RANGE (data_to_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

The purpose here is to create a tuple timestamped table for storing historical and current data. The data_from_date represents the date the row is known to be valid from and the data_to_date is when the row is known to changed or been removed entirely from the source system. The current row, the one that is still available in the source system, would not have an ‘end date’ and therefore would either be NULL or a made up ‘high’ date, such as DATE 9999-12-31.

And this is where the problems start.

Winding back a little, and forgetting the high date scenario for a moment, let’s take a look at how queries use the partitions. Generally, queries would use a specific date that’s between the data_from_date and data_to_date to get a view of the data for a point in time.

EXPLAIN PLAN FOR 
SELECT * 
FROM interval_test 
WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/
-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |    14 |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |    14 |1048575|
-----------------------------------------------------------------------------

As you can see, the partitions are being pruned where possible, starting at partition 14. Side note: because this table is INTERVAL partitioned and there is no maximum value set for our data_to_date Oracle may have to look at all the partitions after partition 14. Potentially, this could be all the way up to 1048575 which is the maximum partition number that could exist. It’s a shame the optimiser isn’t a little more intelligent here – I know there aren’t 1048575 partitions and so should the optimiser!

SELECT COUNT(*) FROM dba_tab_partitions WHERE table_name = 'INTERVAL_TEST';

  COUNT(*)
----------
        34

Anyway, back to the matter at hand. Inserting a high date into this table results in an error (described further here).

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

A potential solution to the problem was to add a virtual column to the table that translates the high date into something that would go into a monthly partition. There are a bunch of options, to name a few:

  • TRUNC the date to ‘MM’ and minus 1
  • Minus 31 away from the date
  • TRUNC the date to ‘IQ’ – this would give quarterly partitions and not monthly partitions, but let’s not rule it out.
  • TO_CHAR the date to a YYYYMM, then CAST that as a NUMBER and use numbered partitioning.

I’m not going to explore all of these options and the set up script for you to test further scenarios is here. Note the test SQL will be:

SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date

Truncate the month and minus 1

This was my first test as this would give me the previous months value to partition on which would nicely fit in without causing the ORA-01841 error. The virtual column expression is as follows:

, partition_column AS (TRUNC(data_to_date, 'MM') - 1)

The result wasn’t a success with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Minus 31

Next I simplified the expression by simply minusing 31 days from the date being inserted. This removes the TRUNC ‘complication’ to produce a less complex expression.

, partition_column AS (data_to_date -31)

Again, the result was a failure with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Does it ever work?

Yes! The following expressions work, but as you’ll notice have mixed results in solving our initial problem. It seems as soon as any math operator is specified that the optimiser cannot work out that partition pruning can occur. Using TRUNC however, which to me seems a more complex function, works perfectly. Interestingly when it does work, the filter section of the query plan shows an additional predicate that your query doesn’t specify but that is used to partition prune.

filter("DATA_FROM_DATE"<=TO_DATE(' 2001-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DATA_TO_DATE">=TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"INTERVAL_TEST"."PARTITION_COLUMN">=TRUNC(TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'),'IQ'))

Here are some of the virtual columns that do partition prune however, might not solve the original issue.

, partition_column AS (TRUNC(data_to_date, 'IQ'))

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------
, partition_column AS (TRUNC(data_to_date)) 

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------

 


Oracle Database Interval Partition Set Up Script

Get Social!

This script is used to set up the test table for the following blog posts:

DROP TABLE interval_test PURGE
/
CREATE TABLE interval_test
(
  data_from_date   DATE NOT NULL
, data_to_date     DATE
, business_key     NUMBER
, partition_column AS (TRUNC(data_to_date, 'MM') - 1) -- change this
)
PARTITION BY RANGE (partition_column) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

INSERT INTO interval_test (data_from_date, data_to_date, business_key)
SELECT   DATE '2000-01-01' + (ROWNUM)     data_from_date
,        DATE '2000-01-01' + (ROWNUM + 1) data_to_date
,        ROWNUM                           table_key
FROM     dual
CONNECT BY LEVEL <= 365
/

COMMIT
/
 

BEGIN
	Sys.DBMS_Stats.Gather_Table_Stats(
		    Ownname          => null
		,   Tabname          => 'interval_test'
		,   Estimate_Percent => 100
		,   Degree           => Sys.DBMS_Stats.Auto_Degree
		,   Granularity      => 'AUTO'
		,   Cascade          => Sys.DBMS_Stats.Auto_Cascade
		,   Force            => true
		);
END;
/
 

EXPLAIN PLAN FOR SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/
SELECT plan_table_output FROM TABLE(dbms_xplan.display(format=>'ALL -PROJECTION'))
/

Oracle Database INTERVAL Partitioning Date Error With 31-12-9999

Get Social!

oracle-logoYou may have seen the below error when using dates at the extremes with an Oracle Database. This post focuses on the scenario where you’re inserting a high date, such as 31-12-9999 into an INTERVAL partitioned table. High dates such as this are often used in warehouse environments where from and to dates are specified to denote a period of time the data was valid for – a high date specifying the current data row.

Take the following table and partition definition:

CREATE TABLE interval_date_test
(
  my_date   DATE NOT NULL
)
PARTITION BY RANGE (my_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_date_test0 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

Each row inserted will go into a partition for that year-month combination. This can be useful for high volume data that’s generally queried for one-or-so months at a time.

If, however, you try to insert the following row, you’ll get an error:

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31');
INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31')
            *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

This is due to the way the Oracle Database defines a partition. An INTERVAL RANGE partition in an Oracle Database is defined as accepting dates LESS THAN a specific value. The below example would store any dates up to the clock ticking over into May 2016.

VALUES LESS THAN (TO_DATE('2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘10000-01-01’ and that is not a valid date value. Oracle Database dates must be between the year -4713 and +9999 inclusive as explained by the error.

The solution? Either use a non-INTERVAL partition strategy, reduce the date being INSERTed or experiment with a Virtual Column and partition on that.


Visit our advertisers

Search

Quick Poll

Are you using Docker.io?

Visit our advertisers