Friday, December 18, 2009

Query and Transitive Closure

I have got some of the comments on my last blog - "Query Performance & Join Conditions" which were asking about the transitive closure. The example that I have given was not for transitive closure. Oracle has introduced transitive

closure when the literal values are present for joining conditions. For example when I have join condition on two tables and same column of one of the table is having filter condition then other table will use the same filter condition.

To demonstrate, we will see the following test case.


SQL> create table t1 as select rownum "N", rownum*30 "M" from dual connect by level <=10000;

Table created.


SQL> create table t2 as select rownum+20 "R", rownum*50 "T" from dual connect by level <=10000;

Table created.



SQL> create index ind_t1 on t1(n);

Index created.



SQL> create index ind_t2 on t2(r);

Index created.



SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.



SQL> set autot trace exp stat
SQL> select n, m, t
2 from t1, t2
3 where t1.n = t2.r
4 and t1.n=30;


Execution Plan
----------------------------------------------------------
Plan hash value: 863060763

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 4 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 1 | 16 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 8 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 8 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_T2 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

3 - access("T1"."N"=30)
6 - access("T2"."R"=30)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
512 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

Check for "Predicate Information" in above-mentioned execution plan. When I have assigned n=30, it has also picked up r=30. Now one might have question, what is the use of this and will it impact performance?

Based on the input filter condition, oracle may opt for better execution plan.
To demonstrate, lets create t2 table with R column as 4 distinct values and execute the above query again.


SQL> create table t1 as select rownum "N", rownum*30 "M" from dual connect by level <=10000;

Table created.



SQL> create table t2 as select mod(rownum,4) "R", rownum*50 "T" from dual connect by level <=10000;

Table created.



SQL> create index ind_t1 on t1(n);

Index created.



SQL> create index ind_t2 on t2(r);

Index created.



SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);

PL/SQL procedure successfully completed.



SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

PL/SQL procedure successfully completed.



SQL> set autot trace exp stat
SQL> select n, m, t
2 from t1, t2
3 where t1.n = t2.r
4 and t2.r=3;

2500 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2470703826

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 37500 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN | | 2500 | 37500 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 8 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_T1 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T2 | 2500 | 17500 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - access("T1"."N"="T2"."R")
3 - access("T1"."N"=3)
4 - filter("T2"."R"=3)


As per the above plan, based on filter condition(N=3), oracle opted for index scan. This is because of transitive closure. Now what would have happen if transitive closure was not taken place.
For this, lets modify query as follows -


SQL> select n, m, t
2 from t1, (select /*+ no_merge */ * from t2 where r=3) t2
3 where t1.n = t2.r;

2500 rows selected.



Execution Plan
----------------------------------------------------------
Plan hash value: 157564253

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 90000 | 14 (8)| 00:00:01 |
|* 1 | HASH JOIN | | 2500 | 90000 | 14 (8)| 00:00:01 |
| 2 | VIEW | | 2500 | 70000 | 6 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T2 | 2500 | 17500 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 10000 | 80000 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - access("T1"."N"="T2"."R")
3 - filter("R"=3)


If we execute same query, oracle would have opted for nested loops.

Conclusion : Transitive closure is the feature which works when we are using filter predicate in query with join condition. Oracle will consider transitive closure with all equility, non-equility predicates.

No comments: