Yet another "why is my index not used" question

From: Radoulov, Dimitre <cichomitiko_at_gmail.com>
Date: Mon, 26 Jan 2009 21:18:50 +0100
Message-ID: <gll5ra$jlp$1_at_reader.motzarella.org>

Hi all,

our environment: 9i EE 9.2.0.4 64bit, Solaris 8

We're trying to understand why the CBO picks suboptimal single partition scan, when there is a more efficient index range scan available.

Some details (table name modified):
segment statistics are gathered with the following command:

exec dbms_stats.gather_table_stats(user,'T', cascade=>true, - method_opt=>'for all columns size 1')

CPU costing is disabled (no system statistics).

The computed cardinality seems correct, the clustering factor of the index, we're interested in, seems realistic.

Segment space management is manual (no ASSM).

A quick example with autotrace, notice the elapsed time (you will find more details: ddl, table/index stats below, I could provide a 10053 trace file offline):

20:16:09 SQL> set autot traceo exp stat
20:16:13 SQL> SELECT * FROM
20:17:14 SQL> t WHERE
20:17:14 SQL> vmenmag=18 AND  20:17:15 SQL> vmemois=200810;

53710 rows selected.

Elapsed: 00:01:37.00

Execution Plan

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5940 Card=56013 Byte
         s=9298158)

  1    0   TABLE ACCESS (FULL) OF 'T' (Cost=5940 Card=56013 By
         tes=9298158)

Statistics

         0  recursive calls
         0  db block gets
     65300  consistent gets
     61750  physical reads
         0  redo size
   4262557  bytes sent via SQL*Net to client
     40036  bytes received via SQL*Net from client
      3582  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     53710  rows processed


20:18:52 SQL> SELECT --+ index(t)
20:20:00   2  *
20:20:02   3  from t

20:20:07 4 where vmenmag=18 AND
20:20:14 5 vmemois=200810;

53710 rows selected.

Elapsed: 00:00:14.67

Execution Plan

  0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11018 Card=56013 Byt
         es=9298158)

  1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T' (Cost=11
         018 Card=56013 Bytes=9298158)

  2    1     INDEX (RANGE SCAN) OF 'IDX_2' (NON-UNIQUE) (Cost=34
         1 Card=56013)

Statistics

         0  recursive calls
         0  db block gets
     28025  consistent gets
      1599  physical reads
         0  redo size
   4154910  bytes sent via SQL*Net to client
     40036  bytes received via SQL*Net from client
      3582  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
     53710  rows processed

Any help would be appreciated.

Regards
Dimitre

object ddl

CREATE TABLE t
(
  VMENSOC NUMBER(3, 0) NOT NULL,
  VMENMAG NUMBER(5, 0) NOT NULL,
  VMECODE VARCHAR2(13 BYTE),

  VMENCLI NUMBER(9, 0),
  VMEMOIS NUMBER(6, 0),
  VMECMAG NUMBER(3, 0),
  VMENDOS NUMBER(8, 0),
  VMENIV1 NUMBER(4, 0),
  VMENIV2 NUMBER(4, 0),
  VMENIV3 NUMBER(4, 0),
  VMENIV4 NUMBER(4, 0),
  VMENIV5 NUMBER(4, 0),
  VMENIV6 NUMBER(4, 0),
  VMECNUF NUMBER(9, 0),
  VMEDEPT NUMBER(2, 0),
  VMETCOD NUMBER(1, 0),
  VMEMTCA NUMBER(13, 2),
  VMENART NUMBER(10, 3),
  VMEMTMA NUMBER(13, 2),
  VMEMTAL NUMBER(13, 2),
  VMENAAL NUMBER(10, 3),
  VMENJAL NUMBER(2, 0),
  VMEMTPR NUMBER(13, 2),
  VMENJPR NUMBER(2, 0),
  VMENAPR NUMBER(10, 3),

VMETVA NUMBER(13, 2),

  VMEBIIU NUMBER(10, 3),
  VMEBIAC NUMBER(13, 2),
  VMEBIAR NUMBER(13, 2),
  VMEPUIU NUMBER(10, 3),
  VMEPUAC NUMBER(13, 2),
  VMEPUAR NUMBER(13, 2),
  VMEDTIU NUMBER(10, 3),
  VMEDTIC NUMBER(13, 2),
  VMEDTIR NUMBER(13, 2),
  VMEDTOU NUMBER(10, 3),
  VMEDTOC NUMBER(13, 2),
  VMEDTOR NUMBER(13, 2),
  VMEUAIU NUMBER(10, 3),
  VMEUAAC NUMBER(13, 2),
  VMEUAAR NUMBER(13, 2),
  VMEUMIU NUMBER(10, 3),
  VMEUMAR NUMBER(13, 2),
  VMEGMIU NUMBER(10, 3),
  VMEGMAR NUMBER(13, 2),
  VMEVRIU NUMBER(10, 3),
  VMEVRAC NUMBER(13, 2),
  VMEVRAR NUMBER(13, 2),
  VMECRIU NUMBER(10, 3),
  VMECRAC NUMBER(13, 2),
  VMECRAR NUMBER(13, 2),
  VMEFDAC NUMBER(13, 2),
  VMEVDAC NUMBER(13, 2),
  VMETTVA NUMBER(4, 2),
  VMEGMAC NUMBER(13, 2),
  VMEDTIP NUMBER(10, 3),
  VMEPRIU NUMBER(12, 5),
  VMEPRAC NUMBER(16, 5),
  VMEPRAR NUMBER(16, 5),
  VMECOIU NUMBER(12, 5),
  VMECOAC NUMBER(16, 5),
  VMECOAR NUMBER(16, 5),
  VMEAIIU NUMBER(12, 5),
  VMEAIAC NUMBER(16, 5),
  VMEAIAR NUMBER(16, 5),
  VMEEXIU NUMBER(12, 5),
  VMEEXAC NUMBER(16, 5),
  VMEEXAR NUMBER(16, 5),
  VMEMVIU NUMBER(10, 3),
  VMEMVAC NUMBER(13, 2),
  VMEMVAR NUMBER(13, 2),
  VMEMLMA NUMBER(13, 2)

)
  TABLESPACE "DATI01_128K"
  LOGGING PCTFREE 10
  PCTUSED 40
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    BUFFER_POOL DEFAULT
  )
PARTITION BY LIST(VMENMAG)
(
  PARTITION PDV1 VALUES(1)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV2 VALUES(2)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV3 VALUES(3)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV4 VALUES(4)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV5 VALUES(5)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV6 VALUES(6)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV7 VALUES(7)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV8 VALUES(8)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV9 VALUES(9)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV10 VALUES(10)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV11 VALUES(11)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV12 VALUES(12)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV13 VALUES(13)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV14 VALUES(14)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV15 VALUES(15)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV16 VALUES(16)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV17 VALUES(17)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV18 VALUES(18)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV19 VALUES(19)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV20 VALUES(20)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV21 VALUES(21)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV22 VALUES(22)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV23 VALUES(23)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV24 VALUES(24)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV25 VALUES(25)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV26 VALUES(26)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV27 VALUES(27)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV28 VALUES(28)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV29 VALUES(29)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV30 VALUES(30)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDV31 VALUES(31)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
, PARTITION PDVN VALUES(DEFAULT)
    TABLESPACE "DATI02"
    LOGGING PCTFREE 10
    PCTUSED 40
    INITRANS 1
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

)
)
;

CREATE UNIQUE INDEX idx_1 ON t (VMENMAG ASC, VMECODE ASC, VMEMOIS ASC, VMENCLI ASC, VMETTVA ASC, VMENDOS ASC) LOCAL (PARTITION PDV1 TABLESPACE "IDX02"
    LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV2 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV3 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV4 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV5 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV6 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV7 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV8 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV9 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV10 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV11 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV12 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV13 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV14 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV15 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV16 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV17 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV18 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV19 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV20 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV21 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV22 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV23 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV24 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV25 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV26 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV27 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV28 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV29 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV30 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV31 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDVN TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS ); CREATE INDEX idx_2 ON t (VMENMAG ASC, VMEMOIS ASC, VMENIV1 ASC, VMENIV2 ASC, VMENIV3 ASC) LOCAL (PARTITION PDV1 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV2 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV3 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV4 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV5 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV6 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV7 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV8 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV9 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV10 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV11 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV12 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV13 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV14 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV15 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV16 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV17 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV18 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV19 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV20 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV21 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV22 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV23 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV24 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV25 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV26 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV27 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV28 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV29 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV30 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV31 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDVN TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS ); CREATE INDEX idx_3 ON t (VMENMAG ASC, VMECNUF ASC, VMEDEPT ASC) LOCAL (PARTITION PDV1 TABLESPACE "IDX02"
    LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV2 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV3 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV4 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV5 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV6 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV7 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV8 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV9 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV10 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV11 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV12 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV13 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV14 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV15 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV16 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV17 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV18 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV19 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV20 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV21 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV22 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV23 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV24 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV25 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV26 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV27 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV28 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV29 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV30 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDV31 TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

    )
NOCOMPRESS, PARTITION PDVN TABLESPACE "IDX02"     LOGGING PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE
(

      INITIAL 1M
      NEXT 1M
      MINEXTENTS 1
      MAXEXTENTS 2147483645
      PCTINCREASE 0
      FREELISTS 1
      FREELIST GROUPS 1
      BUFFER_POOL DEFAULT

)
NOCOMPRESS );

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- --------- ------
IDX02                          LOCAL      UNIFORM   MANUAL
DATI02                         LOCAL      UNIFORM   MANUAL

11:22:39 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

|   0 | SELECT STATEMENT     |             | 56082 |  9091K|  5941 | 
    |       |
|*  1 |  TABLE ACCESS FULL   | T           | 56082 |  9091K|  5941 |

18 | 18 |

Predicate Information (identified by operation id):

1 - filter("T"."VMEMOIS"=200810)

Note: cpu costing is off

11:22:55 SQL> explain plan for
11:23:19   2   select --+ index(t)
11:23:33   3  * from
11:23:35   4  t
11:23:38   5  WHERE
11:23:41   6  vmenmag=18 AND vmemois=200810;

Explained.

Elapsed: 00:00:00.01
11:23:45 SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| Id  | Operation                         |  Name       | Rows  | Bytes

| Cost | Pstart| Pstop |

|   0 | SELECT STATEMENT                  |             | 56082 | 
9091K| 10787 |       |       |
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID| T           | 56082 | 
9091K| 10787 |    18 |    18 |
|*  2 |   INDEX RANGE SCAN                | IDX_2       | 56082 |

| 327 | 18 | 18 |

Predicate Information (identified by operation id):

2 - access("T"."VMENMAG"=18 AND "T"."VMEMOIS"=200810)

Note: cpu costing is off

15 rows selected.

13:40:47 SQL> select PARTITION_NAME,NUM_ROWS,BLOCKS from dba_tab_partitions
13:41:09   2  where table_name='T'
13:41:21   3  order by PARTITION_POSITION;

PARTI   NUM_ROWS     BLOCKS
----- ---------- ----------
PDV1     2491420      54547
PDV2     1722270      39551
PDV3     2083500      46473
PDV4     1987620      45356
PDV5     2366150      53144
PDV6     2500430      54903
PDV7     2737560      61572
PDV8     2837150      63484
PDV9     2657710      60013
PDV10    1757540      40339
PDV11    1430370      31100
PDV12    1601970      36840
PDV13          0          0
PDV14    2074720      46656
PDV15    1648920      37503
PDV16    1974740      43948
PDV17      43180        877
PDV18    2748000      61760
PDV19    1432570      32782
PDV20          0          0
PDV21    1694630      38577
PDV22    2076730      45612
PDV23    1747020      38873
PDV24    2001250      45155
PDV25    2039750      46317
PDV26    1410010      31660
PDV27    1198720      27451
PDV28    1914000      43619
PDV29     945650      21937
PDV30          0          0
PDV31          1          1
PDVN      452680      23842

32 rows selected.

  1 select PARTITION_NAME,PCT_FREE,BLEVEL,   2 LEAF_BLOCKS,DISTINCT_KEYS,
  3 AVG_DATA_BLOCKS_PER_KEY,
  4 CLUSTERING_FACTOR,NUM_ROWS
  5 from user_ind_partitions
  6 where index_name='IDX_2'
  7* order by PARTITION_POSITION

PARTI PCT_FREE BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS ----- ---------- ---------- ----------- -------------

----------------------- ----------------- ----------
PDV1          10          2       17021          2013 
   229            461842    2425824
PDV2          10          2       10982          2186 
   165            360981    1667491
PDV3          10          2       11714          2403 
   171            412745    2077771
PDV4          10          2       16602          2107 
   203            428120    1998145
PDV5          10          2       13683          2370 
   201            477567    2348829
PDV6          10          2       18972          2262 
   241            545952    2683091
PDV7          10          2       19457          1949 
   254            496306    2718534
PDV8          10          2       21926          1796 
   287            517175    2682240
PDV9          10          2       19934          2034 
   255            519590    2755304
PDV10         10          2       10929          2148 
   167            360505    1726238
PDV11         10          2        9371          3073 
   107            328979    1409525
PDV12         10          2       12430          2140 
   156            334801    1626964
PDV13         10          0           0             0 
     0                 0          0
PDV14         10          2       14617          2307 
   195            451975    2138158
PDV15         10          2       10213          2794 
   132            370107    1691048
PDV16         10          2       11234          2386 
   170            406076    1913378
PDV17         10          1         247           362 
    26              9650      42149
PDV18         10          2       15918          2054 
   249            512527    2631709
PDV19         10          2        9552          2165 
   135            294416    1370312
PDV20         10          0           0             0 
     0                 0          0
PDV21         10          2        9344          2405 
   158            381595    1658533
PDV22         10          2       12080          2694 
   166            449093    2130087
PDV23         10          2       11012          2553 
   156            399657    1729032
PDV24         10          2       17650          1921 
   212            407702    1974145
PDV25         10          2       17655          2075 
   224            465008    2090246
PDV26         10          2        8077          2765 
   117            324433    1445522
PDV27         10          2        7035          3032 
    86            261172    1220152
PDV28         10          2       11545          2362 
   165            391499    1918104
PDV29         10          2        6832          1983 
   103            204819     928208
PDV30         10          0           0             0 
     0                 0          0
PDV31         10          0           1             1 
     1                 1          1
PDVN          10          2        8469           897 
   172            154818     452538

32 rows selected.

SQL> select /*+
  2 cursor_sharing_exact
  3 dynamic_sampling(0)
  4 no_monitoring
  5 no_expand
  6 index (t,"IDX_2")
  7 noparallel_index(t,"IDX_2")
  8 */
  9 sys_op_countchg(substrb(t.rowid,1,15),&m_history) as clf 10 from
11 "OWNER"."T" partition(PDV18) t
12 where
13 "VMENMAG" is not null

14  or "VMEMOIS" is not null
15  or "VMENIV1" is not null
16  or "VMENIV2" is not null
17  or "VMENIV3" is not null

18 ;
Enter value for m_history: 5
old 9: sys_op_countchg(substrb(t.rowid,1,15),&m_history) as clf new 9: sys_op_countchg(substrb(t.rowid,1,15),5) as clf

CLF

522971

1 row selected. Received on Mon Jan 26 2009 - 14:18:50 CST

This message: [ Message body ]
Next message: joel garry: "Re: 2009 Salford Data Mining Conference - San Diego, CA"
Previous message: ghost.hunters.trap_at_gmail.com: "* Hot girls videos pics and much more"
Next in thread: Shakespeare: "Re: Yet another "why is my index not used" question"
Reply: Shakespeare: "Re: Yet another "why is my index not used" question"
Reply: Michael Austin: "Re: Yet another "why is my index not used" question"
Reply: Jonathan Lewis: "Re: Yet another "why is my index not used" question"
Maybe reply: Jonathan Lewis: "Re: Yet another "why is my index not used" question"
Maybe reply: Jonathan Lewis: "Re: Yet another "why is my index not used" question"
Maybe reply: Jonathan Lewis: "Re: Yet another "why is my index not used" question"
Maybe reply: Jonathan Lewis: "Re: Yet another "why is my index not used" question"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message