Re: Slow Full Table Scan on IOT

From: <decompton_at_worldnet.att.net>
Date: Thu, 15 Jul 1999 19:10:15 GMT
Message-ID: <7mlbmc$olt$1@nnrp1.deja.com>

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 Thu Jul 15 1999 - 14:10:15 CDT