1 2 3 Previous Next 40 Replies Latest reply: Feb 18, 2014 2:19 AM by David Berger RSS

Reg: philosophy behind Index creation -

ranit B Expert
Currently Being Moderated

Hi Experts,

 

I have some confusion/doubts regarding Indexes getting used (or not used) in a query.

Even after trying much, I am not able to get a concrete hold over this mysterious data structure called "Index".

 

Specifically speaking, the major confusion is while creating a *composite index* and in ordering of columns in it.

 

Consider this scenario of "table_x" :

(

     id number, -->-- sequential Id

     time_period number,

     col3,

     col4,

     and more...

)

The creator of this table has defined a PK on column "id".

 

Now, this index being chosen depends on the way query is written.

There are queries where only column "id" is used, in some queries only "time_period" and in some both are used.

 

So, what is the optimal way of creating an Index?

There's some concept where ordering of columns matter.

 

Please advise on this and ask me questions if any further inputs are required.

(I know information provided by me is not sufficient)

 

-- Ranit

( on Oracle 11.2.0.3.0 )

  • 1. Re: Reg: philosophy behind Index creation -
    SKP Pro
    Currently Being Moderated

    Oracle Performance tunning Guide  http://docs.oracle.com/cd/E11882_01/server.112/e41573.pdf   Has a very good explanation about how and why to use the composite index.

  • 2. Re: Reg: philosophy behind Index creation -
    Hoek Guru
    Currently Being Moderated

    Hi Ranit,

     

    Your question falls into the "it depends" category.

    You have to know the data first, how it's distributed, determine selectivity and the queries that the application uses, for starters.

    There are no Silver Bullits that''ll answer your question.

  • 3. Re: Reg: philosophy behind Index creation -
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    Hi Ranit.

    I didn't understand if ID is PK then where is the logic to use id and time_period (both) in where condition ??? ID only will return 1 row if row exists.


    ----

    Ramin Hashimzade

  • 4. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    Hello ranit B

     

    Yes, it matters how you order the columns in the index.

     

    On the first position should be a column with high cardinality (most selective column). In your case when you have an Index on an almost unique or real unique column then it does not matter whether you have other columns in your index after that.

     

    But there are cases where you can have a very low cardinality column on the first position in the index.

    Example: INDEX (type, group, name) where type has for example only 10 distinct values maximum.

    In this case it is not wrong because oracle has the "skip index scan" and in this case oracle makes union all operations:

    type = A AND...

    UNION ALL

    type = B AND ...

    ...

     

    Does it help you?

  • 5. Re: Reg: philosophy behind Index creation -
    Hoek Guru
    Currently Being Moderated

    It helps in confirming the fact that one has to know/understand the data first before choosing an 'index strategy'

  • 6. Re: Reg: philosophy behind Index creation -
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    I do not see the logic to use the composite index in this case, i mean (time_period, id) or (id, time_period). But in other case when you have an distinct N1 count of values for field A and  N2  count of values for field B and you have where caluse for example

    where a = 1 and b = 'xxxx'

    then if you create index (A,B) you will earn performance if selectivity of table is not a bad than selectivity of FTS


    ----

    Ramin Hashimzade

  • 7. Re: Reg: philosophy behind Index creation -
    Dave Rabone Journeyer
    Currently Being Moderated

    Hoek wrote:

     

    It helps in confirming the fact that one has to know/understand the data first before choosing an 'index strategy'

     

    ... and know / understand the likely usage patterns of the data (ie the queries that will be used, etc etc).

     

    The usage pattern may well vary as time passes, and the 'index strategy' may need to be revised.

  • 8. Re: Reg: philosophy behind Index creation -
    Hoek Guru
    Currently Being Moderated

    Yes, very true.

  • 9. Re: Reg: philosophy behind Index creation -
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    DavidBerger wrote:

     

    On the first position should be a column with high cardinality (most selective column). In your case when you have an Index on an almost unique or real unique column then it does not matter whether you have other columns in your index after that.

     

     

     

    I do wish people would stop repeating this silly suggestion. It falls into the same bucket as the "create bitmap indexes on columns with low cardinality" - i.e. approaching the problem from a silly direction and therefore a BAD sugggestion - especially when it's the first comment you make.

     

    You design indexes by looking at your data patterns and query requirements.

     

    Strangely, and for at least the last 15 years, if you find that you have a multi-column index and there is nothing in the query requirements dictating a specific column order then it's probably better to arrange the index so that the column with the smallest number of distinct values comes first as this gives you the best degree of  index compression.

     

    Regards

    Jonathan Lewis

  • 10. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Hi Hoek,

    Hoek wrote:

     

    You have to know the data first, how it's distributed, determine selectivity and the queries that the application uses, for starters.

     

    Yes, very true.

     

    But, just want to understand "how" to determine that. What are the various parameters? The philosophy? How exactly to do it?

     

    Hope you are getting my curiosity to learn this technik. May be I am not able to express in words, but will still try.

  • 11. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Hi Ramin

    Ramin Hashimzadeh wrote:

     

    I didn't understand if ID is PK then where is the logic to use id and time_period (both) in where condition ??? ID only will return 1 row if row exists.

    Say - the column "id" is used in Joins and "time_period" is used for Filters.

     

     

     

     

     

  • 12. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Ramin Hashimzadeh wrote:

     

    I do not see the logic to use the composite index in this case, i mean (time_period, id) or (id, time_period). But in other case when you have an distinct N1 count of values for field A and  N2  count of values for field B and you have where caluse for example

    where a = 1 and b = 'xxxx'

    then if you create index (A,B) you will earn performance if selectivity of table is not a bad than selectivity of FTS

    True, that Index will only be chosen if optimizer feels it is efficient than FTS.

     

    But, my concern was about creating index i.e. (A,B) or (B,A) or only B or only A.

     

    What is the concept while chosing leading columns in composite index?

     

    (I guess - have to dig in and read more in the concepts manual)

  • 13. Re: Reg: philosophy behind Index creation -
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    ranit B wrote:

     

    Hi Ramin

    Ramin Hashimzadeh wrote:

     

    I didn't understand if ID is PK then where is the logic to use id and time_period (both) in where condition ??? ID only will return 1 row if row exists.

    Say - the column "id" is used in Joins and "time_period" is used for Filters.

     

     

     

     

     

    It is not saying that you need to create composite index.

    As

    And design indexes by looking at your data patterns and query requirements.

     

    ----

    Ramin Hashimzade

  • 14. Re: Reg: philosophy behind Index creation -
    Hoek Guru
    Currently Being Moderated

    Hi Ranit,

     

    I'd not call it a technique but rather a 'strategy', and it's completely based on the nature of the system/application.

    You'll have to know the tables and the queries that are executed, study execution plans, it's a matter of monitoring and adjusting when needed.

     

    I like this quote from Tom:

    "it is quite simple with regards to indexes, follow these two rules:

    o have as few indexes as possible

    o but as many as you need"

    Ask Tom "indexing strategy"

     

     

    "In general, consider creating an index on a column in any of the following situations:

    • The indexed columns are queried frequently and return a small percentage of the total number of rows in the table.
    • A referential integrity constraint exists on the indexed column or columns. The index is a means to avoid a full table lock that would otherwise be required if you update the parent table primary key, merge into the parent table, or delete from the parent table.
    • A unique key constraint will be placed on the table and you want to manually specify the index and all index options. "

     

    Indexes and Index-Organized Tables

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points