Oracle Scratchpad

October 17, 2013

Virtual date partitions

Filed under: Infrastructure,Oracle,Partitioning — Jonathan Lewis @ 7:40 pm BST Oct 17,2013

I posted this question on twitter earlier on today (It was a thought that crossed my mind during a (terrible) presentation on partitioning that I had to sit through a few weeks ago – it’s always possible to be prompted to think of some interesting questions no matter how bad the presentation is, though):

Quiz: if you create a virtual column as trunc(date_col,’W’) and partition on it – will a query on date_col result in partition elimination?

The answer is yes – on the version of Oracle that I happened to have to hand (12c) the next time I had a few minutes spare. Here’s a quick and dirty demo – with data adjusted to the publication date, so you may need to adjust the code to your current date.

drop table transactions purge;

create table transactions (
	transaction_date	date		not null,
	job_id			varchar2(10)	not null,
	account_id		number(8)	not null,
	transaction_type	varchar2(2)	not null,
	transaction_id		varchar2(10)	not null,
	amount			number(10,2)	not null,
	padding			varchar2(100),
	transaction_week 	generated always as (trunc(transaction_date,'IW'))
)
partition by range (transaction_week) interval (numtodsinterval(7,'day')) (
	partition p0 values less than (to_date('30-Sep-2013','dd-mon-yyyy'))
)
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate)-7, 1, 1, 'IN', 1, 1, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate), 2, 2, 'IN', 2, 2, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate) + 7, 3, 3, 'IN', 3, 3, rpad('x',100))
;

begin
	dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'transactions',
                method_opt       => 'for all columns size 1'
	);
end;
/

So transaction_date is a column in the table, but the partitioning column is transaction_week, which is defined as trunc(transaction_date,’IW’) which gives me Mondays as the partition boundaries. (I always go back to the manuals for truncating on weeks – there are at least two options (W and IW) which have very different effects.)

So here are a couple of simple queries – will the optimizer do partition elimination ?


select * from transactions where transaction_date = sysdate;
select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');

And the answer is yes. Note the “partition single”, although in both cases the partition start and stop show KEY/KEY – which means Oracle has to deduce which partitions are relevant at run-time.


-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=SYSDATE@! AND "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(SYSDATE@!,'fmiw'))

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(TO_DATE(' 2013-10-17 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'),'fmiw'))

Pretty clever !

I find it slightly strange that the predicate section reports the derived predicate on the virtual column – it’s clearly not necessary for this example because we can see that the optimizer has already decided that the query will visit just one partition, so and every row in that partition will have the same value for the virtual column. Possibly its presence is just a side effect of the generic strategy used for more complex predicates and the special-case code hasn’t been included, but maybe there’s something I’ve overlooked that makes it necessary.

18 Comments »

  1. Hi Jonathan.
    Pretty clever indeed.
    This behavior is new in 12c (I’ve just tested it in 11.2).
    It reminds me another clever feature in 12c: if we create an index on TRANSACTION_WEEK in your example (with or without the partitioning), and select “where TRANSACTION_DATE = :x”, the index is used:

    select * from transactions where transaction_date = :x;
    
    ------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |                 |     1 |   130 |     2   (0)| 00:00:01 |       |       |
    |*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TRANSACTIONS    |     1 |   130 |     2   (0)| 00:00:01 | ROWID | ROWID |
    |*  2 |   INDEX RANGE SCAN                         | IX_TRANSACTIONS |     1 |       |     1   (0)| 00:00:01 |       |       |
    ------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter("TRANSACTION_DATE"=:X)
       2 - access("TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(:X,'fmiw'))
    

    This is also true if we don’t have the virtual column, and we just create the function based index.

    Thanks,
    Oren.

    Comment by Oren Nakdimon @DBoriented — October 17, 2013 @ 10:06 pm BST Oct 17,2013 | Reply

    • Oren,

      Excellent contribution, thank you very much.

      That may be related to the appearance of the apparently redundant predicate. Perhaps this is an extension of generating new predicates though constraints and transitive closure that gives Oracle the option of finding more optimisation options.

      Comment by Jonathan Lewis — October 17, 2013 @ 10:11 pm BST Oct 17,2013 | Reply

  2. I also tested in in 11.2.0.3 and generated these plans:

    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> select * from transactions where transaction_date = sysdate;
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2739120976
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |              |     1 |   130 |    14   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |    14   (0)| 00:00:01 |   KEY |   KEY |
    |*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |    14   (0)| 00:00:01 |   KEY |   KEY |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("TRANSACTION_DATE"=SYSDATE@! AND "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(SYSDATE@
                  !,'fmiw'))
    
    Note
    -----
       - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             21  consistent gets
              0  physical reads
              0  redo size
            853  bytes sent via SQL*Net to client
            508  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    SQL> select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');
    
    TRANSACTI JOB_ID     ACCOUNT_ID TR TRANSACTIO     AMOUNT
    --------- ---------- ---------- -- ---------- ----------
    PADDING                                                                                              TRANSACTI
    ---------------------------------------------------------------------------------------------------- ---------
    17-OCT-13 2                   2 IN 2                   2
    x                                                                                                    14-OCT-13
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2739120976
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |              |     1 |   130 |    11   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |    11   (0)| 00:00:01 |   KEY |   KEY |
    |*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |    11   (0)| 00:00:01 |   KEY |   KEY |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                  "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd
                  hh24:mi:ss'),'fmiw'))
    
    Note
    -----
       - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
             21  consistent gets
              0  physical reads
              0  redo size
           1170  bytes sent via SQL*Net to client
            519  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>
    
    But if I add a global index to the example I get this:
    
    SQL> select * from transactions where transaction_date = sysdate;
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4197942015
    
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |              |     1 |   130 |     2   (0)| 00:00:01 |    |          |
    |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 | ROWID | ROWID |
    |*  2 |   INDEX RANGE SCAN                 | TXN_DT_IDX   |     1 |       |     1   (0)| 00:00:01 |    |          |
    -------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("TRANSACTION_DATE"=SYSDATE@!)
           filter("TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(SYSDATE@!,'fmiw'))
    
    Note
    -----
       - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              1  consistent gets
              0  physical reads
              0  redo size
            853  bytes sent via SQL*Net to client
            509  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
    
    SQL> select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');
    
    TRANSACTI JOB_ID     ACCOUNT_ID TR TRANSACTIO     AMOUNT
    --------- ---------- ---------- -- ---------- ----------
    PADDING                                                                                              TRANSACTI
    ---------------------------------------------------------------------------------------------------- ---------
    17-OCT-13 2                   2 IN 2                   2
    x                                                                                                    14-OCT-13
    
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4197942015
    
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |              |     1 |   130 |     2   (0)| 00:00:01 |    |          |
    |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 | ROWID | ROWID |
    |*  2 |   INDEX RANGE SCAN                 | TXN_DT_IDX   |     1 |       |     1   (0)| 00:00:01 |    |          |
    -------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
           filter("TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd
                  hh24:mi:ss'),'fmiw'))
    
    Note
    -----
       - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
    
    
    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
           1170  bytes sent via SQL*Net to client
            520  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
    
    SQL>
    

    The results posted in the previous comment are the result of adding a global index to your example. If the example is run as-is I get the same results in 11.2.0.3 as you do in 12c.

    David Fitzjarrell

    Comment by dfitzjarrell — October 17, 2013 @ 10:56 pm BST Oct 17,2013 | Reply

    • David,
      Thanks for the note – I’ve just repeated the test in 11.2.0.4, and the “partition range single” appears in that version too.

      Oren,
      Did you test an earlier version of 11.2, or can you find some other detail of your configuration (e.g. optimizer parameter) that could explain the difference ?

      Comment by Jonathan Lewis — October 17, 2013 @ 11:07 pm BST Oct 17,2013 | Reply

      • Indeed, Jonathan, it was my ancient 11.2.0.1 database.

        David, it seems that you created the index on TRANSACTION_DATE, is that right?
        The point I was making is that if you create an index on TRANSACTION_WEEK (the virtual column), the optimizer can use it even for the condition on TRANSACTION_DATE. And I tested it now in more versions: doesn’t work on 11.2.0.1, works as of 11.2.0.2.
        It’s fun (yet embarrassing) discovering that features you thought to be new are actually several years old…
        Thanks,
        Oren.

        Comment by Oren Nakdimon @DBoriented — October 18, 2013 @ 5:42 am BST Oct 18,2013 | Reply

        • Quite recently I ran a little test on 12c and discovered something I hadn’t seen before – so I started re-running the test on older versions of Oracle, and discovered it did the same thing all the way back to at least 8.1.7.4. (I don’t recall what the detail was, it was just one of those tiny but clever touches that’s very impressive when you happen to notice it.)

          Comment by Jonathan Lewis — October 18, 2013 @ 2:36 pm BST Oct 18,2013

  3. Two remarks. First here are my tests on 11.2.0.3 where the predicate section doesn’t report the virtual column

    SQL> select * from v$version where rownum = 1;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    
    SQL> select * from transactions where transaction_date = sysdate;
    
    no rows selected
    
    SQL> select * from table(dbms_xplan.display_cursor);
    
    SQL_ID  g6t0c9hqhm7kt, child number 0
    -------------------------------------
    select * from transactions where transaction_date = sysdate
    
    Plan hash value: 2739120976
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |              |       |       |    15 (100)|          |       |       |
    |   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |    15   (0)| 00:00:01 |   KEY |   KEY |
    |*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |    15   (0)| 00:00:01 |   KEY |   KEY |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("TRANSACTION_DATE"=SYSDATE@!)
    
    SQL> select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');
    
    no rows selected
    
    SQL_ID  1x8sjpsma5g8j, child number 0
    -------------------------------------
    select * from transactions where transaction_date =
    to_date('17-Oct-2013','dd-mon-yyyy')
    
    Plan hash value: 2739120976
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |              |       |       |    12 (100)|          |       |       |
    |   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |    12   (0)| 00:00:01 |   KEY |   KEY |
    |*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |    12   (0)| 00:00:01 |   KEY |   KEY |
    -------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
    

    Second, I have a sligthly different case in the same database where a table t1 is list partitioned by a virtual column (DAY_IN_MONTH) which is defined as follows

     DAY_IN_MONTH NUMBER(2,0) GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(CRE_DATE,'DD'))) VIRTUAL 
    

    In this typical configuration and table design, I am not getting the same results when selecting from T1 using the CRE_DATE instead of the DAY_IN_MONTH virtual column

    SQL> select * from T1 where CRE_DATE = to_date('14/10/2013 14:20:17','dd/mm/yyyy hh24:mi:ss');
      
    SQL_ID  853y8awwhrmcj, child number 0
    -------------------------------------
    select * from T1 where CRE_DATE = to_date('14/10/2013
    14:20:17','dd/mm/yyyy hh24:mi:ss')
    
    Plan hash value: 4250255042
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                   |       |       |  1106 (100)|          |       |       |
    |   1 |  PARTITION LIST ALL|                   |  5947 |   435K|  1106   (4)| 00:00:04 |     1 |    32 |
    |*  2 |   TABLE ACCESS FULL| T1                |  5947 |   435K|  1106   (4)| 00:00:04 |     1 |    32 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("CRE_DATE"=TO_DATE(' 2013-10-14 14:20:17', 'syyyy-mm-dd hh24:mi:ss'))
    
    
    SQL> select * from T1 where CRE_DATE = sysdate;
    
    
    SQL_ID  34g7jgn0va0cm, child number 0
    -------------------------------------
    select * from T1 where CRE_DATE = sysdate
    
    Plan hash value: 4250255042
    
    --------------------------------------------------------------------------------------------------------
    | Id  | Operation          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |                   |       |       |  1122 (100)|          |       |       |
    |   1 |  PARTITION LIST ALL|                   |  4560 |   333K|  1122   (5)| 00:00:04 |     1 |    32 |
    |*  2 |   TABLE ACCESS FULL| T1                |  4560 |   333K|  1122   (5)| 00:00:04 |     1 |    32 |
    --------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       2 - filter("CRE_DATE"=SYSDATE@!)
    

    But when I use the virtual column the partition elimination occurs as shown below:

    SQL> select * from T1 where DAY_IN_MONTH = 14;
    
    SQL_ID  0nd1pk0xhj4b7, child number 0
    -------------------------------------
    select * from T1 where DAY_IN_MONTH = 14
    
    Plan hash value: 2781463793
    
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                   |       |       |    26 (100)|          |       |       |
    |   1 |  PARTITION LIST SINGLE|                   |     4 |   364 |    26   (0)| 00:00:01 |   KEY |   KEY |
    |   2 |   TABLE ACCESS FULL   | T1                |     4 |   364 |    26   (0)| 00:00:01 |    14 |    14 |
    -----------------------------------------------------------------------------------------------------------
    

    And not in passing that the predicate part is not present :-)

    Comment by hourim — October 18, 2013 @ 9:31 am BST Oct 18,2013 | Reply

    • Hi Mohamed,
      >>predicate section doesn’t report the virtual column
      It seems that dbms_xplan.display_cursor doesn’t display it (not stored in v$sql_plan) when dbms_xplan.display (after explain plan) displays it.
      But the PARTITION RANGE SINGLE means that the predicate was considered.
      Cheers,
      Franck.

      Comment by Franck Pachot — October 18, 2013 @ 1:48 pm BST Oct 18,2013 | Reply

    • Mohamed,

      Interesting example of a EXPLAIN PLAN and actual execution being different – even thought it’s not in the predicate section in your first comment. (I don’t expect to see the partition-related predicate if it is an exact match for the entire contents of a single partition – or even for a consecutive range of partitions). Interesting, though, that the predicate is visible when the path switches to indexed access (I tried it for local and global indexes).

      Your second point is another example where we feel the optimiser could be just that little bit smarter – especially given the truncate(,’IW’) example – again your requirement is very like Stefan’s.

      Why is truncate difference – I did have one thought – there is a parameter: _truncate_optimization_enabled set to TRUE, which looks promising, but it’s enabled back in 10.2.0.1. At which point I rememberer that object truncating got an optimization some time back there where seg$ ended up being updated once instead of once per extent remove.

      There’s nothing obvious in the 10053 trace file to suggest why the truncate(,’IW’) works – the extra predicate just suddenly appears in the unparsed subquery. (And yes, it’s in the unparsed text even though it then disappears from the display_cursor() call).

      Comment by Jonathan Lewis — October 18, 2013 @ 4:02 pm BST Oct 18,2013 | Reply

  4. Hi Jonathan,
    great article. It seems like that the terrible presentation was useful for something anyway :-)) Unfortunately Oracle is not that clever by using virtual columns with data type conversions.

    For example – something like that (on 12c because of invisible virtual columns):

    SQL> CREATE TABLE TAB1_PART (JINUM VARCHAR2(10),  
    TEXT VARCHAR2(40), JINUM2 NUMBER INVISIBLE AS (TO_NUMBER(JINUM)) VIRTUAL)   
    PARTITION BY RANGE(JINUM2)  
    INTERVAL (5000)  
    ( PARTITION P5000 VALUES LESS THAN (5000), 
    PARTITION P10000 VALUES LESS THAN (10000), 
    PARTITION P15000 VALUES LESS THAN (15000)
    );  
    

    Queries with predicates on column JINUM will force an “PARTITION RANGE ALLL” starting by 1 up to 1048575. Queries with predicates on column JINUM2 will use partition pruning. I currently can not think of a valid reason why Oracle is not able to check the input of JINUM for “numeric characters only” and decide between two plans based on that finding. I know that it is better to use the correct data type right from the start, but unfortunately you can not change this in SAP environments due to specific database layer handling :-((

    Regards
    Stefan

    Comment by Stefan Koehler — October 18, 2013 @ 9:52 am BST Oct 18,2013 | Reply

    • Stefan, you should only “encourage” the optimizer a little bit ;-)
      Following your example,
      select * from tab1_part where JINUM=’42′
      will not cause partition pruning, but
      select * from tab1_part where JINUM=42
      will.

      Comment by Oren Nakdimon @DBoriented — October 18, 2013 @ 10:09 am BST Oct 18,2013 | Reply

      • Hi Oren,
        i would love to, but the predicate data type (in reality binds are used) is generated by SAP DBSL depending on the SAP DDIC definitions and so no chance to change the predicate from “JINUM = VARCHAR2 TYPE” to “JINUM = NUMBER TYPE” … the whole construct is like raping the database of course, but i hoped that the optimizer would be that clever in 12c :-((

        Best Regards
        Stefan

        Comment by Stefan Koehler — October 18, 2013 @ 10:50 am BST Oct 18,2013 | Reply

  5. Just recently, I was looking at approaches to partition a table with a number “date” of format yyyymmdd and I was looking at partitioning by a virtual column of date datatype.

    I had distant hopes that the optimizer might just be clever enough to figure out the partition pruning without me having to change a lot of SQL to use the virtual column.
    But it wasn’t. No surprise really.

    But because you can do interval partitioning on a number – e.g. “interval(1)” for daily or “interval(100)” for monthly – it wasn’t too big a disappointment.

    E.g.

    create table transactions 
    (transaction_date     number       not null
    ,job_id               varchar2(10) not null
    ,account_id           number(8)    not null
    ,transaction_type     varchar2(2)  not null
    ,transaction_id       varchar2(10) not null
    ,amount               number(10,2) not null
    ,padding              varchar2(100)
    ,transaction_really_a_date generated always as (to_date(transaction_date,'yyyymmdd'))
    )
    partition by range (transaction_really_a_date) interval (numtodsinterval(1,'day')) 
    (partition p0 values less than (to_date('20130930','yyyymmdd')));
    
    select * from transactions where transaction_date = to_number(to_char(sysdate,'yyyymmdd'));
    select * from table(dbms_xplan.display_cursor);
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |              |       |       |    32 (100)|          |       |       |
    |   1 |  PARTITION RANGE ALL|              |     1 |   120 |    32   (0)| 00:00:01 |     1 |1048575|
    |*  2 |   TABLE ACCESS FULL | TRANSACTIONS |     1 |   120 |    32   (0)| 00:00:01 |     1 |1048575|
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("TRANSACTION_DATE"=TO_NUMBER(TO_CHAR(SYSDATE@!,'yyyymmdd')))
    
    

    Comment by Dom Brooks — October 18, 2013 @ 10:09 am BST Oct 18,2013 | Reply

    • “… not too big a disappointment.”

      Presumably because you did at least partition the data the way you wanted, even though the optimizer couldn’t do partition elimination for existing application code.

      Comment by Jonathan Lewis — October 18, 2013 @ 2:38 pm BST Oct 18,2013 | Reply

      • Right, disappointing but not a showstopper.
        Desire to reverse engineer best practice by moving away from number dates to date dates remains unfulfilled.
        But the tables could be still be interval partitioned by number, although in hindsight I would have stuck with traditional range partitioning.

        Comment by Dom Brooks — October 18, 2013 @ 3:11 pm BST Oct 18,2013 | Reply

    • Dom,

      Just realised that you’re trying to do the same as Stefan, except he’s trying to “turn characters into numbers” and you’re trying to “turn numbers into dates”. Looking at the 10053 Oracle gets SO CLOSE if you add a predicate that reiterates virtual column definition – in his case:

      "T1"."JINUM"='42'
      try to generate transitive predicate from check constraints for query block SEL$1 (#0)
      constraint: "T1"."JINUM2"=TO_NUMBER("T1"."JINUM")
      
      finally: "T1"."JINUM"='42'
      
      
      

      Comment by Jonathan Lewis — October 18, 2013 @ 3:22 pm BST Oct 18,2013 | Reply

  6. I like the interchange of thoughts that this post has generated – but I’m going to have to spend a little time reading the comments before I can catch up.

    Comment by Jonathan Lewis — October 18, 2013 @ 2:00 pm BST Oct 18,2013 | Reply

  7. […] answer before I did, so I won’t repeat it. The posting was prompted by an email I got about the previous posting, suggesting that the apparently redundant predicate might have been generated to avoid exactly this […]

    Pingback by Quiz Night | Oracle Scratchpad — October 18, 2013 @ 2:26 pm BST Oct 18,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers