Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: January 19, 2017 - 2:41 am UTC

Last updated: January 20, 2017 - 12:15 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Team,

I was reading through this In-memory blog post https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database3

I am trying to construct a test script for Swap Join Inputs Optimization, but un-successful with that. (I was able to get Blooming filters, but no swap join inputs).

could you help me with a test script for demonstrating Swap Join Inputs Optimization.

BIG_TABLE is a copy of all_objects again and again, having 10M rows.

drop table t1 purge;
drop table t2 purge;

create table t1 
as
select *
from all_objects
where rownum <=5000;

create table t2 
as
select *
from dba_objects
where rownum <=5000;

alter table t1 inmemory;
alter table t2 inmemory;
select count(*) from t1;
select count(*) from t2;

delete from plan_table;
commit;
explain plan for 
select b.owner, sum(b.object_id)
from big_table b, t2 ,t1
where b.object_id = t2.object_id
and b.object_id = t1.object_id
and b.owner = 'SYS'
and t1.object_id between 30 and 50
and t2.owner ='SYS'
group by b.owner;

select * from table(dbms_xplan.display);


demo@ORA12C> explain plan for
  2  select b.owner, sum(b.object_id)
  3  from big_table b, t2 ,t1
  4  where b.object_id = t2.object_id
  5  and b.object_id = t1.object_id
  6  and b.owner = 'SYS'
  7  and t1.object_id between 30 and 50
  8  and t2.owner ='SYS'
  9  group by b.owner;

Explained.

demo@ORA12C>
demo@ORA12C> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
Plan hash value: 3686232791

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |     2 |    50 |  1999   (5)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT           |           |     2 |    50 |  1999   (5)| 00:00:01 |
|*  2 |   HASH JOIN                     |           |     2 |    50 |  1999   (5)| 00:00:01 |
|   3 |    JOIN FILTER CREATE           | :BF0000   |     5 |   105 |  1993   (5)| 00:00:01 |
|*  4 |     HASH JOIN                   |           |     5 |   105 |  1993   (5)| 00:00:01 |
|   5 |      JOIN FILTER CREATE         | :BF0001   |     5 |    50 |     5   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS INMEMORY FULL| T2        |     5 |    50 |     5   (0)| 00:00:01 |
|   7 |      JOIN FILTER USE            | :BF0001   |    71 |   781 |  1988   (5)| 00:00:01 |
|*  8 |       TABLE ACCESS INMEMORY FULL| BIG_TABLE |    71 |   781 |  1988   (5)| 00:00:01 |
|   9 |    JOIN FILTER USE              | :BF0000   |    21 |    84 |     5   (0)| 00:00:01 |
|* 10 |     TABLE ACCESS INMEMORY FULL  | T1        |    21 |    84 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access("B"."OBJECT_ID"="T1"."OBJECT_ID")
   4 - access("B"."OBJECT_ID"="T2"."OBJECT_ID")
   6 - inmemory("T2"."OBJECT_ID"<=50 AND "T2"."OWNER"='SYS' AND "T2"."OBJECT_ID">=30)
       filter("T2"."OBJECT_ID"<=50 AND "T2"."OWNER"='SYS' AND "T2"."OBJECT_ID">=30)
   8 - inmemory("B"."OBJECT_ID"<=50 AND "B"."OWNER"='SYS' AND "B"."OBJECT_ID">=30
              AND SYS_OP_BLOOM_FILTER(:BF0001,"B"."OBJECT_ID"))
       filter("B"."OBJECT_ID"<=50 AND "B"."OWNER"='SYS' AND "B"."OBJECT_ID">=30 AND
              SYS_OP_BLOOM_FILTER(:BF0001,"B"."OBJECT_ID"))
  10 - inmemory("T1"."OBJECT_ID"<=50 AND "T1"."OBJECT_ID">=30 AND
              SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJECT_ID"))
       filter("T1"."OBJECT_ID"<=50 AND "T1"."OBJECT_ID">=30 AND
              SYS_OP_BLOOM_FILTER(:BF0000,"T1"."OBJECT_ID"))

33 rows selected.

demo@ORA12C>

and we said...

SWAP_JOIN_INPUTS is a cost based query transformation that the Optimizer uses to swap which side of a HASH JOIN a table will appear.

It is typically used when an extremely large table is joined to two or more much smaller tables and storing the intermediate result set between the joins would be extremely expensive.

In your case, the join order is T2 -> B -> T1. In order for SWAP_JOIN_INPUTS to kick in the intermediate result set after joining T2 to B would have to be large enough to be considered expensive to store, which is not the case (estimate 5 rows).

In summary, the BIG_TABLE is simply not large enough to trigger the SWAP_JOIN_INPUTS transformation.

In order to get the transformation to kick in I made the following changes to your testcase:

1. Increase BIG_TABLE to be 26 million rows

2. Remove the where clause predicate on BIG_TABLE (b.owner=’SYS’) as it was to selective

4. Changed the join column between the BIG_TABLE and T1 to avoid transitive predicates

3. Changed the where clause predicate on t1 to be more selective

So, the query becomes:

select b.owner, sum(b.object_id)
from big_table b, t2 ,t1
where b.object_id = t2.object_id
and b.data_object_id = t1.data_object_id
and t1.object_type='VIEW'
and t2.owner ='SYS'
group by b.owner;

And the execution plan becomes:
--------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |    | 24 |   768 | 10793   (3)| 00:00:01 |
|   1 |  HASH GROUP BY         |    | 24 |   768 | 10793   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |    |   442 | 14144 | 10792   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS INMEMORY FULL  | T2    |  3187 | 31870 |  1   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |    | 12368 |   265K| 10791   (3)| 00:00:01 |
|   5 |     JOIN FILTER CREATE        | :BF0000   | 22 |   198 |  1   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS INMEMORY FULL| T1    | 22 |   198 |  1   (0)| 00:00:01 |
|   7 |     JOIN FILTER USE        | :BF0000   |  4322K| 53M| 10778   (3)| 00:00:01 |
|*  8 |      TABLE ACCESS INMEMORY FULL| BIG_TABLE |  4322K| 53M| 10778   (3)| 00:00:01 |
--------------------------------------------------------------------------------------------



Now if we check the outline for the query by including the +outline argument in our plan display command
select * from table(dbms_xplan.display(format=>'TYPICAL +outline'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3144656435

--------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |    | 24 |   768 | 10793   (3)| 00:00:01 |
|   1 |  HASH GROUP BY         |    | 24 |   768 | 10793   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |    |   442 | 14144 | 10792   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS INMEMORY FULL  | T2    |  3187 | 31870 |  1   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |    | 12368 |   265K| 10791   (3)| 00:00:01 |
|   5 |     JOIN FILTER CREATE        | :BF0000   | 22 |   198 |  1   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS INMEMORY FULL| T1    | 22 |   198 |  1   (0)| 00:00:01 |
|   7 |     JOIN FILTER USE        | :BF0000   |  4322K| 53M| 10778   (3)| 00:00:01 |
|*  8 |      TABLE ACCESS INMEMORY FULL| BIG_TABLE |  4322K| 53M| 10778   (3)| 00:00:01 |
--------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$1" "T2"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "B"@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

   2 - access("B"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - inmemory("T2"."OWNER"='SYS')
       filter("T2"."OWNER"='SYS')
   4 - access("B"."DATA_OBJECT_ID"="T1"."DATA_OBJECT_ID")
   6 - inmemory("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='VIEW')
       filter("T1"."DATA_OBJECT_ID" IS NOT NULL AND "T1"."OBJECT_TYPE"='VIEW')
   8 - inmemory("B"."DATA_OBJECT_ID" IS NOT NULL AND
       SYS_OP_BLOOM_FILTER(:BF0000,"B"."DATA_OBJECT_ID"))
       filter("B"."DATA_OBJECT_ID" IS NOT NULL AND
       SYS_OP_BLOOM_FILTER(:BF0000,"B"."DATA_OBJECT_ID"))

51 rows selected.


If we examine the outline (set of hints required to reproduce the plan), we see from the LEADING hint that the join order has changed to T1 -> B -> T2.

But that table T2 will swap side on its HASH JOIN because of the SWAP_JOIN_INPUTS hint.

The optimizer assumes that the number of rows coming from T2 will be a lot smaller than the number of rows coming from the HASH JOIN between T1 and B.

Remember in a hash join, the optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger data source, and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row. Thus T2 belongs on the left hand side of the JOIN.

So, the first step executed in the plan is actual the scan of T2, the results of which are saved (in the form of the hash table built by the hash join) while we then do the join between T1 and B. Finally, the results from the full table scan on T2 are joined to output of the T1 and B HASH JOIN.

Rating

  (1 rating)

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

Comments

Order of plan execution

Rajeshwaran, Jeyabal, January 20, 2017 - 7:54 am UTC

....
So, the first step executed in the plan is actual the scan of T2, the results of which are saved (in the form of the hash table built by the hash join) while we then do the join between T1 and B. Finally, the results from the full table scan on T2 are joined to output of the T1 and B HASH JOIN.
....

Thanks for the explanation.

So given this plan
--------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |    | 24 |   768 | 10793   (3)| 00:00:01 |
|   1 |  HASH GROUP BY         |    | 24 |   768 | 10793   (3)| 00:00:01 |
|*  2 |   HASH JOIN         |    |   442 | 14144 | 10792   (3)| 00:00:01 |
|*  3 |    TABLE ACCESS INMEMORY FULL  | T2    |  3187 | 31870 |  1   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |    | 12368 |   265K| 10791   (3)| 00:00:01 |
|   5 |     JOIN FILTER CREATE        | :BF0000   | 22 |   198 |  1   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS INMEMORY FULL| T1    | 22 |   198 |  1   (0)| 00:00:01 |
|   7 |     JOIN FILTER USE        | :BF0000   |  4322K| 53M| 10778   (3)| 00:00:01 |
|*  8 |      TABLE ACCESS INMEMORY FULL| BIG_TABLE |  4322K| 53M| 10778   (3)| 00:00:01 |
--------------------------------------------------------------------------------------------


The order of the plan execution is 3,6,5,8,7,4,2,1,0 - is that i am getting it right ?
Connor McDonald
January 20, 2017 - 12:15 pm UTC

Yes. Whilst ORacle can only join 2 tables at once, it can *commence* more than 2 joins, when you see plans like:

<code>
hash join
table
hash join
table
hash join
<code>

etc

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library