Forum Stats

  • 3,839,050 Users
  • 2,262,441 Discussions
  • 7,900,841 Comments

Discussions

Use of DBMS_UTILITY.EXPAND_SQL_TEXT

mpatzwahl
mpatzwahl Member Posts: 284 Bronze Badge
edited Aug 4, 2022 9:04AM in General Database Discussions

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

Answers

  • User_3ABCE
    User_3ABCE Member Posts: 53 Blue Ribbon

    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
    JohnWatson2 Member Posts: 4,470 Silver Crown

    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
    mpatzwahl Member Posts: 284 Bronze Badge

    Thanks for your answer