Oracle Delete and Update Commands Executes With Set Autotrace Traceonly Explain

When you set autotrace traceonly explain in SQLPlus prompt, generally the select command will not execute but it will show the execution plan. However, it will differ in case of DMLs like delete and update. It executes the query first and will provide the execution plan.
SQL >select * from tab;


TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
MV_DEPT                        TABLE
RUPD$_DEPT                     TABLE

SQL >select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        70 PRODUCTION     DALLAS

SQL >set autotrace traceonly explain
SQL >delete from dept;

4 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 711689319

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | DELETE STATEMENT |         |     4 |    12 |     1   (0)| 00:00:01 |
|   1 |  DELETE          | DEPT    |       |       |            |          |
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

SQL >rollback;

Rollback complete.

SQL >update dept set loc='USA';

4 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 921533340

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |     4 |    32 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | DEPT |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    32 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

SQL >select * from dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Update happened already with above command.
SQL >set autotrace off
SQL >/

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     USA
        20 RESEARCH       USA
        30 SALES          USA
        70 PRODUCTION     USA

SQL >rollback;

Rollback complete.

This actual deletion or updation can be override by using explain plan command. See below mentioned example.

SQL >explain plan for delete from dept;

Explained.

SQL >select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 711689319

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | DELETE STATEMENT |         |     4 |    12 |     1   (0)| 00:00:01 |
|   1 |  DELETE          | DEPT    |       |       |            |          |
|   2 |   INDEX FULL SCAN| PK_DEPT |     4 |    12 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------


9 rows selected.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google