PARTition elimination

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 17 Nov 2005 06:27:22 -0800
Message-ID: <1132237642.614559.242350@f14g2000cwb.googlegroups.com>

Hi ,

I have a table which is partitoned by range using a date column called tarih. There exists also a bitmap local index on column dwh_isyeri_kod.

The below 2 queries , although does the same thing creates very different kinf of load. The first sql counts all data by using the bitmap indexand runs very fast . It does:

      31324  consistent gets
      17752  physical reads

On the other hand , second query limits the tarih column which leads to partition elimination . But this query runs slower.It does:

     745001  consistent gets
     290466  physical reads

But this query does partition elimination . Why does the query need to do 'table access' although i only need index?

Does partition elimination needs to access the table itself in order to eliminate partitions for bitmap index searches?

Kind Regards,
tolga

select count(*)
  2 from ALisveris_ana a

  3      where
  4             a.dwh_isyeri_kod=12

Statistics

---

         44  recursive calls
        111  db block gets
      31324  consistent gets
      17752  physical reads
       7548  redo size
        494  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        700  sorts (memory)
          0  sorts (disk)
          1  rows processed



		select /*+index(a,IDXALVR_DWH_ISYERI_KOD) */
		count(a.dwh_isyeri_kod) from  ALisveris_ana a
        where
              a.dwh_isyeri_kod=12 AND
			   a.tarih>=to_date('20050101','YYYYMMDD')

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=202253 Card=1 Bytes=
          12)

   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)

:Q162812

8000

   3 2 PARTITION RANGE* (ITERATOR)
:Q162812

8000

   4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'ALISVERIS_A
:Q162812

          NA' (Cost=202253 Card=1 Bytes=12) 8000

   5 4 BITMAP CONVERSION* (TO ROWIDS)
:Q162812

8000

   6 5 BITMAP INDEX* (SINGLE VALUE) OF 'IDXALVR_DWH_ISY
:Q162812

          ERI_KOD'
8000

   2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(A1.C1

                                   )) FROM (SELECT /*+ NO_EXPAND INDEX_

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_COMBINED_WITH_PARENT

   5 PARALLEL_COMBINED_WITH_PARENT
   6 PARALLEL_COMBINED_WITH_PARENT

---

      79293  recursive calls
          4  db block gets
     745001  consistent gets
     290466  physical reads
        928  redo size
        509  bytes sent via SQL*Net to client
        652  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
        321  sorts (memory)
          0  sorts (disk)
          1  rows processed

Received on Thu Nov 17 2005 - 08:27:22 CST