This discussion is archived
10 Replies Latest reply: May 30, 2012 5:35 AM by 311441 RSS

Want to create index on table?

yash_08031983 Newbie
Currently Being Moderated
Hello experts, i am using 10g db.
i have a table prd_mst having column
locn_code, only two values '400001' and '400002'
order_no, primary key
item_code, more than 2000 values
order_qty
with lakh of records.


i generaly use in column in where clause
where locn_code='40002'
and order_no='1111'
and item_code='abc'

i can create the index in two way..
1. create saperate index on all the three columns
like:
create index i1 on prd_mst(locn_code)

2. create single index on all the three column like
create index i1 on prd_mst(locn_code,order_no,item_code)

which one is better and why??
and also tell me the order of column in index definition should be according to cardinality of columns in table???

thanks
yash

Edited by: yash_08031983 on May 29, 2012 2:20 AM
  • 1. Re: Want to create index on table?
    Bugra Canbakal Newbie
    Currently Being Moderated
    If you always query same type of sql secound would be better.

    2. create single index on all the three column like
    create index i1 on prd_mst(locn_code,order_no,item_code)
  • 2. Re: Want to create index on table?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    Oracle can only use an index efficiently if its leading column(s) are used in the query's WHERE clause. If you ALWAYS use all the 3 conditions that you mentioned in your queries, than it doesn't matter much which order you choose. If sometimes you will use just 1 or 2 columns, then you should choose these columns as leading ones.

    Best regards,
    Nikolay
  • 3. Re: Want to create index on table?
    Bugra Canbakal Newbie
    Currently Being Moderated
    You can create virtual indexes and see the cost of them

    http://www.oracle-base.com/articles/misc/virtual-indexes.php
  • 4. Re: Want to create index on table?
    yash_08031983 Newbie
    Currently Being Moderated
    can we also create index on date column in table???
    as always there is a from date and todate in query's where clause.
    thanks yash
  • 5. Re: Want to create index on table?
    Girish Sharma Guru
    Currently Being Moderated
    Please read below link where John and Justin are mentioning good points for index on date column :

    creating index on datepart of datetime field

    Regards
    Girish Sharma
  • 6. Re: Want to create index on table?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    you can, but there unless you are going to use it for equalities, it may be not very useful. Very often, dates are used for conditions like:
    select * from mytable where to_date('29-May-2012', 'dd-MON-YYYY') between _from and _to
    which are equivalent to two inequalities, which means

    1) accessing a large part of index blocks
    2) inability to efficiently combine two indexes into one

    Best regards,
    Nikolay
  • 7. Re: Want to create index on table?
    898064 Newbie
    Currently Being Moderated
    Also, for best performance you can put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set. So, you index should like as below:

    create index i1 on prd_mst(order_no, item_code, locn_code)

    order_no is first because this is you primary key, so, this will have all unique values. Then item_code comes next, followed by locn_code.

    Regards,
    Himanshu
  • 8. Re: Want to create index on table?
    jgarry Guru
    Currently Being Moderated
    Seems odd, but anyways, see http://richardfoote.wordpress.com/2008/03/10/index-skip-scan-does-index-column-order-matter-any-more-warning-sign/
  • 9. Re: Want to create index on table?
    Richard Foote Employee ACE
    Currently Being Moderated
    HimsemaJ wrote:
    Also, for best performance you can put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set. So, you index should like as below:

    create index i1 on prd_mst(order_no, item_code, locn_code)

    order_no is first because this is you primary key, so, this will have all unique values. Then item_code comes next, followed by locn_code.
    Hi Himanshu

    This is a common myth. If you specify all columns in the index, Oracle will always traverse directly to the leaf block containing the first occurance of required index values, regardless of the column order. There might be a slight additional overhead in the amount of data required to be stored within the index branch blocks, but this is usually trivial compared to the overall size of the index.

    For more details, see: http://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 10. Re: Want to create index on table?
    311441 Employee ACE
    Currently Being Moderated
    Bugra Canbakal wrote:
    You can create virtual indexes and see the cost of them
    Hi Bugra

    Actually, you can't really see the true cost of indexes via the use of virtual indexes. However, I thought it worth a blog post to help explain why:

    http://richardfoote.wordpress.com/2012/05/30/cost-of-virtual-indexes-little-lies/

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/

Legend

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