Re: PARTition elimination

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 18 Nov 2005 04:15:15 -0800
Message-ID: <1132316115.722240.158960@g44g2000cwa.googlegroups.com>

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 Received on Fri Nov 18 2005 - 06:15:15 CST