Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
> composite index-
michal.jablonski
post Feb 21 2011, 02:43 AM
Post #1


Advanced Member
***

Group: Members
Posts: 38
Joined: 20-December 10
Member No.: 44,392



I have a table with (for example) column A, B. Table is often selected with clauses:
CODE
WHERE A=:1
and
CODE
WHERE A=:1 AND B=:2
. If I create composite index on coulmn A, B It will be used in both cases. If I create another index on column A, will first select could have less cost or cost will be the same?
Go to the top of the page
 
+Quote Post
 
Start new topic
Replies
burleson
post Feb 21 2011, 08:11 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,495
Joined: 26-January 04
Member No.: 13



Hi Michael,

>> If I create another index on column A, will first select could have less cost or cost will be the same?

Yes, but it's VERY SMALL and not worth the overhead of keeping a second index.

Good Question!

In general, multi-column indexes have less overhead, ONLY WHEN the SQL workload uses both columns. You can run queries to see how many columns are used:

http://www.remote-dba.net/t_index_types_frequency.htm

- When using a multi-column index, you want to put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set.

- Because Oracle can only access one index, your job is to examine your historical SQL workload and build a single composite index that satisfies the majority of the SQL queries.

- The Oracle optimizer may try to make single column indexes behave as-if they were a single composite index. Prior to 10g, this could be done with the "and_equal" hint.

Beware that indexes have overhead

>> If I create another index on column A, will first select could have less cost or cost will be the same?

Very slightly faster, but NOT worth the index overhead!

FYI, you can monitor the usage of columns within composite indexes:

http://www.rampant-books.com/t_oracle_coun...x_usage_sql.htm

If you want to learn more, I have a book on the subject here:

http://www.rampant-books.com/book_1001_oracle_sql_tuning.htm

Good Luck!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Posts in this topic
- michal.jablonski   composite index-   Feb 21 2011, 02:43 AM
- - michal.jablonski   Thank you!   Feb 21 2011, 01:43 PM


Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th September 2014 - 08:56 AM