chap5_1.html

Oracle资料大集合

<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.networkservices1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Network Services</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.middleware.html"><font face="verdana,helvetica" size="1" color="000000"><b>Middleware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</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.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</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.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">5.1	How do I匘etermine all of the indexes on a particular table?</font></b>

<p>

<B>Problem</B>

<p>

The most commonly used method to improve data queries is the creation of indexes. Many times, it is important to know how a table has been indexed. How do I determine all of the indexes on a particular table?
<p>
<B>Technique</B>
<p>
The DBA_INDEXES data dictionary view contains information on each index within the database. By querying this view, you can determine which indexes exist on which tables. In this How-To, a sample table and indexes will be created. Then, the process of determining the indexes on the table will be shown.
<P>
<B>Steps</B>
<p>
<B>1.</B>	For regular indexes to exist, you must first create a table. Connect to SQL*Plus as the WAITE account and run CHP5_1.SQL, which will create a sample table used throughout this How-To.
<p>
<code><b>
SQL> GET CHP5_1.sql
<p>
1  CREATE TABLE EMPLOYEE05
<p>
2  (EMPLOYEE_NAME   VARCHAR2(30) NOT NULL,
<p>
3   SSN             NUMBER,
<p>
4   SALARY          NUMBER,
<p>
5   STATE           VARCHAR2(2),
<p>
6   JOB_DESCRIPTION VARCHAR2(100),
<p>
7   DATE_EMPLOYED   DATE)
<p>
8   TABLESPACE USER_DATA PCTFREE 10, PCTUSED 90
<p>
9   STORAGE ( INITIAL 1M NEXT 500K PCTINCREASE 50
<p>
10             MINEXTENTS 1 MAXEXTENTS 100);
<p>
SQL> /
<p>
Table Created.</b></code>
<p>
<B>2.</B>	Now that you have created the EMPLOYEE05 table, you are ready to create some sample indexes. A discussion of sizing indexes is discussed in How-To 5.3. CHP5_2.SQL contains a CREATE INDEX command that will create an index on the SSN column of the EMPLOYEE05 table. The SSN column was chosen for an index because employees are selected by their SSN in this example, and an index will speed up such queries. Run CHP5_2.SQL to create the SSN_INDEX index.
<p>
<code><b>SQL> GET CHP5_2
<p>
1  CREATE INDEX SSN_INDEX
<p>
2  ON EMPLOYEE05 (SSN)
<p>
3  TABLESPACE USER_DATA
<p>
SQL> /
<p>
Index Created.</b></code>
<p>
<B>3.</B>	At this point, the EMPLOYEE05 table has been created with an index on the SSN column. CHP5_3.SQL, shown in <A HREF="image/05oht01.gif" TARGET="_blank">Figure 5.1</A>, contains a script that will list all indexes on a table.
<p>
<B>4.</B>	When the script is run, the user will be prompted for an owner and table name. Enter WAITE for the owner, and EMPLOYEE05 for the table. This is shown in <A HREF="image/05oht02.gif" TARGET="_blank">Figure 5.2</A>. The script can be run for any owner and any table, making it a simple, yet valuable, tool for a developer or database administrator.
<p>
<B>How It Works</B>
<p>
The DBA_INDEXES data dictionary view contains information on each index in the database. Step 1 runs the CHP5_1.SQL to create the EMPLOYEE05 sample table. Step 2 runs the CHP5_2.SQL to create an index on the EMPLOYEE05 table. Step 3 queries the DBA_INDEXES data dictionary view for the WAITE owner and the EMPLOYEE05 table. The result is a list of all indexes on the EMPLOYEE05 table.
<p>
<B>Comments</B>
<p>
Indexes may improve the performance of SELECT statements. They will slow down inserts, updates, and deletes, as any of these manipulations on the table will also be performed on the index. Five thousand record inserts on a table, for instance, will also result in 5,000 index inserts, slowing performance. Be sure to understand what the index will be used for, and determine if the trade-off is worthwhile.
<p>
The impact of an index on a query will depend on the cardinality of the columns that the index is comprised of. For instance, a column that only contains 揟rue