This content has been marked as final. Show 3 replies
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:
It's easiest to consider extreme cases for thought experiments:
Can anyone give me an example where an index wouldThe 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.
be the best option to query 99% of data.
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 theConsider 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.
best option to query 1% of data.
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.