This discussion is archived
3 Replies Latest reply: May 5, 2008 6:52 PM by 637274 RSS

Full Table Scans / Index Scans /Index Organized Tables...

585321 Newbie
Currently Being Moderated
Can anyone give me an example where an index would be the best option to query 99% of data.

Can anyone give me an example where a FTS is the best option to query 1% of data.

If IOT are better in terms of performance and also in terms of saving storage space then why cant we always use IOT in the database . Why do we need to follow the regular table and index to store data.

Responses are appreciated !

null

null
  • 1. Re: Full Table Scans / Index Scans /Index Organized Tables...
    dbtoo Explorer
    Currently Being Moderated
    I'm not savy enough on the first two examples, but I will comment on the IOT.


    Concepts Doc is fairly useful. In particular Table 5-3 where it notes that the IOT primary key MUST be specified [in the predicate of a select statement]. Performance can tank when you don't use that leading column in the predicate!

    We had a vendor application which implemented IOTs for performance of certain screens. The in-house developers didn't understand the concept of IOT and used them like they were 'normal' tables. The in-house queries had terrible performance, until they were told about the need to have that primary key in the predicate. So, if you allow user access to those IOT's nothing can quarentee they won't abuse the IOT and query without the primary key.

    Message was edited by:
    dbtoo
  • 2. Re: Full Table Scans / Index Scans /Index Organized Tables...
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    It's easiest to consider extreme cases for thought experiments:
    Can anyone give me an example where an index would
    be the best option to query 99% of data.
    The obvious case would be where you had an order by clause with a matching index. With a well-clustered table, the optimizer might decide to do an index full scan to collect 100% of the data in order rather then doing a tablescan and sort.

    In fact, with first_rows_1 optimization, and certainly with first_rows optimization before it, you would sometimes see the optimizer use such a strategy to read 100% of the data in the right order, discarding 99% of it, rather than selecting 1% of the data through a more precise index and sorting it.

    Can anyone give me an example where a FTS is the
    best option to query 1% of data.
    Consider a table in a tablespace with an 8Kb block size - with short rows (roughly 80 bytes) you can get 100 rows per block. Run a query that wants to get one row from each block (i.e. 1% of the data). A tablescan with a db_file_multiblock_read_count of just 2 would be more efficient than an indexed access. For a more realistic example consider picking one row from every 16th block when your db_file_multiblock_read_count is 128.

    In fact, if you check what Oracle does when you sample 1% of the rows using the sample clause, you may see cases where it actualy uses multiblock reads to scan the whole table, discarding the blocks it doesn't want.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 3. Re: Full Table Scans / Index Scans /Index Organized Tables...
    637274 Newbie
    Currently Being Moderated
    http://www.orafaq.com/node/39