Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Anthony.

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

Last updated: August 09, 2016 - 1:50 pm UTC

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

Rating

  (1 rating)

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

Comments

A reader, August 10, 2016 - 1:30 am UTC

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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.