Re: Anyone know any WP which explains about FTS vs Index Scan?

Two things:

1. Index scans can actually cause much more IO than the full table scans, because index scans actually require access to two segments, not just to the table segment.
2. It is possible to have full table scan done in a smart way, in parallel and with "smart storage" which will discard the blocks which do not contain the requested information by using something called "Bloom filters". You can have up to 18 those smart storage servers in one rack. It is also possible to combine that with the hybrid columnar compression, which further reduces the need for IO. Under those circumstances, a full table scan can actually be much faster than an index scan.

An additional design principle says that you should always separate an OLTP database and reporting or DW databases. OLTP databases usually execute huge amounts of small transactions for which index range scan sometimes may be the best option. On the other hand, reporting databases, data marts and data warehouses usually utilize full table scans and "snowflake queries" and can immensely benefit from the columnar storage and optimized full scans like Exadata. Exadata is a DW machine. It's all about the optimization of the full table scan. However, there are other contenders that can perform as well as Exadata: Greenplum and Netezza on the high end and Vertica or SAP Hana on the low end. I invested a significant effort to familiarize myself with ever more popular SAP Hana and was hugely disappointed by the lack of support for recursive queries. Instead of SQL standard recursive queries supported by Oracle, DB2, MS SQL and PostgreSQL, SAP Hana has a proprietary "hierarchy" function, which means that you would have to replace proprietary "connect by" clause by equally proprietary and more cumbersome "hierarchy" function. However, I am not sure how many DW applications actually use hierarchical queries, so the practical implications of that lack of support remain to be seen.

On 04/15/2018 03:22 PM, Ls Cheng wrote:
> Hi
>
> He sticked with late 90's theory however he actually pointed me some
> oracle 11.2 documentation links which talks about reduce I/O, use
> index when possible, then I started searching some stuff from Oracle
> and I cannot find a lot which says index scan is not always the best
> (I found the explanation in asktom only). So I question if Oracle
> documentation is actually promoting index scans! :-)

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l