Skip to Main Content
  • Questions
  • In explain plan one of the tables in the query is not even scanned for joining , Do you know why ?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sid.

Asked: September 19, 2018 - 1:53 am UTC

Last updated: September 19, 2018 - 10:44 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

I am running a query like below

explain plan for
select count(1)
from A , B
where A.column1=1
and A.column2=3
and A.column3=b.column3(+)


When I check the explain plan I can see the sort aggregate and other things ,However I do not see any access operation in Table B .
Can you help me answer why optimizer is doing it ?

and Chris said...

Assuming that b.column3 has a primary/unique key constraint on it, left joining B makes no difference to the result.

This is because for each row in A, you have at most one row in B. Unlike an inner join, an outer join preserves all the rows from the driving table (A). So there's no need to read B.

But to do this optimization, you must have a constraint enforcing uniqueness:

set serveroutput off
create table t1 (
  c1 int 
);
create table t2 (
  c1 int 
);

insert into t1 values ( 1 );
insert into t1 values ( 2 );
insert into t2 values ( 1 );
commit;

select count (*) from t1
left   join t2
on     t1.c1 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

EXPLAINED SQL STATEMENT:                                       
------------------------                                       
select count (*) from t1 left   join t2 on     t1.c1 = t2.c1   
                                                               
Plan hash value: 4045007133                                    
                                                               
------------------------------------                           
| Id  | Operation           | Name |                           
------------------------------------                           
|   0 | SELECT STATEMENT    |      |                           
|   1 |  SORT AGGREGATE     |      |                           
|   2 |   HASH JOIN OUTER   |      |                           
|   3 |    TABLE ACCESS FULL| T1   |                           
|   4 |    TABLE ACCESS FULL| T2   |                           
------------------------------------  

alter table t2 add primary key ( c1 );

select count (*) from t1
left   join t2
on     t1.c1 = t2.c1;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

EXPLAINED SQL STATEMENT:                                       
------------------------                                       
select count (*) from t1 left   join t2 on     t1.c1 = t2.c1   
                                                               
Plan hash value: 1634389831                                    
                                                               
-----------------------------------                            
| Id  | Operation          | Name |                            
-----------------------------------                            
|   0 | SELECT STATEMENT   |      |                            
|   1 |  SORT AGGREGATE    |      |                            
|   2 |   TABLE ACCESS FULL| T1   |                            
----------------------------------- 

Rating

  (1 rating)

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

Comments

from optimizer blog

Rajeshwaran, Jeyabal, September 20, 2018 - 11:43 am UTC

Maria - has gone through this optimization in detail at this blog

https://blogs.oracle.com/optimizer/why-are-some-of-the-tables-in-my-query-missing-from-the-plan

and few more details available at this AskTom link.

https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-tune-access-of-fat-tables#9535983800346671794

BTW - prior to 12.2 this optimization can kick in only for single column PK/FK. starting with 12.2 this optimization is possible for composite PK/FK's also.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database