Use of DBMS_UTILITY.EXPAND_SQL_TEXT

mpatzwahl

Hi,

i am playing arround with the package DBMS_UTILITY.EXPAND_SQL_TEXT

I thought that is the sql text that the optimizer uses before it starts (doc says only for Views, but it works for other statements also) Testcase:

 WITH function sql_text (text IN CLOB)

   RETURN CLOB IS

   ret CLOB;

   BEGIN

   DBMS_UTILITY.EXPAND_SQL_TEXT (

   input_sql_text => text,

   output_sql_text => ret);

   RETURN ret;

   END;

select sql_text('select d.deptno,ename from emp e right outer join dept d on e.deptno=d.deptno where ename is null') from dual;

=>SELECT "A1"."QCSJ_C000000000300001_2" "DEPTNO","A1"."ENAME_0" "ENAME" FROM (SELECT "A3"."ENAME" "ENAME_0","A3"."DEPTNO" "QCSJ_C000000000300000","A2"."DEPTNO" "QCSJ_C000000000300001_2" FROM "MUNIQ"."EMP" "A3","MUNIQ"."DEPT" "A2" WHERE "A3"."DEPTNO"="A2"."DEPTNO") "A1" WHERE "A1"."ENAME_0" IS NULL

OK test it: Result is empty, but the original Statement produce this output

DEPTNO ENAME

--------- ---------

40 null

So why is the output wrong ?

Thanks

Marco

User_3ABCE

So why is the output wrong ?

The procedure DBMS_UTILITY.EXPAND_SQL_TEXT is buggy. For some queries i got wrong text (invalid identifier).

JohnWatson2

This is a right exclusive outer join. Even though dbms_utility gets it wrong the CBO does get it right, running it as an outer join with a filter:

orclz> set autot trace exp
orclz> select  * from emp right join dept on emp.deptno=dept.deptno where emp.deptno is null;


Execution Plan
----------------------------------------------------------
Plan hash value: 457395871


-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    58 |     6  (17)| 00:00:01 |
|*  1 |  FILTER                       |         |       |       |            |          |
|   2 |   MERGE JOIN OUTER            |         |     1 |    58 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   1 - filter("EMP"."DEPTNO" IS NULL)
   5 - access("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"(+)="DEPT"."DEPTNO")


orclz>

So clearly the output of expand_sql_text is not what the CBO uses.

DBMS_UTILITY.EXPAND_SQL_TEXT gets it right for these two equivalent statements that don't use ANSI join syntax:

orclz> select * from dept where not exists (select 'x' from emp where emp.deptno=dept.deptno);


Execution Plan
----------------------------------------------------------
Plan hash value: 1353548327


----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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


   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


orclz> select * from dept,emp where dept.deptno=emp.deptno(+) and emp.deptno is null;


Execution Plan
----------------------------------------------------------
Plan hash value: 457395871


-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    58 |     6  (17)| 00:00:01 |
|*  1 |  FILTER                       |         |       |       |            |          |
|   2 |   MERGE JOIN OUTER            |         |     1 |    58 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


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


   1 - filter("EMP"."DEPTNO" IS NULL)
   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))


orclz>

I would be interested to know if there are other equivalent SQLs and other exec plans for right exclusive outer join. One would need larger data sets to determine which is the most efficient.

mpatzwahl

Thanks for your answer