The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, Rajeshwaran.
Asked: March 25, 2020 - 3:10 pm UTC
Last updated: March 27, 2020 - 10:26 am UTC
Version: 12.2.0.1
Viewed 1000+ times
demo@PDB1> create table t1 as select * from dba_objects; Table created. demo@PDB1> create table t2 as select * from dba_objects; Table created. demo@PDB1> set autotrace traceonly explain demo@PDB1> select * 2 from t1, t2 3 where t1.object_id between 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 | | 1340M| 331G| | 16218 (69)| 00:00:01 | | 1 | MERGE JOIN | | 1340M| 331G| | 16218 (69)| 00:00:01 | | 2 | SORT JOIN | | 73222 | 9510K| 27M| 2576 (1)| 00:00:01 | | 3 | TABLE ACCESS FULL | T1 | 73222 | 9510K| | 399 (1)| 00:00:01 | |* 4 | FILTER | | | | | | | |* 5 | SORT JOIN | | 73223 | 9510K| 27M| 2576 (1)| 00:00:01 | | 6 | TABLE ACCESS FULL| T2 | 73223 | 9510K| | 399 (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)
set serveroutput off create table t1 as select * from dba_objects; create table t2 as select * from dba_objects; explain plan for select * from t1, t2 where t1.object_id between t2.object_id+5 and t2.object_id+10; ------------------------------------- | Id | Operation | Name | ------------------------------------- | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | | 2 | SORT JOIN | | | 3 | TABLE ACCESS FULL | T1 | |* 4 | FILTER | | |* 5 | SORT JOIN | | | 6 | TABLE ACCESS FULL| T2 | ------------------------------------- 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) select * from table(dbms_xplan.display(null, null, 'BASIC LAST +PREDICATE')); explain plan for select * from t1, t2 where t1.object_id between t2.object_id-5 and t2.object_id+5; select * from table(dbms_xplan.display(null, null, 'BASIC LAST +PREDICATE')); ------------------------------------ | Id | Operation | Name | ------------------------------------ | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN | | | 2 | SORT JOIN | | | 3 | TABLE ACCESS FULL| T1 | |* 4 | SORT JOIN | | | 5 | TABLE ACCESS FULL| T2 | ------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - access(INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5) filter("T1"."OBJECT_ID"<="T2"."OBJECT_ID"+5 AND INTERNAL_FUNCTION("T1"."OBJECT_ID")>="T2"."OBJECT_ID"-5)
Get all the information about database performance in the Database Performance guide.