Re: why does oracle not use the index pk_emp

<zhangguoping_at_boco.com.cn> wrote in message news:b5trgb$1rs7$1_at_mail.cn99.com...
> i exec following sql in scott/tiger:
>
> "explain plan for select empno from emp"
>
> but it shows
> --------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost |
> --------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 82 | 1066 | 2 |
> | 1 | TABLE ACCESS FULL | EMP | 82 | 1066 | 2 |
> --------------------------------------------------------------------
> Note: cpu costing is off
>
> why doesn't it perform full index scan since pk_emp contain the empno
> needed!
> by the way, what 's meaning of "Note: cpu costing is off"?????
>
> i am a freshman, thks!

You have 82 rows. That appear to be slightly over 1K in total. That means that, almost certainly, the entire EMP table resides in a single Oracle block, and can be read with one I/O. The index on such a table would have at least two blocks... one root node, and one leaf node. Indexes are only ever read one block at a time, so that would require two physical I/Os to process.

Hence the table is preferred to the index. As it will be, incidentally, any time you ask for all the rows in a table, without an order by clause. And even if you put an order by clause in, your table would have to be pretty humungous before Oracle decides that sorting it fresh in memory is less attractive a proposition than reading it, one block at a time, out of an index.

Regards
HJR Received on Thu Mar 27 2003 - 00:41:45 CST