chap9_3.html

来自「Oracle资料大集合」· HTML 代码 · 共 394 行 · 第 1/2 页

<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>

<b><font size="+1"><B>9.3	How do I匔orrect row migration in tables?</B></font></b>
<p>
<B>Problem</B>
<p>
I have used the techniques in How-To 9.2 to analyze some database tables and have discovered that some of the tables in my database do indeed contain chained or migrated rows. How can I correct this state so that each table row resides entirely in a single block?
<p>
<B>Technique</B>
<p>
Using the sample tables from How-To 9.2, including the CHAINED_ROWS table, move the table rows in question to a temporary table, delete them from the original table, and copy them back to the original table from the temporary table.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the CHP9_3.SQL script and the CHP9_4.SQL script. See the previous How-To for an in-depth explanation of the SQL statements in these scripts.
<p>
<B>2.</B>	While still in SQL*Plus, issue these two START commands to execute the CHP9_5 script for the CHAIN09 and MIGRATE09 tables: start chp9_5 migrate09 and start chp9_5 chain09. This CHP9_5 script appears in Listing 9.2.
<p>
Listing 9.2 The CHP9_5 script attempts to correct row migration
<p>
<code><b>1  DROP TABLE temp;
<p>
2  --
<p>
3  CREATE TABLE temp AS
<p>
4   SELECT * FROM &1<BR>
<p>
5   WHERE rowid IN
<p>
6    (SELECT head_rowid FROM chained_rows
<p>
7     WHERE table_name = upper('&1'));
<p>
8  --
<p>
9  DELETE FROM &1<BR>
<p>
10  WHERE rowid IN
<p>
11 (SELECT head_rowid FROM chained_rows
<P>
12  WHERE table_name = upper('&1'));
<p>
13  --
 <p>
14  INSERT INTO &1
 <p>
15* SELECT * FROM temp;</b></code>
<p>
The script makes multiple use of a single substitution variable that contains the name of the table containing the migrated rows. You抣l call this the target table for the remainder of this discussion. The SQL function UPPER appears throughout the script to convert the target table name to uppercase. This is necessary if the user invokes this script with the table name in lowercase because the CHAINED_ROWS table stores the table name in uppercase.
<p>
Line 1 drops the table TEMP created by previous invocations of this script. Lines 3 through 8 recreate the TEMP table and populate it with any rows from the target table whose ROWIDs appear in the CHAINED_ROWS table. Lines 9 through 12 delete these same rows from the target table. Lines 14 and 15, finally, insert the problematic rows back into the target table.
<p>
<B>3.</B>	While still in SQL*Plus, use the START command to load and execute the script CHP9_4.SQL. This script analyzes the CHAIN09 and MIGRATE09 tables again to help determine what effect, if any, the preceding steps had on these tables.
<p>
<B>How It Works</B>
<p>
Step 1 recreates the CHAIN09 and MIGRATE09 tables from the previous How-To. Step 2 attempts to correct row migration by essentially giving the Oracle8 server another chance to insert the offending rows, identified in the CHAINED_ROWS table, back into the target table. Step 3 analyzes the two sample tables after the curative measures applied in step 2. The results of the re-analysis of the two sample tables appear in <A HREF="image/09oht07.gif" TARGET="_blank">Figure 9.7</A>.
<p>
Notice that the CHAIN09 table is still listed in the CHAINED_ROWS table. Why? The answer is that even after the steps in this How-To gave Oracle8 another opportunity to insert this problematic row from the CHAIN09 table, the row still occupies more than one data block. Oracle8 did not migrate this row because of an update statement like the row from the MIGRATE09 table. Oracle8 chained this row because it contains 4000 bytes of data and the database block size is only 2048 bytes. The row is too large to fit in any database block; the DBA can move it to another table and re-insert it in the target table until the Oracle9 release date, and the row will still be chained.
<p>
<B>Comments</B>
<p>
Row migration can contribute to degraded database performance, but it is often resolvable using the preceding techniques. If it is not feasible to create a table to temporarily store the migrated rows, as in this How-To, then the export and import utilities can help resolve row migration problems. After the DBA resolves migrated row difficulties with one of these techniques, an increase in the PCTFREE parameter can decrease migration frequency in the future. (Recall that the value of this parameter is equal to the amount of database block space, expressed as a percent, that Oracle8 attempts to keep free for future updates.)
<p>
Row chaining is more problematic because the only way to address it is usually to rebuild the database with a larger database block size. This intricate process requires a full database export, database recreation, and database import. Moreover, if the database抯 primary function is online transaction processing, a larger block size can impede database operation. This performance degradation could more than offset any gains arising from elimination of row chaining. In general, this discussion emphasizes the need for benchmarking in a test environment to identify and assess unintended side effects of database parameter modifications.
<p>


<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>