I have a query that goes like this
select * from (
select a.id, b.col1, c.col2, d.col3
FROM table1 a, table2 b, table3 c, table4 d
WHERE a.id = b.id
AND b.id2 = c.id2
AND c.id3 = d.id3
) QRSLT
where QRSLT.id in (select /*+ qb_name(sq1)*/ distinct id
from table5)
table5 on the non-correlated IN subquery, is it possible to force the optimizer to join table5 on the outer query (QRSLT) and start joining from table5?
if table5 was the driving table, then it would have a much optimal result, less fetch of data on disk.
You can specify which tables to join first with the leading() hint. Use
<table alias>@<qb name>
to reference the appropriate table:
select /*+ gather_plan_statistics leading (d@dep) */* from (
select * from hr.employees
where 1 in (select /*+ qb_name(dl) */1 from dual)
)
where department_id in (
select /*+ qb_name(dep) */department_id from hr.departments d
where location_id = 1800
);
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FILTER | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS FULL | DEPARTMENTS |
| 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
| 7 | FAST DUAL | |
-----------------------------------------------------------
But...
Are you sure you really need to do this?
If Oracle thinks that the table in the subquery returns the fewest rows, it can already use this as the driving table:
select /*+ gather_plan_statistics */* from (
select * from hr.employees
)
where department_id in (
select /*+ qb_name(dep) */department_id from hr.departments d
where location_id = 1800
);
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | DEPARTMENTS |
| 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
----------------------------------------------------------
So if it's not doing this it suggests:
- The subquery isn't the smallest number of rows => you don't want it to be first! Or
- There's an issue with your stats
If neither of these are the case and you're hitting a edge case or bug, you may want to check out SQL plan management and/or SQL profiles instead of directly hinting your query:
http://www.oracle.com/technetwork/database/manageability/sql-profiles-technical-overview-128535.pdf http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf