Howard J. Rogers wrote:
>
> <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
although full scanning a 1 block table sometimes takes more than just
the 1 block for the table itself...
hth
connor
--
=========================
Connor McDonald
http://www.oracledba.co.uk
"Some days you're the pigeon, some days you're the statue"
Received on Thu Mar 27 2003 - 00:59:33 CST
