RE: Index Efficiency
Date: Sat, 20 Apr 2013 11:13:40 -0400
Message-ID: <01b301ce3dd9$a4968ad0$edc3a070$_at_rsiz.com>
True.
Igor's point that this would allow you to not mess with your application is correct.
TomK has a good example of using function based indexes to create the equivalent of "conditional" indexes in Oracle at
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4092298900 346548432
Of course if you'd rather not store the extra 44 million bytes in the table for keeping all the 1s and undergo the overhead of function based indexes, then you can use the very old technique of storing the most popular value as NULL and interpreting NULL as this known value via your application. (You've got over 22 million rows with a 1, and the vsize of 1 is 2).
If certain other predicates exist that would be useful for superior selectivity, you can create the appropriate function based index whether the value for no entry in the function based index is 1 or NULL. Another way to pick up the size savings and NOT mess with your application is to use NULL in the base table with a different name and create a view that interprets the NULL for this column as 1.
Your mileage may vary on which of these techniques will be most performant in a specific case, and likewise on which is easiest to implement.
mwf
From: Igor Neyman [mailto:igor.neyman_at_gmail.com]
Sent: Saturday, April 20, 2013 9:55 AM
To: mwf_at_rsiz.com
Cc: oracledba71_at_gmail.com; Oracle-L Freelists
Subject: Re: Index Efficiency
Or, create partial/conditional indexes on the column with appropriate WHERE clause ("...where status = 1", or "where status = 2"...).
Optimizer should know which one to use based on your query.
Regards,
Igor Neyman
On Sat, Apr 20, 2013 at 12:19 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
I don't see the graph, but I do have a suggestion.
Now I don't know whether "completed" or "declined" is a more popular value, but let's say most things end up "completed."
If your applications can manage interpreting NULL as completed, then you can probably permanently and dramatically shrink the size of your index by using the value NULL instead of 1 for completed.
This seems to match your brief description of your use case below. Your mileage may vary.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of oracledba
Sent: Friday, April 19, 2013 10:20 PM
To: Oracle-L Freelists
Subject: Index Efficiency
I have used Jonathan's index efficiency script to plot the graph.The graph
is attached with this mail.
This index is a single column index ( it's like a status column which has
only 4 values say 1 - completed,2- declined,3-suspended,4-unknown).This
table is populated every day and this column's values other than 1 and 2 are
updated with a new status based on daily's data.
By looking at the graph,what do you infer? How effiecient the index is?
Thanks
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Apr 20 2013 - 17:13:40 CEST