Need help to create outline using first_rows(100) hint without changing the plan
oracle db version: 10.2.0.3.0
optimizer mode: ALL_ROWS
Some observations on my query select a1.col1, a1.col2... from a1,...a39 where <40 conditions> which is expected to run within few seconds (10-15 seconds at most):
1. without any hint costs 1,330,000+ and doesn't fetch anything within 60 seconds.
2. with /*+FIRST_ROWS(100)*/ hint costs only 5,600+ and fetch result within 15 seconds.
I tried to create outline on the query no. 2 (hinted query) and have the following observations:
1. before creating outline, query costs 5,600+ and fetch results within 15 seconds.
2. after creating outline, cost of the same query increased to 1,350,000+ and again fetched nothing
3. then dropped the outline and found the query cost reduced to 5,600+ and fetching result