Nov 28, 2008

Overlap,Explain Plan

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 * 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: