From: "Powell, Mark" <mark.powell2@hpe.com>
To: Jonathan Lewis <jonathan@jlcomp.demon.co.uk>, "oracle-l@freelists.org"
 <oracle-l@freelists.org>, "jprem@outlook.com" <jprem@outlook.com>
Subject: Re: Tuning "INSERT as SELECT"
Thread-Topic: Tuning "INSERT as SELECT"
Date: Thu, 2 Feb 2017 15:28:43 +0000
Message-ID: <DF4PR84MB020445DA024CBE602818E80ACC4C0@DF4PR84MB0204.NAMPRD84.PROD.OUTLOOK.COM>
References: <TY1PR01MB0538F2C3A70674B77DBBC319BC4C0@TY1PR01MB0538.jpnprd01.prod.outlook.com>,<MMXP123MB0911F7009F1BED10AA6BAB08A54C0@MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>,<TY1PR01MB05389100D912B48FB267515ABC4C0@TY1PR01MB0538.jpnprd01.prod.outlook.com>
In-Reply-To: <TY1PR01MB05389100D912B48FB267515ABC4C0@TY1PR01MB0538.jpnprd01.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
received-spf: None (protection.outlook.com: hpe.com does not designate
 permitted sender hosts)
spamdiagnosticoutput: 1:99
spamdiagnosticmetadata: NSPM
Content-Type: multipart/alternative;
 boundary="_000_DF4PR84MB020445DA024CBE602818E80ACC4C0DF4PR84MB0204NAMP_"
MIME-Version: 1.0
X-MS-Exchange-CrossTenant-originalarrivaltime: 02 Feb 2017 15:28:43.4664
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 105b2061-b669-4b31-92ac-24d304d195dc
X-MS-Exchange-Transport-CrossTenantHeadersStamped: DF4PR84MB0201
X-archive-position: 67556
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: mark.powell2@hpe.com
Precedence: normal
Reply-To: mark.powell2@hpe.com
List-help: <mailto:ecartis@freelists.org?Subject=help>
List-unsubscribe: <oracle-l-request@freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request@freelists.org?Subject=subscribe>
List-owner: <mailto:mark.bobak@proquest.com>
List-post: <mailto:oracle-l@freelists.org>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l
--_000_DF4PR84MB020445DA024CBE602818E80ACC4C0DF4PR84MB0204NAMP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Prem, "UNUSABLE" basically means one of the following 1- the index row entr=
ies no longer point to the correct location of the base table rows, the ind=
ex row entries point to non-existent rows, or there are table rows with no =
entry in the index when there should be an entry.  If you TRUNCATE the tabl=
e non of these conditions is true and on insert to the table the indexes wi=
ll be maintained so the indexes are USABLE, that is, the index entries are =
valid.

________________________________
From: oracle-l-bounce@freelists.org <oracle-l-bounce@freelists.org> on beha=
lf of Prem Khanna J <jprem@outlook.com>
Sent: Thursday, February 2, 2017 9:12:32 AM
To: Jonathan Lewis; oracle-l@freelists.org
Subject: Re: Tuning "INSERT as SELECT"


Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.

>>Did you really select and fetch 80 million rows ?  or was it the first fe=
w, or a count(*) of an inline view, or what ?

You are on spot . we did a "select count(*) from inline view" :-(    Also t=
ested with "+all_rows" hint in sql*developer thinking that might help , but=
 now I feel like
that's also not the right way of testing as sql*developer stops with first =
50~100 rows. Will let the exact SQL run all the way in sql*plus and see lon=
g it takes.Just curious - is
there any other way of doing it (don't want to see all the 80m recs in sql*=
plus) or how would you guys do it ?

>> ... your test starts with a truncate then it will have made the indexes =
valid again.

Exaclty. we made indexes "unusable" , truncated the table before I ran my t=
ests. Did not imagine that truncate would my indexes valid. Just checked an=
d it was so.
Shocked !! Wonder why truncate does so. Thanks a million Jonathan for letti=
ng me know that.

>> please turn on SQL tracing using DBMS_MONITOR or .... Everything else is=
 pure witchcraft.
Sure Mladen. Thanks again for the valuable piece of advice.

Will continue with my tests and keep you guys posted.

Regards,
Prem


--_000_DF4PR84MB020445DA024CBE602818E80ACC4C0DF4PR84MB0204NAMP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"><!-- P {margin-top:0;margi=
n-bottom:0;} --></style>
</head>
<body dir=3D"ltr">
<style type=3D"text/css" style=3D"display:none;"><!-- P {margin-top:0;margi=
n-bottom:0;} --></style>
<div id=3D"divtagdefaultwrapper" style=3D"font-size:12pt;color:#000000;font=
-family:Calibri,Arial,Helvetica,sans-serif;" dir=3D"ltr">
<p>Prem, &quot;UNUSABLE&quot; basically means one of the following 1-&nbsp;=
the index row entries no longer point to the correct location of the base t=
able rows, the index row entries point to non-existent rows, or there are t=
able rows with no entry in the index when there
 should be an entry.&nbsp; If you TRUNCATE the table non of these condition=
s is true and on insert to the table the indexes will be maintained so the =
indexes are USABLE, that is, the index entries are valid.<br>
</p>
</div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> oracle-l-bounce@freel=
ists.org &lt;oracle-l-bounce@freelists.org&gt; on behalf of Prem Khanna J &=
lt;jprem@outlook.com&gt;<br>
<b>Sent:</b> Thursday, February 2, 2017 9:12:32 AM<br>
<b>To:</b> Jonathan Lewis; oracle-l@freelists.org<br>
<b>Subject:</b> Re: Tuning &quot;INSERT as SELECT&quot;</font>
<div>&nbsp;</div>
</div>
<div>
<div id=3D"divtagdefaultwrapper" style=3D"font-size:12pt;color:#000000;font=
-family:Calibri,Arial,Helvetica,sans-serif;" dir=3D"ltr">
<p></p>
<div>Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.</=
div>
<div><br>
</div>
<div>&gt;&gt;Did you really select and fetch 80 million rows ? &nbsp;or was=
 it the first few, or a count(*) of an inline view, or what ?</div>
<div><br>
</div>
<div>You are on spot . we did a &quot;select count(*) from inline view&quot=
; :-( &nbsp; &nbsp;<span style=3D"font-size: 12pt;">Also tested with &quot;=
&#43;all_rows&quot; hint in sql*developer thinking that might
</span>help ,<span style=3D"font-size: 12pt;"> but now I feel </span>like&n=
bsp;</div>
<div>that's also not the right way of testing as sql*developer stops with f=
irst 50~100 rows. Will&nbsp;let<span style=3D"font-size: 12pt;"> the exact =
SQL run all the way in sql*plus and see long it takes.Just curious - is&nbs=
p;</span></div>
<div><span style=3D"font-size: 12pt;">there any </span>other&nbsp;way<span =
style=3D"font-size: 12pt;"> of doing it (don't want to see all the 80m recs=
 in sql*plus) or how would you guys do
</span>it ?</div>
<div><br>
</div>
<div>&gt;&gt; ... your test starts with a truncate then it will have made t=
he indexes valid again.</div>
<div><br>
</div>
<div>Exaclty. we made indexes &quot;unusable&quot; , truncated the table be=
fore I ran my tests. Did not imagine&nbsp;that<span style=3D"font-size: 12p=
t;"> truncate would my indexes valid. Just checked and it was so.&nbsp;</sp=
an></div>
<div><span style=3D"font-size: 12pt;">Shocked !! Wonder </span>why<span sty=
le=3D"font-size: 12pt;"> truncate does so.&nbsp;</span><span style=3D"font-=
size: 12pt;">Thanks a million Jonathan for letting me know that.&nbsp;</spa=
n></div>
<div><br>
</div>
<div>&gt;&gt; please turn on SQL tracing using DBMS_MONITOR or .... Everyth=
ing else is pure witchcraft.</div>
<div>Sure Mladen. Thanks again for the valuable piece of advice.</div>
<div><br>
</div>
<div>Will continue with my tests and keep you guys posted.</div>
<div><br>
</div>
<div>Regards,</div>
<div>Prem</div>
<p></p>
<div style=3D"color: rgb(0, 0, 0);"><font size=3D"2"><span style=3D"font-si=
ze:10pt;">
<div class=3D"PlainText"><br>
</div>
</span></font></div>
</div>
</div>
</body>
</html>

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