Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anthony.

Asked: August 04, 2016 - 8:43 am UTC

Answered by: Chris Saxon - Last updated: August 09, 2016 - 1:50 pm UTC

Category: Database - Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

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.

and we said...

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

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

August 10, 2016 - 1:30 am UTC

Reviewer: A reader

Thanks! the explanation helps a lot. one new info about the optimizer.

More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here