Forum Stats

  • 3,824,845 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

Optimizer to better understand that criteria is satisfied in some partition definitions.

user3897193
user3897193 Member Posts: 16 Blue Ribbon
edited Nov 5, 2019 8:31AM in Database Ideas - Ideas

Oracle optimizer does understand partitioning when fetching data using global indexes. It does not reference table when it can determine, that row won't exists in those partitions which satisfy where clause. But this doesn't work vice versa. Optimizer does reference table even if can determine that row satisfies partition definitions and there are no other columns which would require fetching table data. E.g. all other columns exists in used index. These kind of situations do happen e.g. in checking existance of row with key column and by some status field. List partitioning by multiple columns does make this kind of behavior more attractive.

0 votes

Active · Last Updated

Comments

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    A worked example would help a lot to clarify this suggestion.

  • user3897193
    user3897193 Member Posts: 16 Blue Ribbon

    Hi

    Simple example about this:

    create table t_demo_fact (
    t_c1 varchar2(2 char),
    t_c2 varchar2(10 char),
    ref_c number(9)
    );

    create table t_demo_reference_table (
    c_key number(9) not null,
    t_status varchar2(10 char),
    t_c3 varchar2(100)
    )
    partition by list(t_status) (
    partition p_active values ('ACTIVE'),
        partition p_archived values ('ARCHIVE')
    );

    create unique index pk_t_demo_reference_table on t_demo_reference_table(c_key);

    alter table t_demo_reference_table add constraint pk_t_demo_reference_table primary key (c_key);

    select * from t_demo_fact where ref_c in (select c_key from t_demo_reference_table where t_status='ACTIVE');

    Now execution plan is:

    |   0 | SELECT STATEMENT                    |                           |       |       |     3 (100)|          |       |       |

    |   1 |  NESTED LOOPS                       |                           |     1 |    76 |     3   (0)| 00:00:01 |       |       |

    |   2 |   NESTED LOOPS                      |                           |     1 |    76 |     3   (0)| 00:00:01 |       |       |

    |   3 |    TABLE ACCESS FULL                | T_DEMO_FACT               |     1 |    41 |     3   (0)| 00:00:01 |       |       |

    |*  4 |    INDEX UNIQUE SCAN                | PK_T_DEMO_REFERENCE_TABLE |     1 |       |     0   (0)|          |       |       |

    |*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T_DEMO_REFERENCE_TABLE    |     1 |    35 |     0   (0)|          |     1 |     1 |

    There is unnecessary access to T_DEMO_REFERENCE_TABLE. In index and in partition definitions there is information, which keys are in partition containing values 'ACTIVE'.

    lh

  • FatMartinR
    FatMartinR Member Posts: 190 Blue Ribbon

    Hi

    Simple example about this:

    create table t_demo_fact (
    t_c1 varchar2(2 char),
    t_c2 varchar2(10 char),
    ref_c number(9)
    );

    create table t_demo_reference_table (
    c_key number(9) not null,
    t_status varchar2(10 char),
    t_c3 varchar2(100)
    )
    partition by list(t_status) (
    partition p_active values ('ACTIVE'),
        partition p_archived values ('ARCHIVE')
    );

    create unique index pk_t_demo_reference_table on t_demo_reference_table(c_key);

    alter table t_demo_reference_table add constraint pk_t_demo_reference_table primary key (c_key);

    select * from t_demo_fact where ref_c in (select c_key from t_demo_reference_table where t_status='ACTIVE');

    Now execution plan is:

    |   0 | SELECT STATEMENT                    |                           |       |       |     3 (100)|          |       |       |

    |   1 |  NESTED LOOPS                       |                           |     1 |    76 |     3   (0)| 00:00:01 |       |       |

    |   2 |   NESTED LOOPS                      |                           |     1 |    76 |     3   (0)| 00:00:01 |       |       |

    |   3 |    TABLE ACCESS FULL                | T_DEMO_FACT               |     1 |    41 |     3   (0)| 00:00:01 |       |       |

    |*  4 |    INDEX UNIQUE SCAN                | PK_T_DEMO_REFERENCE_TABLE |     1 |       |     0   (0)|          |       |       |

    |*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T_DEMO_REFERENCE_TABLE    |     1 |    35 |     0   (0)|          |     1 |     1 |

    There is unnecessary access to T_DEMO_REFERENCE_TABLE. In index and in partition definitions there is information, which keys are in partition containing values 'ACTIVE'.

    lh

    If you add in the column header names for the explain plan,. you can see it's only accessing the data for one partition.

      
    OPERATION OBJECT_NAME OPTIONS PARTITION_START PARTITION_STOP PARTITION_ID CARDINALITY COST
    SELECT STATEMENT
                   1 2
       
    NESTED LOOPS
                   1 2
           
    NESTED LOOPS
                   1 2
               
    TABLE ACCESS
    T_DEMO_FACT FULL          1 2
               
    INDEX
    PK_T_DEMO_REFERENCE_TABLE UNIQUE SCAN          1 0
                   
    Access Predicates
                       
    REF_C=C_KEY
           
    TABLE ACCESS
    T_DEMO_REFERENCE_TABLE BY GLOBAL INDEX ROWID 1 1 5 1 0
               
    Filter Predicates
                   
    T_STATUS='ACTIVE'
  • user3897193
    user3897193 Member Posts: 16 Blue Ribbon

    Hi

    But that table partition is referenced for no reason. Ín table definitions there is told that this partition holds values where T_STATUS ='ACTIVE'. There is no need to access data from this partition.

    lh

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    Hi

    But that table partition is referenced for no reason. Ín table definitions there is told that this partition holds values where T_STATUS ='ACTIVE'. There is no need to access data from this partition.

    lh

    Perhaps I am missing something, but surely it needs to access the table to check whether a row exists. Just because a partition exists, it doesn’t mean there is anything in it.

  • user3897193
    user3897193 Member Posts: 16 Blue Ribbon

    Hi

    Are You familiar with excellent article ?

    https://richardfoote.wordpress.com/2018/10/04/hidden-efficiencies-of-non-partitioned-indexes-on-partitioned-tables-part-…

    If You omit the criteria from them sql-statement: select * from t_demo_fact where ref_c in (select c_key from t_demo_reference_table), You can notice that only index is referenced, not actual table.

    Now in select * from t_demo_fact where ref_c in (select c_key from t_demo_reference_table where t_status='ACTIVE'); it is known from partition definitions that all the rows where t_status='ACTIVE'

    do exist in same partition.  Optimized could utilize the fact and not fetch the actual row because there is no real value for it. If index tells that row exists and it is located in the correct partition, it satisfies the criteria.

    lh