Re: local index

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_at_t-online.de < ahmed.fikri_at_t-online.de> 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
>
>
> 
>

--
http://www.freelists.org/webpage/oracle-l