Yet another "why is my index not used" question
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
| 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. Received on Mon Jan 26 2009 - 14:18:50 CST