Re: Yet another "why is my index not used" question

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 26 Jan 2009 22:17:06 +0100
Message-ID: <497e2851$0$187$e4fe514c_at_news.xs4all.nl>

Radoulov, Dimitre schreef:
>
> 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
> ------------------------------------------------------------------------------------------------------------------------------------
>
>
> ------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost |
> Pstart| Pstop |
> ------------------------------------------------------------------------------------
>
> | 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.
>
>

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

have any effect? What happens if you set it to, say, 100 ?

Shakespeare Received on Mon Jan 26 2009 - 15:17:06 CST

This message: [ Message body ]
Next message: Michael Austin: "Re: Regarding Oracle version upgrading questions"
Previous message: joel garry: "Re: 2009 Salford Data Mining Conference - San Diego, CA"
In reply to: Radoulov, Dimitre: "Yet another "why is my index not used" question"
Next in thread: Radoulov, Dimitre: "Re: Yet another "why is my index not used" question"
Reply: Radoulov, Dimitre: "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