Overlap
If any of the condition in where clause of a query overlaps , with the parameter in overlap function query returns value else not
select 'a' from dual where (sysdate,sysdate+1) overlaps (sysdate,sysdate+1);
---------------------------------------------
SQL> set autotrace on explain
Then execute the SQL script that you want to get the explain plan
--------------------------------------------------------------------------------------------------------
EXPLAIN PLAN
------------------
EXPLAIN PLAN statement determine the execution plan Oracle Database follows to execute a specified
SQL statement.
TO INSERT INTO PLAN TABLE
EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table FOR SELECT * FROM DUAL;
DISPLAYING PLAN_TABLE OUTPUT
----------------------------------------------
select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",object_name
"Object",position, BYTES,CARDINALITY,COST from plan_table start with id = 0
connect by prior id=parent_id;
---------------------------------------
select * from table(dbms_xplan.display);
-----------------------------------------
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));
BASIC : Display only the minimum set of information
TYPICAL: This is the default. Display most information of the explain plan (operation id, name and option,#rows, #bytes and optimizer cost).
ALL: Maximum user level, like typical with additional informations (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).
Some words about PLAN_TABLE resides in sys user with name PLAN_TABLE$ ;which is a ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE
------------------------------------
How to Read Explain Plan
------------------------------------
select * from sys.v_$sql_workarea t
--------------------------------------
select * from sys.v_$sql_plan t
-------------------------------
If any of the condition in where clause of a query overlaps , with the parameter in overlap function query returns value else not
select 'a' from dual where (sysdate,sysdate+1) overlaps (sysdate,sysdate+1);
---------------------------------------------
SQL> set autotrace on explain
Then execute the SQL script that you want to get the explain plan
--------------------------------------------------------------------------------------------------------
EXPLAIN PLAN
------------------
EXPLAIN PLAN statement determine the execution plan Oracle Database follows to execute a specified
SQL statement.
TO INSERT INTO PLAN TABLE
EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table FOR SELECT * FROM DUAL;
DISPLAYING PLAN_TABLE OUTPUT
select * from plan_table t
----------------------------------------------
select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",object_name
"Object",position, BYTES,CARDINALITY,COST from plan_table start with id = 0
connect by prior id=parent_id;
---------------------------------------
select * from table(dbms_xplan.display);
-----------------------------------------
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));
BASIC : Display only the minimum set of information
TYPICAL: This is the default. Display most information of the explain plan (operation id, name and option,#rows, #bytes and optimizer cost).
ALL: Maximum user level, like typical with additional informations (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).
Some words about PLAN_TABLE resides in sys user with name PLAN_TABLE$ ;which is a ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE
------------------------------------
How to Read Explain Plan
------------------------------------
select * from sys.v_$sql_workarea t
--------------------------------------
select * from sys.v_$sql_plan t
-------------------------------
No comments:
Post a Comment