Just the plain DDL please...

The other day I need to get some DDL statements from the data dictionary, you can use DBMS_METADATA to get the DDL for you.

 

Let’s take a look at how you can use this, the DDL we want to extract is from the EMP table from SCOTT schema.

 

If we use DBMS_METADATA just like that, we get more than we asked for.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

 

If you don’t need “Storage” clause in the above DDL, you can turn then off using STORAGE option from set_transform_param method.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'STORAGE',FALSE);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  TABLESPACE "USERS"

 

Similarly if you don’t need “tablespace” clause in the above DDL, you can turn then off using TABLESPACE option from set_transform_param method.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',FALSE);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

 

Similarly if you don’t need “Referential integrity” constraint in the above DDL, you can turn then off using REF_CONSTRAINT option from set_transform_param method.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'REF_CONSTRAINTS',FALSE);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

 

 

demo@ORA12C>

 

Similarly if you don’t need “Segment attributes” in the above DDL, you can turn then off using SEGMENT_ATTRIBUTES option from set_transform_param method.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX  ENABLE

   )

 

 

demo@ORA12C>

 

Similarly if you don’t need “primary key” constraint in the above DDL, you can turn then off using CONSTRAINTS option from set_transform_param method.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   )

 

 

demo@ORA12C>

 

If you need the sql terminator after the generated ddl(s) then you can turn them on using SQLTERMINATOR option.

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',true);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   ) ;

 

 

demo@ORA12C>

 

To get the grant(s) – you can make use of “get_dependent_ddl” method from dbms_metadata API.

 

demo@ORA12C> select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','EMP','SCOTT')

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

 

  GRANT SELECT ON "SCOTT"."EMP" TO PUBLIC;

  GRANT SELECT ON "SCOTT"."EMP" TO "DEMO";

 

 

demo@ORA12C>

 

To get the partitioning stuff removed, just set PARTITIONING option to false.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','T')

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

 

  CREATE TABLE "DEMO"."T"

   (    "OWNER" VARCHAR2(128),

        "OBJECT_NAME" VARCHAR2(128),

        "OBJECT_ID" NUMBER,

        "OBJECT_TYPE" VARCHAR2(23)

   )

  PARTITION BY HASH ("OBJECT_ID")

 (PARTITION "P1" ,

 PARTITION "P2" ) ;

 

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'PARTITIONING',false);

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','T') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','T')

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

 

  CREATE TABLE "DEMO"."T"

   (    "OWNER" VARCHAR2(128),

        "OBJECT_NAME" VARCHAR2(128),

        "OBJECT_ID" NUMBER,

        "OBJECT_TYPE" VARCHAR2(23)

   ) ;

 

 

demo@ORA12C>

 

One nice thing is that if you don’t need to modify all the transformation parameter(s) again to go back to default, they made it really easy to return to the default setting(s).

 

demo@ORA12C> exec dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'DEFAULT');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')

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

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

 

 

demo@ORA12C>

 

Band join

 

One of many optimizer enhancements that appeared in Oracle 12cR2 for sql is the “Band join” that makes certain type of “merge join” much more efficient.

 

demo@ORA12C> create table t1

  2  as

  3  select a.*, rownum as id

  4  from all_objects a

  5  where rownum <= 10000;

 

Table created.

 

demo@ORA12C> create table t2

  2  as

  3  select a.*, rownum as id

  4  from all_objects a

  5  where rownum <= 10000;

 

Table created.

 

Now consider the following query that joins two tables of 10,000 rows each using a range predicates on a column which happen to be a unique with sequential values – though we have no constraint/index in place.

 

select *

from t1, t2

where t1.id between t2.id -1

       and t2.id + 2;

 

 

Here are the two execution plans from 11g (11.2.0.4) and 12c (12.2.0.1) database.

 

demo@ORA11G> select *

  2  from t1, t2

  3  where t1.id between t2.id -1

  4     and t2.id + 2;

 

Execution Plan

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

Plan hash value: 1030928244

 

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

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      |    25M|  4390M|       |   890  (43)| 00:00:11 |

|   1 |  MERGE JOIN          |      |    25M|  4390M|       |   890  (43)| 00:00:11 |

|   2 |   SORT JOIN          |      | 10000 |   898K|  2552K|   258   (1)| 00:00:04 |

|   3 |    TABLE ACCESS FULL | T1   | 10000 |   898K|       |    42   (0)| 00:00:01 |

|*  4 |   FILTER             |      |       |       |       |            |          |

|*  5 |    SORT JOIN         |      | 10000 |   898K|  2552K|   258   (1)| 00:00:04 |

|   6 |     TABLE ACCESS FULL| T2   | 10000 |   898K|       |    42   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter("T1"."ID"<="T2"."ID"+2)

   5 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

       filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

 

 

demo@ORA12C> select *

  2  from t1, t2

  3  where t1.id between t2.id -1

  4     and t2.id + 2;

 

Execution Plan

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

Plan hash value: 412793182

 

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

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      | 40000 |  9687K|       |   677   (1)| 00:00:01 |

|   1 |  MERGE JOIN         |      | 40000 |  9687K|       |   677   (1)| 00:00:01 |

|   2 |   SORT JOIN         |      | 10000 |  1210K|  3656K|   338   (1)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| T1   | 10000 |  1210K|       |    57   (2)| 00:00:01 |

|*  4 |   SORT JOIN         |      | 10000 |  1210K|  3656K|   338   (1)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| T2   | 10000 |  1210K|       |    57   (2)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

       filter("T1"."ID"<="T2"."ID"+2 AND

              INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

 

 

The query returns 40000 rows in the output, expect for the values at the extreme ends of the range each, 10K rows in T1 will join with 4 rows (avg) in T2.

 

Notice how the FILTER that appeared in the setp#4 in Oracle 11g database has disappeared in Oracle 12cR2 plans, and the filter predicate that it used to hold is now part of the filter predicate of the SORT JOIN that has been promoted to the setp#4 in the new plan.

 

The merge join operates like this – for each row returned by the SORT JOIN at step#2 it calls step#4 – in 11g example will then call step#5 so the SORT JOIN there happens 10K times (probing the sorted result set in local memory 10K times)

 

So in 11g database, the setp#5 probes the local in-memory dataset starting at the point where T1.ID >= T2.ID-1 – having found the first point in the in-memory set where the access predicate is true, Oracle will walk through the list passing each row back to the FILTER operation as long as the access predicate is still true and it will be true until the end of the list. As each row arrives the FILTER operation oracle checks to see if the filter predicate there is true and passes the row up to the MERGE JOIN operation if it is. We know that each cycle of FILTER operation will start returning false after returning 4 rows from SORT JOIN operation – but Oracle doesn’t. It sends a total of 50M to the FILTER operation and discarded.

 

In 12cR2 database, Oracle has re-engineered the code to eliminate the FILTER operation and test both part of the range predicates (between clauses) in the same subroutine it uses to probe and scan the row source. In 12.2 the SORT JOIN clause will pass only four rows up to the MERGE JOIN operation and stop scanning on the fifth row it reaches. In our example that was an enormous (CPU) savings in subroutine calls and tests.

 

demo@ORA11G> @script.sql

 

PLAN_TABLE_OUTPUT

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

SQL_ID  as368c5f9cgxc, child number 0

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

select * from t1, t2 where t1.id between t2.id -1  and t2.id + 2

 

Plan hash value: 1030928244

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

| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT     |      |      1 |        |  39996 |00:00:25.92 |     278 |

|   1 |  MERGE JOIN          |      |      1 |     25M|  39996 |00:00:25.92 |     278 |

|   2 |   SORT JOIN          |      |      1 |  10000 |  10000 |00:00:00.02 |     139 |

|   3 |    TABLE ACCESS FULL | T1   |      1 |  10000 |  10000 |00:00:00.01 |     139 |

|*  4 |   FILTER             |      |  10000 |        |  39996 |00:00:25.89 |     139 |

|*  5 |    SORT JOIN         |      |  10000 |  10000 |     50M|00:00:19.83 |     139 |

|   6 |     TABLE ACCESS FULL| T2   |      1 |  10000 |  10000 |00:00:00.01 |     139 |

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

Predicate Information (identified by operation id):

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

   4 - filter("T1"."ID"<="T2"."ID"+2)

   5 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

       filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

         

25 rows selected.

 

 

demo@ORA12C> @script.sql

 

PLAN_TABLE_OUTPUT

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

SQL_ID  as368c5f9cgxc, child number 0

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

select * from t1, t2 where t1.id between t2.id -1  and t2.id + 2

 

Plan hash value: 412793182

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

| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT    |      |      1 |        |  39996 |00:00:00.06 |     410 |

|   1 |  MERGE JOIN         |      |      1 |  40000 |  39996 |00:00:00.06 |     410 |

|   2 |   SORT JOIN         |      |      1 |  10000 |  10000 |00:00:00.02 |     205 |

|   3 |    TABLE ACCESS FULL| T1   |      1 |  10000 |  10000 |00:00:00.01 |     205 |

|*  4 |   SORT JOIN         |      |  10000 |  10000 |  39996 |00:00:00.04 |     205 |

|   5 |    TABLE ACCESS FULL| T2   |      1 |  10000 |  10000 |00:00:00.01 |     205 |

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

Predicate Information (identified by operation id):

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

   4 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1)

       filter(("T1"."ID"<="T2"."ID"+2 AND INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-1))

 

 

23 rows selected.

 

 

And the Tkprof from 11g and 12c database shows this.

 

11g database:

 

select *

from t1, t2

where t1.id between t2.id -1

       and t2.id + 2

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      268     24.89      25.03          0        278          0       39996

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

total      270     24.89      25.03          0        278          0       39996

 

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 91 

Number of plan statistics captured: 1

 

Rows (max)  Row Source Operation

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

     39996  MERGE JOIN  (cr=278 pr=0 pw=0 time=890 us starts=24986311 cost=890 size=4603680368 card=25020002)

     10000   SORT JOIN (cr=139 pr=0 pw=0 time=258 us starts=25996 cost=258 size=920000 card=10000)

     10000    TABLE ACCESS FULL T1 (cr=139 pr=0 pw=0 time=42 us starts=4157 cost=42 size=920000 card=10000)

     39996   FILTER  (cr=139 pr=0 pw=0 time=0 us starts=24949817)

  50014999    SORT JOIN (cr=139 pr=0 pw=0 time=258 us starts=19168333 cost=258 size=920000 card=10000)

     10000     TABLE ACCESS FULL T2 (cr=139 pr=0 pw=0 time=42 us starts=3500 cost=42 size=920000 card=10000)

 

 

12c database:

 

select *

from t1, t2

where t1.id between t2.id -1

       and t2.id + 2

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch      268      0.03       0.11          0        402          8       39996

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

total      270      0.03       0.11          0        402          8       39996

 

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 108 

Number of plan statistics captured: 1

 

Rows (max)  Row Source Operation

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

     39996  MERGE JOIN  (cr=402 pr=0 pw=0 time=65577 us starts=1 cost=677 size=9920000 card=40000)

     10000   SORT JOIN (cr=201 pr=0 pw=0 time=19356 us starts=1 cost=338 size=1240000 card=10000)

     10000    TABLE ACCESS FULL T1 (cr=201 pr=0 pw=0 time=3630 us starts=1 cost=57 size=1240000 card=10000)

     39996   SORT JOIN (cr=201 pr=0 pw=0 time=42567 us starts=10000 cost=338 size=1240000 card=10000)

     10000    TABLE ACCESS FULL T2 (cr=201 pr=0 pw=0 time=3285 us starts=1 cost=57 size=1240000 card=10000)

 

 

One the other note, this band join is applicable only for range predicates involving constants (using binds or literals) – but not for expression involving column names something like this.

 

demo@ORA12C> set autotrace traceonly explain

demo@ORA12C> select *

  2  from t1, t2

  3  where t1.id between t2.id - t2.object_id

  4    and t2.id + t2.object_id ;

 

Execution Plan

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

Plan hash value: 1030928244

 

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

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      |   250K|    59M|       |   713   (6)| 00:00:01 |

|   1 |  MERGE JOIN          |      |   250K|    59M|       |   713   (6)| 00:00:01 |

|   2 |   SORT JOIN          |      | 10000 |  1210K|  3656K|   338   (1)| 00:00:01 |

|   3 |    TABLE ACCESS FULL | T1   | 10000 |  1210K|       |    57   (2)| 00:00:01 |

|*  4 |   FILTER             |      |       |       |       |            |          |

|*  5 |    SORT JOIN         |      | 10000 |  1210K|  3656K|   338   (1)| 00:00:01 |

|   6 |     TABLE ACCESS FULL| T2   | 10000 |  1210K|       |    57   (2)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter("T1"."ID"<="T2"."ID"+"T2"."OBJECT_ID")

   5 - access(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-"T2"."OBJECT_ID")

       filter(INTERNAL_FUNCTION("T1"."ID")>="T2"."ID"-"T2"."OBJECT_ID")

 

demo@ORA12C> set autotrace off

demo@ORA12C>

 

Addendum: added on 3/31/2020

 

Currently the band join is only possible if the lower bound uses subtraction and the upper bound is an addition

 

c##rajesh@QES1> select banner_full from v$version;

 

BANNER_FULL

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

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

c##rajesh@QES1> create table t1 as select * from all_objects;

 

Table created.

 

c##rajesh@QES1> create table t2 as select * from all_objects;

 

Table created.

 

c##rajesh@QES1> set autotrace traceonly explain

c##rajesh@QES1> select *

  2  from t1, t2

  3  where t1.object_id between

  4  t2.object_id + 5 and t2.object_id + 10;

 

Execution Plan

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

Plan hash value: 1030928244

 

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

| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT     |      |  1129M|   281G|       | 12389  (62)| 00:00:01 |

|   1 |  MERGE JOIN          |      |  1129M|   281G|       | 12389  (62)| 00:00:01 |

|   2 |   SORT JOIN          |      | 67219 |  8796K|    25M|  2380   (1)| 00:00:01 |

|   3 |    TABLE ACCESS FULL | T1   | 67219 |  8796K|       |   369   (1)| 00:00:01 |

|*  4 |   FILTER             |      |       |       |       |            |          |

|*  5 |    SORT JOIN         |      | 67220 |  8796K|    25M|  2380   (1)| 00:00:01 |

|   6 |     TABLE ACCESS FULL| T2   | 67220 |  8796K|       |   369   (1)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+10)

   5 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)

       filter(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"+5)

 

c##rajesh@QES1> select *

  2  from t1, t2

  3  where t1.object_id between

  4  t2.object_id - 5 and t2.object_id + 10;

 

Execution Plan

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

Plan hash value: 412793182

 

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

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT    |      |   972K|   248M|       |  4762   (1)| 00:00:01 |

|   1 |  MERGE JOIN         |      |   972K|   248M|       |  4762   (1)| 00:00:01 |

|   2 |   SORT JOIN         |      | 67219 |  8796K|    25M|  2380   (1)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| T1   | 67219 |  8796K|       |   369   (1)| 00:00:01 |

|*  4 |   SORT JOIN         |      | 67220 |  8796K|    25M|  2380   (1)| 00:00:01 |

|   5 |    TABLE ACCESS FULL| T2   | 67220 |  8796K|       |   369   (1)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)

       filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+10 AND

              INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)

 

c##rajesh@QES1> set autotrace off