Home » RDBMS Server » Performance Tuning » functional index null values (database, 9.2.0.8,hp)
functional index null values [message #550059] Thu, 05 April 2012 12:01 Go to next message
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)

Re: functional index null values [message #550063 is a reply to message #550059] Thu, 05 April 2012 12:19 Go to previous messageGo to next message
LNossov
Messages: 284
Registered: July 2011
Location: Germany
Senior Member
And? Do you want to ask, if your solution is very trivial? It is trivial enough.
Re: functional index null values [message #550065 is a reply to message #550059] Thu, 05 April 2012 12:25 Go to previous message
Michel Cadot
Messages: 58607
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where is the question?

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Previous Topic: Query tuning - index suggestion
Next Topic: Tuning for slow Oracle query
Goto Forum:
  


Current Time: Tue Jul 29 03:40:52 CDT 2014

Total time taken to generate the page: 0.09319 seconds