| functional index null values [message #550059] |
Thu, 05 April 2012 12:01  |
 |
vladimirhrivnak
Messages: 1
Registered: April 2012
Location: Montreal
|
Junior Member |
|
|
Create small functional indexes for special cases in very large tables.
When there is a column having one values in 99% records and another values that have to be search for, it is possible to create an index using null value.
Index will be small and the rebuild fast.
Example
create index vh_tst_decode_ind_if1 on vh_tst_decode_ind
(decode(S,'I','I',null),style)
It is possible to do index more selective when the key is updated and there are many records to create more levels in b-tree.
create index vh_tst_decode_ind_if3 on vh_tst_decode_ind
(decode(S,'I','I',null),
decode(S,'I',style,null)
)
To access the record can by like:
SQL> select --+ index(vh_tst_decode_ind_if3)
2 style ,count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 group by style
7 ;
STYLE COUNT(*)
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=305 Bytes=457
5)
1 0 SORT (GROUP BY) (Cost=3 Card=305 Bytes=4575)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'VH_TST_DECODE_IND' (Co
st=2 Card=305 Bytes=4575)
3 2 INDEX (RANGE SCAN) OF 'VH_TST_DECODE_IND_IF3' (NON-UNI
QUE) (Cost=3 Card=122)
SQL> select --+ index(vh_tst_decode_ind_if3)
2 count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6 ;
COUNT(*)
----------
9
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'VH_TST_DECODE_IND_IF3' (NON-UNIQU
E) (Cost=3 Card=305 Bytes=610)
1 select --+ index(vh_tst_decode_ind_if3)
2 count(*)
3 from vh_tst_decode_ind
4 where
5 decode(S,'I','I',null)='I'
6* and decode(S,'I',style,null) between 2 and 8
SQL> /
COUNT(*)
----------
7
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'VH_TST_DECODE_IND_IF3' (NON-UNIQU
E) (Cost=3 Card=1 Bytes=15)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
