Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Justin.

Asked: October 20, 2017 - 1:55 pm UTC

Last updated: January 31, 2022 - 2:27 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi there,

I have an example below which I'm not sure how oracle execute the where clause.

Select a.id, b.column_A, b.column_B, b.column_C
from A left join B
on a.id = b.id
where b.column_C = 'Yes'

My question is that do oracle execute the 'where' clause first to filter the records from table b then do the join.
OR it will join the 2 tables first, then filter on the result of join.

If it's the 2nd guess, it would actually converting this left join to an inner join, is that right?

and Connor said...

It is the latter.

So the "on" is the join condition, and *where* clause is the filtering. Which is why you'll see the optimizer choose an inner join not an outer one

SQL> create table a as select object_id id, d.* from dba_objects d;

Table created.

SQL> create table b as select * from a;

Table created.

SQL>
SQL> set autotrace traceonly explain
SQL> select *
  2  from A left join B
  3  on a.id = b.id
  4  where b.owner = 'XXXX';

Execution Plan
----------------------------------------------------------
Plan hash value: 4090908061

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2232 |   597K|   881   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2232 |   597K|   881   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| B    |  2232 |   298K|   439   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| A    | 78404 |    10M|   441   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."ID"="B"."ID")
   2 - filter("B"."OWNER"='XXXX')

SQL> set autotrace off
SQL> select *
  2  from A left join B
  3  on a.id = b.id
  4  where b.owner = 'XXXX';   <===  nothing matches this

no rows selected


Compare this to your owner clause being *part* of the join

SQL> set autotrace off
SQL> set feedback only
SQL> select *
  2  from A left join B
  3  on a.id = b.id
  4  and b.owner = 'XXXX';

78404 rows selected.    <<====


Rating

  (3 ratings)

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

Comments

Still valid in 19c?

Hans, January 30, 2022 - 6:12 pm UTC

I have heard that with 19c "where" has been improved, especially "where ... in <long list>" Is this answer still correct?

Connor McDonald
January 31, 2022 - 2:27 am UTC

I have not seen or heard anything to that effect to be honest

Thanks for your fast response

Hans, January 31, 2022 - 5:34 pm UTC

Thanks for your fast response. I will try continue to find the source of of these statements, maybe Hibernation, because it seems not to support the new private temporary tables:

https://github.com/hibernate/hibernate-orm/discussions/4751

SYS.QDCIVARCHAR2LIST

Hans, February 03, 2022 - 6:21 am UTC

According due to my tests replacing

WHERE x IN ('ab','cd') OR IN ('ef','gh')

with

WHERE x IN (SELECT * FROM sys.odcivarchar2list('ab','cd') UNION ALL sys.odcivarchar2list('ef','gh') )

(assuming used after JOINS with big results and long lists) is much faster, but not as fast as creating a temp. table.

Crossposted from here: https://asktom.oracle.com/pls/apex/asktom.search?tag=limit-and-conversion-very-long-in-list-where-x-in#9546068800346423625

So I assume, then the filtering is handles as as JOIN, including the usage of the opitmizer.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.