Any Orcale/SQL gurus?

  The Sheep 12:23 03 Apr 2003

Can anyone explain to me when it is appropriotte to use an index on an Oracle table? Any help would be appreciated.

  Big Elf 13:00 03 Apr 2003

I'm an Oracle Apps specialist (FA) rather than a tekki but my understanding is that indexing is used for when fast searches of large tables are required. The disadvantage being that it uses significant resources.

If you mail me with more detail I'll ask our tekkis.

  Big Elf 18:58 03 Apr 2003

Let's suppose you have a table called customer with columns of customer_id, name, address_line1, address_line2 etc.

You would definitely have an index on customer_id and you would make it unique to prevent the same customer_id being used twice. You would also want an index on name but this shouldn't be unique.

If you did a search on customer without any qualification it would return all rows and not use any indexes. If you qualified the search by specifying the customer_id you would get one row and it would use the index making it
very fast. If you qualified the search by specifying the name you would get one or more rows and it would use the index making it reasonably fast.

You could have indexes on other columns too, but be careful about it. Each index takes up space and has to be updated every time a row is inserted/deleted/amended. Although this latter consideration does not apply
to data warehouses which are read-only most of the time.

Don't create indexes on columns which do not appear in selection qualification. I.e. if you are never going to say "where address_line_1 = 22 Skid Row" then don't have an index on address_line_1.

Don't create indexes on columns which have many duplicates. I.e if there are thousands of rows where address_line_2 = London then an index on address_line_2 is waste of time.

Also an index will not be used when the selection qualification is vague. So whilst "where name = Smith" would use an index, "where name like %ith" wouldn't.

Oracle will only use one index on one table per query. Thus if you said "where customer_id = 1234 and name = Smith", it would just use the best one.

I'm well impressed (if only I could understand it).

Hope this is of some use.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Microsoft Paint set to die after 32 years

Mac power user tips and hidden tricks

Comment désactiver la saisie intuitive et paramétrer votre clavier ?