Re: PARTition elimination

hopehope_123 wrote:
> hI Daniel ,
>
> I have tried this with an index on a not null column. The result is
> same:
>
> select /*+ordered index(a,IDXALVR_ANA_DWH_TRX_NO) */
> count(*) from ALisveris_ana a 2 ;
>
> Elapsed: 00:02:57.94
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18950 Card=1)
> 1 0 SORT (AGGREGATE)
> 2 1 SORT* (AGGREGATE)
> :Q163055
>
> 4000
>
> 3 2 PARTITION RANGE* (ALL)
> :Q163055
>
> 4000
>
> 4 3 INDEX* (FULL SCAN) OF 'IDXALVR_ANA_DWH_TRX_NO' (NON-
> :Q163055
> UNIQUE) (Cost=18950 Card=139595900)
> 4000
>
>
>
> 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */
> SYS_OP_MSR(COUNT(*)) F
> ROM (SELECT /*+ NO_EXPAND INDEX(A2 "
>
> 3 PARALLEL_COMBINED_WITH_PARENT
> 4 PARALLEL_COMBINED_WITH_PARENT
>
>
> Statistics
> ----------------------------------------------------------
> 35 recursive calls
> 3 db block gets
> 256651 consistent gets
> 255163 physical reads
> 812 redo size
> 495 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 /*+ordered index(a,IDXALVR_ANA_DWH_TRX_NO) */
> count(*) from ALisveris_ana a where
> a.tarih>=to_date('20050101','YYYYMMDD')
> 2 3 ;
>
> Elapsed: 00:13:14.69
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11022169 Card=1 Byte
> s=8)
>
> 1 0 SORT (AGGREGATE)
> 2 1 SORT* (AGGREGATE)
> :Q163057
>
> 2000
>
> 3 2 PARTITION RANGE* (ITERATOR)
> :Q163057
>
> 2000
>
> 4 3 TABLE ACCESS* (BY LOCAL INDEX ROWID) OF 'ALISVERIS_A
> :Q163057
> NA' (Cost=11022169 Card=7 Bytes=56)
> 2000
>
> 5 4 INDEX* (FULL SCAN) OF 'IDXALVR_ANA_DWH_TRX_NO' (NO
> :Q163057
> N-UNIQUE) (Cost=18859 Card=139595900)
> 2000
>
>
>
> 2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */
> SYS_OP_MSR(COUNT(*)) F
> ROM (SELECT /*+ NO_EXPAND INDEX(A2 "
>
> 3 PARALLEL_COMBINED_WITH_PARENT
> 4 PARALLEL_COMBINED_WITH_PARENT
> 5 PARALLEL_COMBINED_WITH_PARENT
>
>
> Statistics
> ----------------------------------------------------------
> 79663 recursive calls
> 4 db block gets
> 4488175 consistent gets
> 962665 physical reads
> 18804 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
> 334 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
> SQL>
>
> Kind Regards,
> tolga

Is IDXALVR_ANA_DWH_TRX_NO a local or global index? And why the ORDERED hint? There is nothing to order?

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