UNDERSTANDING EXPLAIN PLAN

    Explain Plan a small overview

            To interpret an execution plan and correctly evaluate your SQL optimization options, you need to first understand the differences between the available database operations. The following topics describe each database access operation identified by the name given to it by the Oracle EXPLAIN PLAN command, along with its characteristics.

For each type of operation, an example is provided. In some cases, the example for an operation will use operations described later in the topic (such as MERGE JOIN, which always uses a SORT JOIN operation).

The operations are classified as row operations or set operations. The following list describes the differences between row and set operations. 

Row Operations	Set Operations
Executed on one row at a time	Executed on a result set of rows
Executed at the FETCH stage, if there is no set operation involved	Executed at the EXECUTE stage when the cursor is opened
Ability to view the first result before the last row is fetched	Inability to view the first result until all rows are fetched and processed
Example: A full-table scan	Example: A full-table scan with a GROUP BY

Aggregation Operations

The following Aggregation operations are available.

COUNT

Counts the rows in the result set to satisfy the COUNT() function

COUNT is executed when the RowNum pseudo-column is used without specifying a maximum value for RowNum. COUNT receives rows from its child operations and increments the RowNum counter. If a limiting counter is used on the RowNum pseudo-column, then the COUNT STOPKEY operation is used instead of COUNT. 
.

Example

.

select Name, City, State, RowNum from COMPANY where City > ‘Roanoke’order by Zip;

.

The query shown in the preceding listing selects rows from the COMPANY. Each row will have the original row number returned.

Execution Plan

SORT ORDER BY
COUNT
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

 

Interpreting the Execution Plan
.

The Execution Plan shows that the index on the City column is used to find ROWIDs in the COMPANY table that satisfy the WHERE clause condition (where City > ‘Roanoke’). The ROWIDs from the City index scan are used to query the COMPANY table for the Name and State column values. For each row returned, the counter is incremented. Because of the use of the index, the rows that are returned will be the "lowest" city names that are greater than the value ‘Roanoke’. The rows will be returned from the COMPANY$CITY index in ascending order of the City column’s value. The RowNum pseudo-column will then be calculated and put into the row. The SORT ORDER BY operation will order the rows by Zip, as requested in the ORDER BY clause.  The RowNum values are assigned before the ordering takes place.

.

STOPKEY

Counts the numbers of rows returned by a result set and stop processing when a certain number of rows are reached. This is usually the result of a WHERE clause which specifies a maximum ROWNUM (for example, WHERE ROWNUM <=10).

­­­Example 

.

 

Select Name, City, State from COMPANY where City > ‘Roanoke’ and Rownum <= 100;
.

Execution Plan

COUNT STOPKEY
TABLE ACCESS BY ROWID COMPANY
INDEX RANGE SCAN COMPANY$CITY

SORT	Performs a sort.
ORDER BY	Sorts a result set to satisfy an ORDER BY clause.
AGGREGATE		Occurs when a group function is used on data, which is already grouped.

SORT AGGREGATE is used to sort and aggregate result sets whenever a grouping function appears in a SQL statement without a GROUP BY clause. Grouping functions include MAX, MIN, COUNT, SUM, and AVG.

Example 

.

select SUM(Total) from SALES;
.

SORT AGGREGATE
TABLE ACCESS FULL SALES
.

The Execution Plan shows that after the SALES table is scanned (via the TABLE ACCESS FULL operation), the records are passed to the SORT AGGREGATE operation. SORT AGGREGATE sums the Total values and returns the output to the user.

JOIN	Sorts the rows in preparation for a merge join.

SORT JOIN sorts a set of records that is to be used in a MERGE JOIN operation.

Example

select C.Name  from COMPANY c, SALES s where C.Company_ID+0 = S.Company_ID+0
and S.Period_ID =3 and S.Sales_Total>1000;

Execution Plan

MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SALES
SORT JOIN
TABLE ACCESS FULL COMPANY

Interpreting the Execution Plan

The Execution Plan shows that the COMPANY table and SALES table will be accessed using TABLE ACCESS FULL operations. Before the records from those tables are passed to the MERGE JOIN operation, they will first be processed by SORT JOIN operations that sort the records. The SORT JOIN output is used as input to the MERGE JOIN operation.

Convert a number to word

select to_char(to_date('1983','J'),'JSP') from dual;

In this query the number is converting into Julian date then by using fromat  'SP' to spell out

Example

  select to_char(sysdate,'DDSP'),to_char(sysdate,'MMSP'),to_char(sysdate,'YYYYSP') from dual  -- Spells the day ,month and the year

 In this only value upto 5373484 can be spelled  ; giving greater value will result in the following error

 ORA-01854: Julian date must be between 1 and 5373484

 select to_char(to_date('190000083','J'),'JSP') from dual;

 ORA-01830: date format picture ends before converting entire input string


Query to Spell  Rupee and Paise

select
TO_CHAR(TO_DATE(substr(10000.123,1,instr(10000.123,'.')-1),'J'),'JSP')||' RUPEES AND  '||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(10000.123,instr(10000.123,'.')+1,length(10000.123)),'J'),'JSP'),'MILLION',''),'HUNDRED',''),'THOUSAND',''),'-',' ')  ||' PAISE ONLY' as number_char
from dual;

----------

Query to convert NUMBER to words

In Oracle 9i


SELECT  LEVEL+1 asnumber FROM dual WHERE to_char(to_date(LEVEL+1,'J'), 'JSP')  = 'ONE THOUSAND'
CONNECT BY to_char(to_date(LEVEL,'J'), 'JSP')  != 'ONE THOUSAND';

OR

SELECT  LEVEL FROM dual WHERE to_char(to_date(LEVEL,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN'
AND LEVEL < 10001  -- Your get out of jail clause!;


In Oracle 10g

select sp, n from (select 'FIVE THOUSAND ONE' sp from dual)
   model dimension by (1 dim) measures (0 n, sp)  rules iterate (10000) until (to_char(date '2000-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])  ( n[1]=ITERATION_NUMBER);


-------

ORA-20171: Workspace Error during BEGIN DDL

SQL > exec dbms_wm.beginddl('TABLE_NAME');

ORA-20171: WM error: 'CREATE TABLE' and 'CREATE SEQUENCE' privileges needed.


This error can be avoided by giving these below  grants to the particular user.

SQL > grant create TABLE  to username ;

SQL >  grant create SEQUENCE  to username;

Then execute the script ;it will be successful

SQL > exec dbms_wm.beginddl('TABLE_NAME');


--------

ORA-20104: cannot version disable this table

ORA-20104: cannot version disable this table
ORA-06512: at "SYS.LT", line 9152
ORA-06512: at line 3

the following error will occur if the TESTP table is a parent table and all the child table that refers TESTP is not versiondisabled .  

 An Example

create table TEST (ee number(10),fg varchar2(100)) ;

ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY (EE);

create table TESTP (ff number(10),ee number(10) ,constraint fk_fg foreign key(ee) references TEST(ee) );

ALTER TABLE TESTP ADD CONSTRAINT TESTp_PK PRIMARY KEY (ff);

execute dbms_wm.enableversioning('TEST');

execute dbms_wm.enableversioning('TESTP');

execute dbms_wm.disableversioning('TESTP');  This will result an error ( ORA-20104: cannot version disable this table )

to avoid this first you should disableversion child table  TEST and then parent table TESTP

execute dbms_wm.disableversioning('TEST');

execute dbms_wm.disableversioning('TESTP');

Impact of SQL Injection

   Retrieval of Crucial Data
   Data Manupulation
   Alter database State
   Revoke Database Service

Developers are ignorant that their above query can be misused to an extent that it can list login names and passwords, which is relevant and crucial information for an organization. An invader can give input as
Code :

p'or upper(s_empname) like'%
p'or'p'='p

Code sql:

SELECT USERNAME, PASSWORD FROM USERS
WHERE USERNAME = ''
OR 1=1

SQL Injection: Example

A procedure P_GET_SAL was created to get the salary of input Employee Id.

Code sql:

CREATE OR REPLACE PROCEDURE P_GET_SAL  (P_ENAME VARCHAR2 DEFAULT NULL)
AS
CUR SYS_REFCURSOR;
V_ENAME VARCHAR2(100);
V_SAL NUMBER;
BEGIN
  V_STMT := 'SELECT ENAME, SALARY FROM EMPLOYEE  WHERE ENAME = '''|| P_ENAME || '''';
  DBMS_OUTPUT.PUT_LINE(V_STMT); 
  OPEN CUR FOR V_STMT;
  LOOP
    FETCH CUR INTO V_ENAME, V_SAL;
    EXIT WHEN CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Employee : '||V_ENAME||' draws '||TO_CHAR(V_SAL));
  END LOOP;
  CLOSE CUR;
END;



Code sql:

SQL> EXEC P_GET_SAL(‘KING’);
Employee KING draws 4500

PL/SQL PROCEDURE successfully completed.


Code sql:

SQL> EXEC P_GET_SAL('KING'' UNION SELECT ENAME, SALARY FROM EMPLOYEE WHERE 1=1');
Employee KING draws 4500
Employee ALLEN draws 1200
Employee MIKE draws 3400
Employee KATE draws 2300
Employee PAUL draws 6400
Employee TOMY draws 2700
Employee JENNY draws 6200
Employee JAKES draws 4600

PL/SQL PROCEDURE successfully completed.



Several strategies can be adopted to safeguard the SQL code and eradicate the impacts of SQL injection in applications. Some of them are listed below.

1. Use of Static SQL
2. Using Invoker’s rights
3. Use of Dynamic SQL with bind arguments
4. Validate and sanitize input using DBMS_ASSERT

-------