Re: PARTition elimination

hopehope_123 wrote:
> 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
>

Is dwh_isyeri_kod NOT NULL? If not ... could it be?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)