Re: Slow Full Table Scan on IOT

Just got out of ILT Tuning class, and Oracle says not to use IOT for any operations involving a FTS or a range scan for the same reasons everyone has just mentioned,

• Dc.

decompton_at_worldnet.att.net wrote:

> Joerg,
>
> An IOT is basically a B-TREE structure and should be much slower than a
> table scan for the following reasons. One, the index has the overhead
> blocks for the B-Tree that is must scan. Two, you are not likely
> performing the multiblock reads on an IOT that you get on a 'normal'
> tablescan.
>
> Try building an index on the 'normal' table and run a query that hints
> a full index scan. I would bet it to be in the 200 second ballpark.
>
> IOTs specifically designed for queries that always will use the index.
> If that is not the case, you need to go back to the normal table
> structure.
>
> thanks,
>
> dave
>
> In article <378D9A92.D59177BD_at_in.tum.de>,
> Joerg Lanzinger <lanzinge_at_in.tum.de> wrote:
> > Hello,
> >
> > for performance testing I build two tables containing about 1M rows of
> > generated data. One table as an IOT, the other with no special
> > properties.
> > It showed up that performing a full table scan on the IOT takes about
> > 200 seconds, while the FTS on the 'normal' table finished after about
> 10
> > seconds.
> > (I'm using Oracle 8.0.3 on a Sun Enterprise, Solaris 2.6)
> > Any suggestions to explain this phenomena?
> >
> > Thanks
> > Joerg
> >
> > --
> > ___ __
> > \_ \ \ \ Joerg Lanzinger, Tankenrainer Str. 33, 82362 Weilheim
> > _\ \ \ \__ EMail: joerg.lanzinger_at_in.tum.de
> > \___\ \___\ WWW: http://www.in.tum.de/~lanzinge
> >
> > -=* REALITY.SYS maybe corrupt - Reboot universe (Y/N) ? *=-
> >
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Sun Jul 18 1999 - 00:25:33 CDT