Skip to Main Content
  • Questions

  • Index used sometimes and sometimes NOT?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Raghu.

Asked: December 18, 2002 - 8:52 pm UTC

Last updated: May 16, 2004 - 10:23 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,
I have an DW table with a b-tree index on a column called fin_comp_id
Now when I issue a 'DELETE FROM MY_TABLE WHERE FIN_COMP_ID = 'x' ' then the explain plan shows that the index is being used rather unpredictably - sometimes yes and at time, it does a FTS.
Why?
Raghu

17:48:49 FDSS_TEST_SQL>>delete from fdss_gl_daily where fin_comp_id = 'xyz' ;

0 rows deleted.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=8 Card=564 Bytes=733
2)

1 0 DELETE OF 'FDSS_GL_DAILY'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FDSS_GL_DAILY' (Cost=8
Card=564 Bytes=7332)

3 2 INDEX (RANGE SCAN) OF 'FDSS_GL_DAILY_IDX1' (NON-UNIQUE
) (Cost=3 Card=564)


17:49:08 FDSS_TEST_SQL>>delete from fdss_gl_daily where fin_comp_id = '311' ;

130917 rows deleted.

Elapsed: 00:00:12.06

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=960 Card=102488 Byte
s=1332344)

1 0 DELETE OF 'FDSS_GL_DAILY'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FDSS_GL_DAILY' (Cost=9
60 Card=102488 Bytes=1332344)

3 2 INDEX (RANGE SCAN) OF 'FDSS_GL_DAILY_IDX1' (NON-UNIQUE
) (Cost=109 Card=102488)




17:49:34 FDSS_TEST_SQL>>delete from fdss_gl_daily where fin_comp_id = '476' ;

220750 rows deleted.

Elapsed: 00:00:34.48

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE (Cost=1014 Card=253404 Byt
es=3294252)

1 0 DELETE OF 'FDSS_GL_DAILY'
2 1 TABLE ACCESS (FULL) OF 'FDSS_GL_DAILY' (Cost=1014 Card=2
53404 Bytes=3294252)


The table has 1.2M recs and the fin_comp_ids of 311 and 476 are the ones with the most records. Is there a threshold of the % of rows that a DML will affect when the index is jettisoned and a FTP done?





and Tom said...

Yes, the optimizer is doing EXACTLY what is it supposed to be doing.

You have histograms on this table.

It understands the distribution of data.

Your data is skewed -- it will use an index when an index is appropriate, it'll use a full table scan when that is more appropriate.


It is working perfectly.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, December 19, 2002 - 10:56 am UTC

Hi Tom

I heard that histogram is obsolite after oracle 8i.

Is it true?

Thanks,

Tom Kyte
December 19, 2002 - 11:28 am UTC

patently FALSE.

Only the explain plan

Raghu, December 19, 2002 - 1:00 pm UTC

Thanks Tom. That confirms what I guessed at. Also, when I do a "set autotr trace explain" and issue a statement, should it not just give me the explain plan without executing the sql itself? In my case it went ahead and deleted the rows (which took more time). What's the exact command for just getting the explain plan?

Tom Kyte
December 19, 2002 - 1:09 pm UTC

set autotrace traceonly explain

will just explain a SELECT, since actually executing the select won't have any effect (you see no rows, you see no stats).

but for DELETE (or insert/update/merge) it runs the command because NOT executing it does have a side effect.

To just see the plan:

scott@ORA920> delete from plan_table;

0 rows deleted.

scott@ORA920> explain plan for delete from emp;

Explained.

scott@ORA920> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | |
| 1 | DELETE | EMP | | | |
| 2 | TABLE ACCESS FULL | EMP | | | |
--------------------------------------------------------------------

Note: rule based optimization

10 rows selected.

scott@ORA920> delete from plan_table;

3 rows deleted.


Confusing Explain plan

Arul, May 14, 2004 - 4:45 am UTC 

1. In below example why there is no table access clause

SQL> delete from emp where empno=12;

0 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

2. I did following tests across version which are more intersting. Why only explain plan for Test 1 is similar and why it differs in Test 2 and 3.

Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
----------------------------------------------------------

1. SQL> delete from emp where empno=12;
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
SQL> 

SQL> create index index1 on emp(ename);
Index created.

SQL> create index index2 on emp(sal);
Index created.

2. SQL> delete from emp where ename='ADAM';
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)



3. SQL> delete from emp where sal=12;
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       INDEX (RANGE SCAN) OF 'INDEX2' (NON-UNIQUE)
SQL> 



Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
----------------------------------------------------------

1. SQL>  delete from emp where empno=12;
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

SQL> 
SQL> create index index1 on emp(ename);
Index created.

SQL> create index index2 on emp(sal);
Index created.

2. SQL> delete from emp where ename='ADAM';
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'INDEX1' (NON-UNIQUE)


3. SQL> delete from emp where sal=12;
0 rows deleted.

Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE
   1    0   DELETE OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'INDEX2' (NON-UNIQUE)

Thanx in Advance.

Tom Kyte
May 14, 2004 - 10:23 am UTC

i would ignore this, delete of 'emp' is a table access. just a nuance of explain plan, nothing to worry about.

Why its not similar within the version 8174 ?

Arul, May 15, 2004 - 10:53 pm UTC 

Hi,

I am still confused about the variation in explain plan. I agree for the 3 similar plans in 9205. But why in 8174 for last 2 deletes the plans is diffrent from first delete.

Why "TABLE ACCESS (BY INDEX ROWID) OF 'EMP'" access path is not shown in "SQL> delete from emp where empno=12;" for 8174 where as the other deletes in 8174 has this access path. Any technical explanation for the variations within the same version. ?

     "TABLE ACCESS (BY INDEX ROWID) OF 'EMP'"

Tom Kyte
May 16, 2004 - 10:23 am UTC

don't worry about it. just a nuance, the delete obviously accesses the table.

if you want to dig into it and find the code that is causing it, you can work it via support. I don't really dig into the source code and as it has no material affect on *anything*....

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.