--execute UTLXPLAN.sql & SampleDB.sql

select c.clno, c.name
from client c, purch p
where c.clno = p.clno and p.qty>1000;

--the following will find the better query plan

explain plan for
select c.clno, c.name
from client c, purch p
where c.clno = p.clno and p.qty>1000;

--to see the query plan

select * from PLAN_TABLE;

--to format the output use the utlxpls_mod.sql file

--to see the values

alter session set optimizer_mode=all_rows;
alter session set "_optimizer_cost_model" = cpu;

--after altering again explaing the plan & select * from PLAN_TABLE, then run the utlxpls_mod.sql, then your cost values should be there


--getting another query plan

explain plan for
select t.*
from trading t
where t.exchange='Tokyo';


--PLAN_TABLE

Plan Table
--------------------------------------------------------------------------------
| Operation and options | Object | cost | cpu_cost | io_cost |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 4 |125588 | 3 |
| TABLE ACCESS FULL |TRADING | 4 |125588 | 3 |
--------------------------------------------------------------------------------

TABLE ACCESS FULL - since there is no index in the table, when you create index the cost will be reduced.

--creating index for the trading table

create index index_3 on trading(exchange);

--after creating the index the cost reduces

Plan Table
--------------------------------------------------------------------------------
| Operation and options | Object | cost | cpu_cost | io_cost |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 3 |21735 | 2 |
| TABLE ACCESS BY INDEX ROWID |TRADING | 3 |21735 | 2 |
| INDEX RANGE SCAN |INDEX_3 | 2 |14443 | 1 |
--------------------------------------------------------------------------------


--to drop an index

drop index [index_name];

--all the indexes will be stored inside the user_indexes table

select index_3
from user_indexes
where table_name='TRADING'
/

--if you used simple letters for trading when creating the table, you have to use capital letters while selecting the index [TRADING]



select index_name
from user_indexes
where table_name='TRADING'
/

INDEX_NAME
------------------------------
INDEX_3
SYS_C0023391

--SYS_C0023391 is the system generated index, and is created for primary keys.
--INDEX_3 is the index we created



select DBMS_METADATA.GET_DDL('INDEX', u.index_name)
from user_indexes u
where table_name = 'TRADING'
/


DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
--------------------------------------------------------------------------------

CREATE INDEX "DIT7C1_0055"."INDEX_3" ON "DIT7C1_0055"."TRADING" ("EXCHANGE")


CREATE UNIQUE INDEX "DIT7C1_0055"."SYS_C0023391" ON "DIT7C1_0055"."TRADING" (





--creating index for client table

create index index_3 on client


DBMS 7 answer provided by Russel Dharmarathne!..

Labels:

0 Responses so far.