| how to use indexes [message #6010] |
Tue, 18 March 2003 12:57  |
ecabiac
Messages: 7
Registered: March 2003
|
Junior Member |
|
|
If I want to use indexes for various selects on a table is it better to create separate indexes for each operation or to find a subset?
IF I have the following columns in my where clause in 3 different SQLs:
c1, c2, c3
c1, c2, c4
c1, c2, c5, c6
what would be the best indexes to create?
|
|
|
|
| Re: how to use indexes [message #6011 is a reply to message #6010] |
Tue, 18 March 2003 13:06  |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
The index is only good if the indexed column is references in the WHERE or ORDER BY clause.
If the list you provided relates to the any of these clauses, then a concatenated (multiple column) index on C1 and C2 will be you best bet.
|
|
|
|
| Re: how to use indexes [message #6013 is a reply to message #6010] |
Tue, 18 March 2003 13:22 |
ecabiac
Messages: 7
Registered: March 2003
|
Junior Member |
|
|
Thank you, that helps-but on the same topic,I have the following SQL
SELECT ORDER.ORDER_NUMBER
FROM ORDER
WHERE ORDER.STATUS = 'OPEN';
I also have an index that only references ORDER.STATUS but when I get the explain plan it says full table scan, what gives?
|
|
|
|
|
|
| Re: how to use indexes [message #6020 is a reply to message #6013] |
Tue, 18 March 2003 14:23 |
Ivan
Messages: 180
Registered: June 2000
|
Senior Member |
|
|
I would assume if you do a
SELECT DISTINCT status
FROM order;
you'll get only two values: OPEN and CLOSE
An index is only good if using it the query will return 15% or less of the entire table's data.
Execute the following query
SELECT NVL (SUM (DECODE (status, 'OPEN', 1, 0)), 0)
, COUNT (1)
FROM order;
If the first column's value is 15% or less of the second column value, you'll be in good shape creating an index on the STATUS column.
In this case, however, because you only have two distinct values in the column, Oracle's Optimizer may decide NOT to use the index, because it still thinks that the selectivity on this column is only 50% (100% / 2 distinct values). If you find out that that's the case (by using the EXPLAIN PLAN command) add a hint to your select statement (either INDEX or RULE)
SELECT /*+ INDEX (o your_index_name_on_status_column) */
o.order_number
FROM order o
WHERE o.status = 'OPEN';
In case when the percentage of the "OPEN" statuses is higher than 15%, an index will only hurt your performance, so if you already have an index on that column, and you find out that Oracle Optimizer is using it while accessing the data, you'll be better off suppressing it
SELECT order_number FROM order WHERE status || '' = 'OPEN';
Good luck
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
