SELECT ...
FROM ( SELECT ...
FROM mytable
WHERE column = constant AND
column2 In ()etc) table_name,
Also try joining together tables that have an inner join and then join to that the outer join tables.
Code:
SELECT ...
FROM ( SELECT ...
FROM mytable
WHERE column = constant AND
column2 In ()etc) table_name
LEFT OUTER JOIN tablec ON
join clause
You can also do a subquery in the outer join. My strategy is to use subqueries to eliminate rows and can be eliminated before doing a join of any kind, and to put the outer joins at the end. Doing so should cut down on the nested loops which tend to be costly, and hopefully replace them with hash joins. IMHO Hash is best.
Originally posted by gandolf989 I should have guessed this was some sort of sappy application.
Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess
Originally posted by stmontgo Yes follow along closely! Let's see what happens next. My money is on PS Financials with NVISION Oracle 8i with optimizer_max_permutations=80000 but that's just a wild guess
I'm not qualified to make that kind of guess with any accuracy. According to www.dba-oracle.com:
The _optimizer_search_limit and _optimizer_max_permutations parameters work together, and the optimizer will generate possible table joins permutations until the value specified by _optimizer_search_limit or _optimizer_max_permutations is exceeded. When the optimizer stops evaluating table join combinations, it will choose the combination with the lowest cost. For example, queries joining nine tables together will exceed the optimizer_search_limit but still may spend expensive time attempting to evaluate all 362,880 possible table join orders (nine factorial) until the optimizer_max_permutations parameter has exceeded its default limit of 80,000 table join orders.
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
Originally posted by balajiyes Hi Guys thanks for ur effort
My boss gave this query and wanted me to tune. I don't understand any thing...
Then it would benefit you to rewrite it using the advice I gave you above, and then test that query and see if the explain plan changed. By the way you are using manual undo and archivelog is turned off. You might want to do some research and change to automatic undo and turn on archivelog.