Re: index skip scan

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:dc662126-1bdb-4a31-92f0-2901dc2521fc_at_s9g2000prm.googlegroups.com... On Jan 6, 3:03 pm, "jerni..._at_nospam.kochind.com" <jerni..._at_kochind.com> wrote:
> I noticed this also
>
> prefetching is on for INV_TRANS_QTY_IND3

On Jan 6, 3:03 pm, "jerni..._at_nospam.kochind.com" <jerni..._at_kochind.com> wrote:
> I noticed this also
>
> prefetching is on for INV_TRANS_QTY_IND3

Prefetching simply allows for "db file parallel reads", and the _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT limits the number of blocks that can be fetched. It's a multi-block read, but the blocks can be randomly scattered around the file. Typically used for picking up multiple leaf blocks based on the contents of a branch block, and multiple table blocks based on the contents of the leaf block, when doing a large range scan. Most commonly seen when you do an index full scan.

There is an optimizer aspect and a run-time aspect to this, and the CKPT process is checking every few seconds to see if it makes sense to allow this type of prefetching

So you may see it mentioned in the trace file (and indicated in the execution plan) but not appearing at run time.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html