Relational Vs Nested Table

What is the difference between relational table and nested table?

• A relational table can have virtual column, whereas nested table can not have virtual column.
• Primary key, foreign keys are allowed on a relational table, whereas constraints are not allowed on nested table.

Truncate Vs Delete

What is the difference between truncate and delete?

1.  Truncate deletes all records at once unconditionally, whereas delete can delete the records conditionally or unconditionally.
2. Deleted data by truncate command can not be rolled back, whereas deleted data by delete command can be rolled back.
3. Memory will be released after deletion of record by truncate command whereas memory will not be released after deletion of record by deleted command.
4. Truncate is a DDL command, whereas delete is DML command.
5. Trigger does not get fired in case of  TRUNCATE whereas triggers get fired in case of a DELETE command.
6. We can not TRUNCATE a table if table have any foreign key constraint. We need to remove the constraint  then truncate the table and recreate the constraint.

Long Vs Lob Data Type

What is difference between LONG and LOB data types?

Differences between LONG and LOB data type are listed below:

---

LOB

 1) The maximum size is 4GB.

 2) LOBs (except NCLOB) can be attributes of an object type.

 3) LOBs support random access to data.

 4) Multiple LOB columns per table or LOB attributes in an object type.

LONG

 1) The maximum size is 2GB.

 2) LONGs cannot.

 3) LONGs support only sequential access.

 4) Only one LONG column was allowed in a table

EXPLAIN PLAN

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer  to execute a SQL statement.
EXPLAIN PLAN takes less than a minute to EXPLAIN a query that takes four hours to run because it does not actually execute the SQL statement, it only outlines the plan to use and inserts this execution plan in an Oracle table (PLAN_TABLE).

Why we will use EXPLAIN PLAN without TRACE?
The statement is not executed; it only shows what will happen if the statement is executed. 
When do you use EXPLAIN without TRACE?
When the query will take exceptionally long to run.

How to use EXPLAIN PLAN?

1. Create PLAN TABLE: Execute the script "utlxplan.sql". File location is below

oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql    

2. EXPLAIN Query: Run the EXPLAIN PLAN for the query to be optimized 

EXPLAIN PLAN FOR
select ename,sal,empno,deptno
from emp
where deptno=10;    

Using Tag:

EXPLAIN  PLAN FOR

SET STATEMENT_ID='SQL1'

select ename,sal,empno,deptno
from emp
where deptno=10;

3. PLAN Table is populated: Select the output from PLAN TABLE

select operation, options, object_name, id, parent_id
from plan_table
where statement_id = 'SQL1'   

OR we can use below query to see the output in proper format 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);    

It shows the following information: 

• The row source tree is the core of the execution plan. 
• An ordering of the tables referenced by the statement
• An access method for each table mentioned in the statement
• A join method for tables affected by join operations in the statement
• Data operations like filter, sort, or aggregation.

In addition to the row source tree, the plan table contains information about the following: 

• Optimization, such as the cost and cardinality of each operation
• Partitioning, such as the set of accessed partitions
• Parallel execution, such as the distribution method of join inputs

The EXPLAIN PLAN results let you determine whether the optimizer selects a particular execution plan, such as, nested loops join. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loops join instead of a hash join, and lets you understand the performance of a query. 

Query processing can be divided into 7 phases :

• Syntactic          : Checks the syntax of the query
• Semantic          : Checks that all objects exist and are accessible
• View Merging  : Rewrites query as join on base tables as opposed to using views
• Statement Transformation : Rewrites query transforming some complex constructs into simpler ones where appropriate (e.g. subquery merging, in/or transformation)
• Optimization  : Determines the optimal access path for the query to take. With the Rule Based Optimizer (RBO) it uses a set of heuristics to determine access path.  With the Cost Based  Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
• QEP Generation    : QEP = Query Evaluation Plan.
• QEP Execution      : QEP = Query Evaluation Plan.

In Toad how we will work on Explain plan :

Using toad , this can be achieved by following the steps below.

• Connect to the Oracle SID
• Open a SQL editor, and write the SQL query for which the explain plan is required.
• CTRL+E will produce the explain plan for the query - which basically means , this is the most likely path oracle will chose while executing the SQL. 
• Analyze the cost of the query , and identify the areas which are causing the cost to grow high.Mostly this happens when full table access is performed, or hashed joins are used , instead of full index scans and nested loops.
• This is the fastest way to identify if a query you have written has some tuning gaps and can be rewritten to perform better in distributed and scalable high volume environments.

ORA-01008 : Not all variables bound

ERROR: ORA-01008 : Not all variables bound

CAUSE : A SQL statement (or Dynamic SQL ) containing substitution variables was executed without all variables bound.

ACTION: All substitution variables must have a substituted value before the SQL statement is executed.

ORA-06548: no more rows needed

ERROR: ORA-06548: no more rows needed

CAUSE: The caller of a pipelined function does not  need more rows to be produced by the pipelined function.

ACTION: Catch the NO_DATA_NEEDED exception in an exception handling block.
Catching the NO_DATA_NEEDED exception inside the pipelined function allows the function to perform any clean-up needed after the loop.

Example:
The table function returns 1000 rows, but the client (which communicates using ODBC) only fetches 200 at a time. If all the rows are fetched,
then there is no issue; however, if only a subset are fetched before another command is executed, the exception gets raised.

DECLARE

  --...

BEGIN

 ....

EXCEPTION

WHEN no_data_needed
THEN
RETURN;   --clean up the resource

END;

/

CURRENT OF Clause

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.
The most recent row fetched from the table should be updated or deleted.
To use this clause we must declare the cursor with the FOR UPDATE clause.

Oracle provides the FOR UPDATE clause in SQL to allow the developer to lock a set of Oracle rows for the duration of a transaction. In Other sessions can only query the rows but they can not update or delete.

Current of clause is useful when a table doesn't have unique values or the table contains duplicate record or updating a column based on some condition . Otherwise if a table contains unique values or table doesn't contain duplicate records or updating columns without any conditions then no need of the where current clause.

Check the below example:

I have one table "EMP_DETAILS"with below data and in this table have duplicate data .

Ename	Sal
Smith	52000
Jones	50000
Smith	70000
King	60000

Now we are updating SAL with 1000 for each employee using below PL/SQL code.

DECLARE
CURSOR EMPLOY_CUR IS SELECT * FROM EMP_DETAILS;
BEGIN
FOR INDEX1 IN EMPLOY_CUR
LOOP
UPDATE EMP_DETAILS
SET SAL=SAL+1000
WHERE ENAME=INDEX1.ENAME;
END LOOP;
COMMIT;

END;

 /

But in this code have some problem with duplicate record . Now try to understand the problem in update.Cursor in 1st record and Ename="Smith" and it will satisfy two record now SAL became
SAL=52000+1000 =53000
SAL=70000+1000 =71000

Cursor in 2nd record Ename="Jones"
SAL=52000+1000 =53000

Cursor in 3rd record Ename="Smith" again  it will satisfy two record now SAL became
SAL=53000+1000=54000
SAL=71000+1000=72000

Now my cursor in 4th record Ename="King"
SAL=60000+1000=61000

Check the out put which is not correct :

Ename	Sal
Smith	54000
Jones	51000
Smith	72000
King	61000

Now we will write a code by using WHERE CURRENT OF clause

DECLARE
CURSOR EMPLOY_CUR IS SELECT * FROM EMP_DETAILS FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR INDEX1 IN EMPLOY_CUR
LOOP
UPDATE EMP_DETAILS
SET SAL=SAL+1000
WHERE CURRENT OF EMPLOY_CUR;
END LOOP;
COMMIT;

END;

 /

Check the out put which is correct :

Ename	Sal
Smith	53000
Jones	51000
Smith	71000
King	61000