| How to reduce nested loops in a big query? [message #178559] |
Wed, 21 June 2006 09:21  |
Jaime Stuardo
Messages: 57
Registered: March 2004
|
Member |
|
|
Hi all..
I have a query that is very heavy and takes very long time to execute.
By watching its explain plan, I see a lot of nested loops, some unique scans and some range scans. I show you this :
SELECT STATEMENT Optimizer Mode=RULE
SORT UNIQUE
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY ROWID ACSEL.POLIZA
INDEX RANGE SCAN ACSEL.POLIZA_KEY
TABLE ACCESS BY ROWID ACSEL.EMPLEADOR
INDEX UNIQUE SCAN ACSEL.IND_EMPLEADOR
INDEX UNIQUE SCAN ACSEL.PK_TERCERO
TABLE ACCESS BY ROWID ACSEL.ASEGURADO
INDEX RANGE SCAN ACSEL.IDX_ASEGURADO_01
TABLE ACCESS BY ROWID ACSEL.CLIENTE
INDEX UNIQUE SCAN ACSEL.PK_CLIENTE
TABLE ACCESS BY ROWID ACSEL.TERCERO
INDEX UNIQUE SCAN ACSEL.PK_TERCERO
Is there a way to reduce the number of nested loops?
One thing I never have cleared is how to arrange conditions in WHERE clause. The condition that filters most of the records must be placed at the end of the query or at just after the WHERE keyword? what about table order in FROM clause? all that affects nested loops? I have tested rearranging tables, but explain plan doesn't vary.
Any hint would be greatly appretiated
Thanks
Jaime
|
|
|
|
|
|
| Re: How to reduce nested loops in a big query? [message #178563 is a reply to message #178559] |
Wed, 21 June 2006 09:32  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What you need to do is stop using the Rule Based Optimiser (RBO) and move to the Cost Based Optimizer (CBO).
The RBO follows a set of rules to determine how to process a query, and one of those runes is 'If there's an index, use it' (I exaggerate, but not much)
The CBO will look at the amount and distribution of data in the queries, estimate how many rows it will get from each table, and decide the access routes to each table based on this data.
If you can't go CBO, then you might want to try this:
Look at the explain plan, and try to work out how many rows each step will be returning, starting at the innermost one.
Any step that's returning more than 5-10% of the table, add a FULL(table alias) hint to the query to disuade the RBO from trying to step through too many rows using an index.
AFAIK, the table ordr and where clause order don't matter.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
