AW: local index

From: <ahmed.fikri_at_t-online.de>
Date: Tue, 26 May 2020 08:58:52 +0200 (CEST)
Message-ID: <1590476332783.1575255.a3d6e9fcf277a0fbe0fb6299558feea7a34a411e_at_spica.telekom.de>

In my real case the table has billions of records and it takes lot of time to execute this one
select count(*) from table_a where loc_id = some_id and part_key in (e.g. 50 partitions)  

for me"is not clear why this step TABLE ACCESS BY LOCAL INDEX ROWID BATCHED.   However using plsql:  

DECLARE
v_partitions sys.odcivarchar2list := sys.odcivarchar2list('part_1', ....,'part_50');

l_count NUMBER;
l_total_count NUMBER;
v_loc_id NUMBER;

BEGIN
  FOR i IN 1..v_partitions.count LOOP
    EXECUTE IMMEDIATE 'select /*+ index(t IDX_TBL_A_LOC_ID ) */ count(*) from tbl_a t where part_key = :1 and loc_id = :2 ' INTO l_count USING v_partitions(i), v_loc_id;
  l_total_count := l_total_count + l_count; END LOOP;
  dbms_output.put_line(l_total_count);
END;   it takes less than 3 sec.  

Regards
Ahmed Fikri      

-----Original-Nachricht-----
Betreff: Re: local index
Datum: 2020-05-26T01:31:36+0200
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "list, oracle" <oracle-l_at_freelists.org>        

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

Received on Tue May 26 2020 - 08:58:52 CEST