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

user3897193

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.

William Robertson

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

user3897193

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

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

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

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

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