online index rebuilds 2006-01-30 - By Tanel Poder
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.
-- -- Original Message -- --
From: Roger Xu
To: joseph@(protected) ; Bobak, Mark ; ORACLE-L
Cc: tanel.poder.003@(protected)
Sent: Wednesday, January 25, 2006 2:05 PM
Subject: online index rebuilds
Any other differences besides: 1) does not lock the table; 2) does a full
table scan instead of full index scan; ?
-- --Original Message-- --
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)
]On Behalf Of Joseph Amalraj
Sent: Tuesday, January 24, 2006 9:48 AM
To: Bobak, Mark; joseph@(protected); ORACLE-L
Subject: RE: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment
Thanks,
Had read the article, but I am interested as to why Oracle is doing a full
table scan instead of full index scan for online index rebuilds.
"Bobak, Mark" <Mark.Bobak@(protected)> wrote:
Well, my name is not Tanel, but I'll jump in here and point you to
MetaLink Doc ID 272762.1.
-Mark
--
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."
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)
] On Behalf Of Joseph Amalraj
Sent: Tuesday, January 24, 2006 9:59 AM
To: ORACLE-L
Subject: Re: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp segment
Thanks Tanel,
Is there any reason why Oracle does full table scans for online rebuilds
and not for offline.
Regards
Joseph Amalraj
Tanel Põder <tanel.poder.003@(protected)> wrote:
Online rebuilds don't use existing index for creating new. They do full
table scan on the table.
However, in both cases, old index segment is kept until new one is
ready to use and switch to it is done. Also the journal IOTs used for online
rebuilds are stored in the target tablespace, so if you've got lots of
transaction s going on while you are rebuilding the index onl ine, you need
additional space in target tablespace.
Tanel.
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@(protected)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR></HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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...</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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).</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Tanel.</FONT></DIV></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT:
#000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">-- -- Original Message -- -- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=roger_xu@(protected) href="mailto:roger_xu@(protected)">Roger Xu</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=joseph@(protected)
href="mailto:joseph@(protected)">joseph@(protected)</A> ; <A
title=Mark.Bobak@(protected)
href="mailto:Mark.Bobak@(protected)">Bobak, Mark</A> ; <A
title=oracle-l@(protected) href="mailto:oracle-l@(protected)">ORACLE-L</A
>
</DIV>
<DIV style="FONT: 10pt arial"><B>Cc:</B> <A title=tanel.poder.003@(protected)
href="mailto:tanel.poder.003@(protected)">tanel.poder.003@(protected)</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Wednesday, January 25, 2006 2:05
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> online index rebuilds</DIV>
<DIV><BR></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=468280220-25012006>Any
other differences besides: 1) does not lock the table; 2) does a full table
scan instead of full index scan; ?</SPAN></FONT></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-- --Original Message-- --<BR><B>From:</B> <A
href="mailto:oracle-l-bounce@(protected)">oracle-l-bounce@(protected)<
/A>
[mailto:oracle-l-bounce@(protected)]<B>On Behalf Of </B>Joseph
Amalraj<BR><B>Sent:</B> Tuesday, January 24, 2006 9:48 AM<BR><B>To:</B>
Bobak, Mark; <A
href="mailto:joseph@(protected)">joseph@(protected)</A>;
ORACLE-L<BR><B>Subject:</B> RE: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp
segment<BR><BR></FONT></DIV>
<DIV>Thanks, </DIV>
<DIV> </DIV>
<DIV>Had read the article, but I am interested as to <U>why</U> Oracle is
doing a full table scan instead of full index scan for online index
rebuilds.<BR><BR><B><I>"Bobak, Mark" <<A
href="mailto:Mark.Bobak@(protected)">Mark.Bobak@(protected)</A>>
</I></B>
wrote:</DIV>
<BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px solid">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<DIV dir=ltr align=left><SPAN class=640153815-24012006><FONT face=Arial
color=#0000ff size=2>Well, my name is not Tanel, but I'll jump in here
and
point you to MetaLink Doc ID 272762.1.</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=640153815-24012006><FONT face=Arial color=#0000ff
size=2>-Mark</FONT></SPAN></DIV><!-- Converted from text/rtf format -->
<DIV><B><SPAN lang=en-us><FONT face="Century Gothic"
size=2>--</FONT></SPAN></B> <BR><B><SPAN lang=en-us><FONT
face="Century Gothic" size=2>Mark J. Bobak</FONT></SPAN></B> <BR><B><SPAN
lang=en-us><FONT face="Century Gothic" size=2>Senior Oracle
Architect</FONT></SPAN></B> <BR><B><SPAN lang=en-us><FONT
face="Century Gothic" size=2>P</FONT><FONT face="Century Gothic"
size=2>ro</FONT><FONT face="Century Gothic" color=#ff0000
size=2>Q</FONT><FONT face="Century Gothic" size=2>uest Information &
Learning</FONT></SPAN></B> </DIV>
<DIV><SPAN lang=en-us><FONT face="Californian FB">"There are 10 types of
people in the world: Those who understand binary, and those who
don't."</FONT></SPAN> </DIV>
<DIV> </DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] <B>On Behalf Of </B>Joseph
Amalraj<BR><B>Sent:</B> Tuesday, January 24, 2006 9:59 AM<BR><B>To:</B>
ORACLE-L<BR><B>Subject:</B> Re: ORA-01652 (See ORA-01652.ora-code.com): unable to extend temp
segment<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Thanks Tanel, </DIV>
<DIV> </DIV>
<DIV>Is there any reason why Oracle does full table scans for online
rebuilds and not for offline.</DIV>
<DIV> </DIV>
<DIV>Regards</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV>Joseph Amalraj<BR><BR><B><I>Tanel Põder
<tanel.poder.003@(protected)></I></B> wrote:</DIV>
<BLOCKQUOTE class=replbq
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #1010ff 2px
solid">
<META content="MSHTML 6.00.2900.2802" name=GENERATOR>
<STYLE></STYLE>
<DIV><FONT face=Arial size=2>Online rebuilds don't use existing index
for creating new. They do full table scan on the table.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>However, in both cases, old index
segment is kept until new one is ready to use and switch to it is
done. Also the journal IOTs used for online rebuilds are stored in
the target tablespace, so if you've got lots of transaction s going on
while you are rebuilding the index onl ine, you need additional space
in
target tablespace.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial
size=2>Tanel.</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE><BR>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.
<BR>__ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ ____ _____<BR
>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@(protected)<BR></BLOCKQUOTE></BODY></HTML>
|
|
