Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Index (9i)
Oracle Index [message #332510] Tue, 08 July 2008 15:20 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
What makes the difference between creating Index in the below two example.

Example 1 :

CREATE INDEX my_index ON table1(column1,column2)
/

Example 2:

CREATE INDEX my_index1 ON table1(column1)
/

CREATE INDEX my_index2 ON table1(column2)
/


Thanks In advance
Re: Oracle Index [message #332512 is a reply to message #332510] Tue, 08 July 2008 15:27 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
The first instance would work if you specified column1, but would not be used if you specified only column2 in your where clause. The second set of indexes would work for either, but would be slower if you specified column1 and column2 because an index merge might need to be done.
Re: Oracle Index [message #332513 is a reply to message #332512] Tue, 08 July 2008 15:29 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Great Than you very much bill Smile
Re: Oracle Index [message #332515 is a reply to message #332510] Tue, 08 July 2008 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
INSERT for Example #2 would be slightly slower than for Example #1
Re: Oracle Index [message #332517 is a reply to message #332515] Tue, 08 July 2008 15:42 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
i was going to ask this also, but i'm just gonna add also this question. is it ok to do this

CREATE INDEX my_index ON table1(column1,column2)
/

then

CREATE INDEX my_index2 ON table1(column2)
/


just in case you just want to use the column2 as the filter?
Re: Oracle Index [message #332547 is a reply to message #332517] Tue, 08 July 2008 23:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Because indexes are not "for free" (eg. inerts are slowed down), one generally does not create indexes 'just in case'.
If you know upfront that you will use column2 without column1 in where-clauses, then it's ok.
An alternative could be to change the other index to column2, column1. But of course that could only be done if you don't select on column1 alone.
Previous Topic: Efficient SQL
Next Topic: Hi Everyone
Goto Forum:
  


Current Time: Thu Dec 08 10:09:24 CST 2016

Total time taken to generate the page: 0.11640 seconds