Re: online index rebuilds - FFS or FTS

Nope,

Index full scan traverses the index tree structure, from root to branch to first (or last) leaf and follows pointers to next leaves block by block. A serial index full scan will guarantee that the rows are returned in key order from it, thus avoiding a later sorting step when using order by for example.

Fast full index scan reads the whole index segment with multiblock reads just as with full table scan, ignores branch blocks and returns whatever keys it finds from there, no ordering is guaranteed. Index FFS can be helpful when doing aggregate functions (sum, count, avg, etc) on indexed column, because index segment is generally smaller than table, thus can be scanned using fewer block gets/multiblock reads. However, when the indexed colum is not marked as "NOT NULL" some aggregate functions such unrestricted count(*) from table can't use it (as rows which have all indexed columns NULLs, aren't stored in the index).

Tanel.

• Original Message ----- From: "Roger Xu" <roger_xu_at_dp7uptx.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>;
  <tanel.poder.003_at_mail.ee>; <joseph_at_amalrajinc.com>; "Bobak, Mark"
  <Mark.Bobak_at_il.proquest.com>; "ORACLE-L" <oracle-l_at_freelists.org>
  Sent: Monday, January 30, 2006 3:20 PM Subject: RE: online index rebuilds - FFS or FTS

I thought "fast full index scan" was just another name for "index full scan"?

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk] Sent: Monday, January 30, 2006 2:06 PM
To: tanel.poder.003_at_mail.ee; Roger Xu; joseph_at_amalrajinc.com; Bobak, Mark; ORACLE-L
Subject: Re: online index rebuilds

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