| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: fix to heavy disk-read query?
Hi:
There's not much point in just adding indexes on each column. You have to do an explain plan on the query to see which indexes it's actually using. You've got the right idea, though. Adding indexes is one of the best ways to reduce the run-time of queries that do lot's of disk reads.
Suggestions:
Start with the column in the WHERE clause with the highest selectivity (assuming you're using CBO). Create an index on that column. EXPLAIN PLAN and see if it is using it. If not, drop it. Find the column in the WHERE clause with the next highest selectivity. Create an index using both these columns, EXPLAIN PLAN, repeat until successful! An index with two columns will always be more selective (and more likely to be used) than an index with a single column, and so on.
If that doesn't work, you might want to look creating an index on the columns in the WHERE clause (in order of selectivity) plus the columns in the select list from the table. This way, the rows can be retrieved by accessing only the index (not the table). This could be a fairly large index, but it is very likely to make the query go quite fast, even if the above approach fails.
Looks like this is based around an order table and might be an OLTP system. If it is a DSS system, you might want to try using bitmap indexes. Put a bitmap on the LOWEST selectivity column in the WHERE clause, and explain plan. If it didn't use the first index, add a second index on the next lowest selectivity column in the WHERE clause, and explain plan again (don't the drop the first index). Continue until successful. Oracle can union or intersect bitmap indexes (it's all matrix math), so adding multiple single column bitmpa indexes has a high probability of improvement. You shouldn't use bitmap indexes if the tables being indexed receive concurrent updates from multiple concurrent sessions (single-stream batch updates are fine).
Keep at it until you get it. I've seen correct/creative indexing make queries go several hundred times faster!
Good luck,
--
David C. Sisk
Need tech info on Oracle? Visit The Unofficial ORACLE on NT site at
http://www.ipass.net/~davesisk/oont.htm
Like original music? Listen to song samples and buy a CD at
http://www.mp3.com/disparityofcult
David Spaisman wrote in message <379D3F38.4D781D92_at_intercall.net>...
>Hello:
>
>The attached query has indexes on each column excpet the 'type' columns.
>This is the worst peforming query every day that I check -- the most
>disk reads and buffer gets.
>
>With the exception of adding a column to the balance table, I don't see
>much to do to remove the excessive disk reads.
>
>Does any one hvae any ideas?
>
>Thanks.
>
>David Spaisman
>
>
>
>---------------------------------------------------------------------------
 |
 |