Scribd
Upload
22 views

PR - 2 Simulate Query Optimization

Uploaded by

Ojas Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
22 views

PR - 2 Simulate Query Optimization

Uploaded by

Ojas Patil
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Practical No: 02 Stimulate Query optimization by Applying an SQL Query

on any Database.

SQL> create table product1(recid varchar(10),pid varchar(10),pname


varchar(10),orderid varchar(10),cost int):
Table created.
SQL> declare
begin
for j in 1..40000
loop
insert into product1(recid,pid,pname,orderid, cost) values
(j, 'P'||j, 'mobile','o'||j*3,j*15/4);
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL>select * from product;
List all 40000 records here

SQL> explain plan for select * from product1;


Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 427209646
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 63 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PRODUCT | 1 | 41 | 63 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
- dynamic sampling used for this statement
12 rows selected.

SQL> create unique index ind2 on product1(recid);


Index created.

SQL> select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from


USER_INDEXES
2 where table_name='PRODUCT';

INDEX_NAME TABLE_OWNER TABLE_NAME UNIQUENES


------------------------------ ------------------------------ ------------------------------ ---------
IND2 SYSTEM PRODUCT NONUNIQUE

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2374733175
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 41 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 1 | 41 | 1 (0)|


00:00:01 |

|* 2 | INDEX UNIQUE SCAN | IND2 | 1| | 1 (0)| 00:00:01 |


--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("RECID"='1000')
14 rows selected.

You might also like