RE: Function based indexes?
- From: "Rick Stephenson" <RStephenson@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Dec 2005 16:03:37 -0700
After trying the idea below, it looks as if the null values are not
stored in the index even though it is a function based index. So, this
might be what I am after.
> create table test (id number);
Table created.
> insert into test values(1);
1 row created.
> insert into test values(1);
1 row created.
> insert into test values(0);
1 row created.
> insert into test values(0);
1 row created.
> create index test_case_idx on test (case when id = 1 then id else null
end);
Index created.
> analyze index test_case_idx validate structure;
Index analyzed.
> select lf_rows from index_stats;
LF_ROWS
----------
2
1 row selected.
Thanks for your help Lex.
Rick Stephenson
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 3:31 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Function based indexes?
I had not thought about doing it that way. The table has about 300
million rows, 10,000 of which would contain the value I am searching for
which is why I only wanted to see if there was a way to create an index
for just 1 value in a column. It will be stored in a separate column.
So, as long as I store the value I want and leave the rest NULL, I can
accomplish what I am after. Does that mean there is no other way to
accomplish this task if I have multiple values?
Thanks,
Rick Stephenson
________________________________
From: Lex de Haan [mailto:lex.de.haan@xxxxxxxxxxxxxx]
Sent: Tuesday, December 06, 2005 11:59 AM
To: Rick Stephenson; oracle-l@xxxxxxxxxxxxx
Subject: RE: Function based indexes?
yes, you can -- more or less -- by mapping all other possible outcomes
to NULL with a CASE expression. Note however that function-based indexes
also store entries for NULL expression outcomes.
By the way, if that value 1 is so special, you might consider to store
it in a separate column. Unless tomorrow the special value is suddenly
42 :-)
but why would you bother about a little bit of space? as long as you
don't perform full index scans, the performance will not be affected by
the index size...
kind regards,
Lex.
---------------------------------------------------------------------
Jonathan Lewis Seminar http://www.naturaljoin.nl/events/seminars.html
<http://www.naturaljoin.nl/events/seminars.html>
---------------------------------------------------------------------
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Rick Stephenson
Sent: Tuesday, December 06, 2005 19:45
To: oracle-l@xxxxxxxxxxxxx
Subject: Function based indexes?
Is there a way to create an index on a column and only index those where
the value meets a certain expression? For example, if I have a numeric
column, can I just have the index built for those values that equal 1?
I don't query on any other value, so I don't want to consume the space
for the other values. I am running EE 9.2.0.3.
Thanks,
Rick Stephenson
Other related posts:
