local index

From: <ahmed.fikri_at_t-online.de>
Date: Mon, 25 May 2020 20:16:53 +0200 (CEST)
Message-ID: <1590430613831.1536800.3d71c6e91bdca5ece144b5248bd84222c1cf9667_at_spica.telekom.de>

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

Received on Mon May 25 2020 - 20:16:53 CEST