RE: online index rebuilds - FFS or FTS

 No, not at all.  "INDEX FULL SCAN" reads the index, starting at the root, one 
block at a time, and walks the tree, in index sorted order.  "INDEX FAST FULL 
SCAN" is the equivalent of a FULL TABLE SCAN, but for an index.  It reads using 
multiblock reads, results are NOT returned sorted, and the reading process 
simply "throws away" the content of the root and branch blocks.  


--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

"There are 10 types of people in the world:  Those who understand binary, and 
those who don't."

-----Original Message-----
From: Roger Xu [mailto:roger_xu@xxxxxxxxxxx] 
Sent: Monday, January 30, 2006 4:20 PM
To: Jonathan Lewis; tanel.poder.003@xxxxxxx; joseph@xxxxxxxxxxxxxx; Bobak, 
Mark; ORACLE-L
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@xxxxxxxxxxxxxxxxxx]
Sent: Monday, January 30, 2006 2:06 PM
To: tanel.poder.003@xxxxxxx; Roger Xu; joseph@xxxxxxxxxxxxxx; Bobak, Mark; 
ORACLE-L
Subject: Re: online index rebuilds




You can actually get an index full scan
(rather than fast full scan) for an offline index rebuild - and I thnk I 
decided the choice was cost-based even back in 8i.

Regards

Jonathan Lewis

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

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 10th Jan 2006

----- Original Message -----
From: "Tanel Põder" <tanel.poder.003@xxxxxxx>
To: <roger_xu@xxxxxxxxxxx>; <joseph@xxxxxxxxxxxxxx>; "Bobak, Mark" 
<Mark.Bobak@xxxxxxxxxxxxxxx>; "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, January 30, 2006 7:58 PM
Subject: Re: online index rebuilds


Hi,

Thinking about why full table scan is required for online index rebuild... 
Maybe 
the potential index block splits are the reason why index FFS can't be used for 
online index rebuild? Consistent reads should overcome this issue though, but 
maybe there is something we don't know...

Btw, with offline index rebuild the decision whether to go with FTS or index 
FFS 
is also cost based (at least 10.2). If I have only single-column table indexed 
then the offline rebuild may do an FTS instead of fasf full index scan. (also, 
even in the case of fast full index scan, rows have to be sorted anyway, 
because 
index fast full scan doesn't guarantee an ordered result).

Tanel.



For technical support please email tech_support@xxxxxxxxxxx or you can
call (972)721-8257. 
This email has been scanned for all viruses by the MessageLabs Email Security 
System.

This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential and/or privileged information. Any 
review, dissemination, copying, printing or other use of this e-mail by persons 
or entities other than the addressee is prohibited. If you have received this 
e-mail in error, please contact the sender immediately and delete the material. 
____________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email Security 
System. Any questions please call 972-721-8257 or email your request to 
tech_support@xxxxxxxxxxxx

BEGIN:VCARD
VERSION:2.1
N:Bobak;Mark
FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
TEL;WORK;VOICE:734.975.6086
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway
LABEL;WORK;ENCODING=QUOTED-PRINTABLE:ECC4-N01E04=0D=0A1400 Eisenhower Parkway
EMAIL;PREF;INTERNET:Mark.Bobak@xxxxxxxxxxxxxxx
REV:20051213T223654Z
END:VCARD

Other related posts: