| explain plan [message #64665] |
Thu, 18 December 2003 20:09  |
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 #64672 is a reply to message #64665] |
Fri, 19 December 2003 04:28  |
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 |
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 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
