Home » RDBMS Server » Performance Tuning » explain plan
explain plan [message #64665] Thu, 18 December 2003 20:09 Go to next message
Vinod Nair
Messages: 6
Registered: December 2003
Junior Member
1. I have login as scott, I wrote the following query
select deptno from dept;

I got following
Execution Plan
------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=8)
1 0 INDEX (FULL SCAN) OF 'SYS_C005630' (UNIQUE) (Cost=1 Card=4
Bytes=8)

After that I disabled the primary key constraint deptno

ALTER TABLE DEPT
DISABLE CONSTRAINT SYS_C005630;

When I again fire the same query
select deptno from dept;
The output of execution plan was TABLE ACCESS(FULL)
Up to this it is correct The real problem starts now
when I again enable the primary key constriant on deptno

alter table dept enable constraint sys_c005630;

select deptno from dept;

the execution plan was

Execution Plan
------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=8)
1 0 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=8)

So why it is now TABLE ACCESS(FULL) instead of
INDEX(FULL SCAN) although I have enabled the primary
key (deptno).
Re: explain plan [message #64671 is a reply to message #64665] Fri, 19 December 2003 02:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
u need to have a where clause to utilize the index.
try
select * from dept where deptno=20;

Re: explain plan [message #64672 is a reply to message #64665] Fri, 19 December 2003 04:28 Go to previous messageGo to next message
Laly
Messages: 2
Registered: December 2003
Junior Member
I think it is because your second query is still in the library cache since it has not been invalidated.

If you want to be sure, try alter system flush shared_pool and see the new plan.

Laly.
Re: explain plan [message #64673 is a reply to message #64665] Fri, 19 December 2003 05:10 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
the reason is that when you disable the constraint,the index gets dropped. When you enable the constraint,the index gets recreated,but the CBO doesnt know that. You will need to Analyze the table to update the statistics so that the optimizer can go for the INDEX Full scan.

See here :

SQL> create table t(x int primary key);

Table created.

-- We have the unique index now

SQL> select count(*) from user_indexes where table_name='T';

COUNT(*)
----------
1

SQL> alter table t disable primary key;

Table altered.

-- the index gets dropped when you disable the constraint PK

SQL> select count(*) from user_indexes where table_name='T';

COUNT(*)
----------
0

SQL> alter table t enable primary key;

Table altered.

-- the unique index gets recreated when you enable the PK

SQL> select count(*) from user_indexes where table_name='T';

COUNT(*)
----------
1

SQL> set autotrace on explain
SQL> select x from t;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'

-- As seen above, the optimizer goes for FTS becos it doesnt know of the index yet

-- Lets update the statistics

SQL> analyze table t estimate statistics;

Table analyzed.

SQL> select x from t;

no rows selected

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
1 0 INDEX (FULL SCAN) OF 'SYS_C0021874' (UNIQUE)

-- Now the CBO knows the index and goes for the right explain plan (ie Index full scan)

HTH
Thiru
Re: explain plan [message #64674 is a reply to message #64671] Fri, 19 December 2003 05:14 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Mahesh,
you dont need a where clause to utilise the index. Index fast FULL SCANS will be used when all the data comes from the index . Pls see my demo in the same thread.

-Thiru
Re: explain plan [message #64676 is a reply to message #64674] Fri, 19 December 2003 06:41 Go to previous message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Agreed~
Previous Topic: Improve the performance of an Oracle Query
Next Topic: Performance Tuning
Goto Forum:
  


Current Time: Thu Sep 24 17:18:49 CDT 2020