Re: Suitable index for the query
- From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
- To: davepacia@xxxxxxxxx
- Date: Sat, 26 Jun 2010 23:52:58 -0400
Actually, creating a composite index in order of decreasing column
cardinality is not generally a good idea. The Oracle Database Performance
Tuning Guide in Release
11<http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#i2773>gets
this right. There was bad advice in the Release 9 Tuning Guide; I'm
not
sure whether they fixed it in 10 or 11, but it's good now. Check section
14.1.4.2<http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/data_acc.htm#i2773>,
called Ordering Keys for Composite Indexes.
The bottom line: If you don't know what your queries look like, then there's
no way you can know what indexes will optimize your system. Specifically,
you *cannot* define indexes optimally by looking only at your data. You have
to look at your SQL.
Using Oracle's corrected guidelines can reduce the total number of indexes
you need on your system, which improves insert/update/delete/merge
performance and backup/recovery performance, in addition to improving query
performance. For example, imagine that you have columns A, B, and C (listed
in decreasing-column-cardinality order), and they're accessed in queries
with WHERE clauses like the following:
- B=:x and C=:y and A between :z1 and :z2
- B=:x
- B=:x and C>:y
Then you'd want only the index (B,C,A) to cover all three queries. If you
had the index (A,B,C), which is what the decreasing-cardinality-order rule
suggests, then you'd also need (B,C) for the second and third queries, which
would waste code path on additional upkeep of having two indexes instead of
just the one better one. It's a long, complicated story in most real
applications, and therefore in a lot of applications there's a lot of room
for improvement. Tapio Lahdenmäki has written a very nice
book<http://www.amazon.com/gp/product/0471719994?ie=UTF8&tag=methodrcom-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0471719994>on
the subject.
Here's a little bit of a mind-blower for some people: When everything else
is equal, it's actually better to list the columns with the
*lowest*cardinality at the head of a composite index (the
*opposite* of what you wrote!). Richard Foote describes one of the reasons
for doing it this way in
http://richardfoote.wordpress.com/2008/03/10/index-skip-scan-does-index-column-order-matter-any-more-warning-sign/.
Another reason has to do with index compression efficiency. I saw Richard
present about this topic earlier this year, but I don't have a link for you
on that one.
Lots of people in the early 1990s taught the descending-cardinality-order
idea. I think it at least partly had to do with the particular indexes that
Oracle Applications (forerunner to E-Business Suite) needed you to create.
Conveniently, creating one particular GL index using the
descending-cardinality-order idea happened to be the right thing to do, but
the reason for its being optimal had nothing to do with the cardinality
values of the columns.
My team and I wrote lots of tests and internal training material within
Oracle Corporation between 1990 and 1994 or so, to teach people why the
order-of-descending-cardinality "rule" for composite indexes wasn't a good
one. Looks like maybe this is one of those myths that sounds too good to
actually die like it should have :-). Nowadays (because of skip-scans, for
example), the descending-cardinality-order idea is an even worse idea than
it was back then. At least nowadays, the Oracle Tuning Guide gives good
advice about it.
Hope this helps...
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
On Sat, Jun 26, 2010 at 1:16 PM, Dave Pacia <davepacia@xxxxxxxxx> wrote:
> Alex,
> How many rows in the item_master table? What is the avg_row_len? How many
> blocks in the table? If the table is small, a full table scan may be more
> efficient than via index. Instead of 2 reads, one for the index followed by
> a second for the table, Oracle can get the job done with a single read.
>
> If we know that the table is going to be accessed via three columns, we
> build indexes in the order of decreasing cardinality: column with largest
> number of distinct values first, followed by the second, etc.
>
> Out of curiousity, what is the Oracle version and operating system?
>
> Best Regards,
> Dave
>
>
> ------------------------------
> *From:* Alex Octan <alex@xxxxxxxxxxx>
> *To:* Oracle I List <oracle-l@xxxxxxxxxxxxx>
> *Sent:* Sat, June 26, 2010 9:53:02 AM
> *Subject:* Re: Suitable index for the query
>
> Hi Stephane,
>
> what you said makes total sense, the way you put is right.
> I was just wondering if I could improve more the performance, ... the other
> columns are indexed and with the statistics up-to-date.
> Anyway, thanks a lot for putting the explanation this way, very very
> helpful.
>
> Alex
>
>
>
>
>
> On Fri, Jun 25, 2010 at 17:58, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote:
>
>> Alex,
>>
>> Why do you think that an index would make things run faster? And let me
>> tell you that applying the Monte-Carlo method to indexing is unlikely to
>> give much result.
>> tran_level and item_level belong to the same table, therefore you can
>> ignore them. Your query is like going to a library and saying "what are
>> the books say about databases in which I find three identical words on
>> page 20 and 50?" All you can do is get all the database books and check
>> them one by one when you have them. Do you see what I mean? In my
>> comparison, one book is just like one row from item_master in your case.
>> You don't know before getting the row what tran_level holds anymore than
>> you know before getting the book which words are on page 20. Indexing is
>> useless.
>>
>> Your entry point is item_loc.loc - I assume boldly (from the names)
>> that's the primary key. You can therefore get the item_loc fast, unless
>> it is defined as a varchar2, in which case your query would lack quotes.
>> Let's say you have this row, and the value for 'item' that it contains.
>> How selective is it? Can you find this value in many rows of
>> item_master, or not? If it's not selective, nothing wrong in running a
>> full scan. If it's selective, have you an index on it? Does Oracle know
>> it is selective? Have you computed stats on the index? If the values is
>> sometimes selective and sometimes not, tough luck, I doubt that even
>> histograms would help you because the optimizer cannot decide beforehand
>> how popular is a value that it retrieves in the process of the join.
>>
>> Hope that helps,
>>
>> Stephane Faroult
>> RoughSea Ltd <http://www.roughsea.com>
>> Konagora <http://www.konagora.com>
>> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>>
>>
>> Alex Octan wrote:
>> > Hi there,
>> >
>> > I am struggling to find a way to optimize the performance of my query
>> > to use index when involving 3 columns.
>> > I highlighted the columns that is being used on my query, however I
>> > tried to create index with different combinations but the db doesnt use.
>> >
>> > The indexes that i tried to create, the execution plan didn't use any
>> > of them.
>> > So, my doubt is how can I improve performance on this query? cause its
>> > getting full table scan
>> >
>> > Any thoughts is very welcome.
>> > Thanks all
>> > Alex
>> >
>> >
>> > . . .
>> > FROM item_loc il,
>> > item_master im --------------------> table access full
>> > WHERE il.loc = 2836
>> > AND il.item = im.item -----------------> one column to include
>> > in the clause
>> > AND im.tran_level = im.item_level ---> two columns to include in
>> > the clause
>> >
>> >
>> > create index ITEM_MASTER_I9 on ITEM_MASTER (item, tran_level,
>> > item_level) tablespace RETEK_CAT_INDEX;
>> >
>> > create index ITEM_MASTER_I9 on ITEM_MASTER (item, tran_level)
>> > tablespace RETEK_CAT_INDEX;
>> >
>> > create index ITEM_MASTER_I9 on ITEM_MASTER (tran_level, item_level)
>> > tablespace RETEK_CAT_INDEX;
>> >
>> > create index ITEM_MASTER_I9 on ITEM_MASTER (item, item_level)
>> > tablespace RETEK_CAT_INDEX;
>> >
>> >
>>
>>
>>
>
>
>
Other related posts:
