That might be Oracle treating partition_key = single_value as a special
case and checking if the partition list is a single value (and the same
single value). It's probably not a test worth doing for a "proper" INLIST,
though you may find some variants in table expansion if you have local
indexes with some unusable partitions.
Regards
Jonathan Lewis
On Mon, May 25, 2020 at 7:18 PM ahmed.fikri@xxxxxxxxxxx <
ahmed.fikri@xxxxxxxxxxx> wrote:
Hi all,
can someone please explain why we have this difference in the following
two execute plans?
SELECT COUNT(*) FROM tbl_a WHERE loc_id = 10 AND part_key IN( 'part_1')
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
|
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 28 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 95 | | | | |
| 2 | PARTITION LIST SINGLE| | 1349 | 125K| 28 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | IDX_TBL_A_LOC_ID | 1349 | 125K| 28 (0)| 00:00:01
| 1 | 1 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LOC_ID"=10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
|
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 37 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 95 | | | | |
| 2 | PARTITION LIST INLIST | | 1997 | 185K| 37 (0)| 00:00:01 |KEY(I)
|KEY(I) |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TBL_A | 1997 | 185K| 37
(0)| 00:00:01 |KEY(I) |KEY(I) |
|* 4 | INDEX RANGE SCAN | IDX_TBL_A_LOC_ID | 1997 | | 37 (0)| 00:00:01
|KEY(I) |KEY(I) |
--------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) FROM tbl_a WHERE loc_id = 10 AND part_key IN(
'part_1','part_2')
Setup the example:
create TABLE tbl_a
(
part_key VARCHAR2(40 CHAR) not null,
loc_id NUMBER,
text VARCHAR2(280 CHAR)
)
partition by list (part_key)
(
partition PART_1 values ('part_1')
);
CREATE INDEX idx_tbl_a_loc_id ON tbl_a(loc_id) TABLESPACE mds_dat_01 LOCAL
alter index idx_tbl_a_loc_id nologging;
ALTER TABLE tbl_a ADD PARTITION part_2 VALUES('part_2')
ALTER TABLE tbl_a ADD PARTITION part_3 VALUES('part_3')
INSERT /*+ append */ INTO tbl_a (SELECT 'part_1', MOD (ROWNUM, 100),
RPAD('x', MOD(ROWNUM, 20),'y') FROM dual CONNECT BY LEVEL < 100001);
COMMIT;
INSERT /*+ append */ INTO tbl_a (SELECT 'part_2', MOD (ROWNUM, 100),
RPAD('x', MOD(ROWNUM, 20),'y') FROM dual CONNECT BY LEVEL < 100001)
COMMIT;
INSERT /*+ append */ INTO tbl_a (SELECT 'part_3', MOD (ROWNUM, 100),
RPAD('x', MOD(ROWNUM, 20),'y') FROM dual CONNECT BY LEVEL < 100001)
COMMIT;
Thanks and Regards
Ahmed
