advertisements
_____________________________________________________________________________________________________________________
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment