ch05.htm

这个是sap开发语言abap的教育文档

<LI>Display the table.
<LI>Choose the menu path Utilities-&gt;Database Utility. The ABAP/4
Dictionary: Utility for Database Tables screen is displayed.
<LI>Choose the menu path Extras-&gt;Database Object-&gt;Display.
A list of all fields in the table is displayed, and beneath it
is a list of all indexes with their fields, including index 0.
</OL>
<H3><A NAME="ConsiderationsWhenCreatinganIndex">
Considerations When Creating an Index</A></H3>
<P>
<IMG SRC="../button/newterm.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/newterm.gif">
<P>
An index can consist of more than one field. For example, building
a secondary index on <TT>land1</TT> and <TT>regio</TT> enables
<TT>select * from ztxlfa1 where land1 = 'US' and regio = 'MA'</TT>
to quickly find matching rows. When multiple indexes exist (a
primary and one or more secondary indexes), the RDBMS uses an
<I>optimizer</I> to pick the best one. To choose an index, the
optimizer looks at the field names in the <TT>where</TT> clause
and then it looks for an index having the same field names in
the same order as they were specified in the <TT>where</TT> clause.
Therefore, to ensure the system chooses the index you intend,
<I>specify the fields in the </I><TT><I>where</I></TT><I>
clause in the same order as they appear in the index</I>.
<H4>Indexes and <TT>MANDT</TT></H4>
<P>
If a table begins with the <TT>mandt</TT> field, so should its
indexes. If a table begins with <TT>mandt</TT> and an index doesn't,
the optimizer might not use the index.
<P>
Remember, if you will, Open SQL's automatic client handling feature.
When <TT>select * from ztxlfa1 where land1 = 'US'</TT> is executed,
the actual SQL sent to the database is <TT>select * from ztxlfa1
where <I>mandt = sy-mandt and</I>
land1 = 'US'</TT>. <TT>Sy-mandt</TT> contains the current logon
client. When you select rows from a table using Open SQL, the
system automatically adds <TT>sy-mandt</TT> to the <TT>where</TT>
clause, which causes only those rows pertaining to the current
logon client to be found.
<P>
When you create an index on a table containing <TT>mandt</TT>,
therefore, you should also include <TT>mandt</TT> in the index.
It should come first in the index, because it will always appear
first in the generated SQL.
<P>
For the rest of this discussion, please assume that <TT>mandt</TT>
is included in all indexes.
<H4>Code a <TT>Select</TT> to Use a Given Index</H4>
<P>
<IMG SRC="../button/newterm.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/newterm.gif">
<P>
If you code <TT>select * from ztxlfa1 where regio = 'MA'</TT>,
there needs to be an index starting with <TT>mandt</TT> and <TT>regio</TT>
to ensure that the optimizer uses it. If you code <TT>select *
from ztxlfa1 where land1 = 'US' and regio = 'MA'</TT>, there should
be an index containing <TT>mandt</TT>, <TT>land1</TT>, and <TT>regio</TT>.
The ideal index would also have them in that order. If there wasn't
an index in that order, but there was an index starting with <TT>mandt</TT>
and <TT>land1</TT>, the optimizer would use it to match the first
two fields of the <TT>where</TT> clause, and then perform an <I>index
range scan</I> to find a match on <TT>regio</TT>. An index range
scan is a sequential scan through a portion of an index.
<H4>Selective Indexes and Index Effectiveness</H4>
<P>
If the <TT>where</TT> clause contains more fields than the index,
the system uses the index to narrow down the search. It then reads
records from the table and scans them to find matches. For example,
if an index is composed of fields <TT>F1</TT> and <TT>F2</TT>,
and you code <TT>where F1 = 1 and F2 = 2 and F3 = 3</TT>, the
system can only use the index to locate records where <TT>F1=1</TT>
and <TT>F2=2</TT>. It will read those records from the table to
find out which ones have <TT>F3=3</TT>. Therefore, the index is
only partially effective. It would be more effective if it contained
all fields: <TT>F1</TT>, <TT>F2</TT>, and <TT>F3</TT>.
<P>
<IMG SRC="../button/newterm.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/newterm.gif">
<P>
How much more effective is the index with <TT>F3</TT>? It depends
on the way the data is distributed in the table. If the combination
of <TT>F1</TT> and <TT>F2</TT> is very specific (or <I>selective</I>)
and results in only a few matching rows, the index is already
very effective. Adding <TT>F3</TT> to the index in this case might
not be worthwhile because of the overhead of maintaining the index
during table updates. If the combination of <TT>F1</TT> and <TT>F2</TT>
results in a large number of the rows from the table being selected,
the index is <I>not</I> very selective and adding <TT>F3</TT>
will probably increase performance and reduce resource consumption.
<P>
When creating or modifying indexes, try not to use a field that
is already in an index. There is a chance, when a preexisting
program is run, that the optimizer will choose the new index,
and a preexisting program might run slower.
<H4>Guidelines for Creating an Index</H4>
<P>
As a rule of thumb, follow these guidelines when you create an
index:
<UL>
<LI>Try not to add unnecessary indexes. Ask yourself, &quot;Can
I code this to use an existing index?&quot;
<LI>Try to avoid overlapping indexes; in other words, avoid including
the same field in multiple indexes.
<LI>Try to make indexes very selective, but don't include fields
that increase selectiveness by only a little.
</UL>
<P>
<CENTER>
<TABLE BORDERCOLOR=#000000 BORDER=1>
<TR VALIGN=TOP><TD WIDTH=600><B>NOTE</B></TD></TR>
<TR VALIGN=TOP><TD WIDTH=600>
<BLOCKQUOTE>
There is a maximum of 15 secondary indexes per table allowed by R/3.</BLOCKQUOTE>

</TD></TR>
</TABLE>
</CENTER>
<P>
<H3><A NAME="CreatingaSecondaryIndex">
Creating a Secondary Index</A></H3>
<P>
<IMG SRC="../button/screencam.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/screencam.gif">
<P>
Start the ScreenCam &quot;How to Create a Secondary Index&quot;
now.
<P>
To create a secondary index:
<OL>
<LI>Display the table. You should begin on the Dictionary: Table/Structure:
Display Fields screen.
<LI>Press the Indexes. . . button on the Application toolbar (or
choose the menu path Goto-&gt;Indexes. . .).
<LI>If secondary indexes already exist, a list of them will be
displayed in a dialog box named Indexes for Table xxxxx. To create
an index, press the Create button. The Create Index dialog box
will be displayed.
<LI>If no secondary indexes exist, you will not see a list of
indexes. Instead, you will see the Create Index dialog box immediately.
<LI>Enter an index id in the Index ID field. Customer indexes
should begin with <I>Y</I> or <I>Z</I>, although the system does
not enforce this.
<LI>Press the Continue button. The ABAP/4 Dictionary: Table: Maintain
Index screen is displayed.
<LI>In the Short Text field, type a description of the index.
<LI>In the Fld Name column, type the fields that should comprise
the index in the order that they should be sorted.
<LI>If the values in these fields when taken together must always
be unique, tickmark the Unique Index check box.
<LI>Press the Save button on the Application toolbar. The values
in the Status fields are now <TT>New</TT> and <TT>Saved</TT>,
and directly below them the message <TT>Does not exist in the
database</TT> appears. Also, the message <TT>Index xxx to table
xxxxx was saved</TT> appears at the bottom of the window in the
status bar.
<LI>Press the Activate button on the Application toolbar. The
system generates SQL and sends it to the RDBMS, creating the index
in the database. If successful, the Status fields read <TT>Active</TT>
and <TT>Saved</TT>, and below the status fields the message <TT>Exists
in the database</TT> appears. The DB Index Name field will contain
the name of the index in the database. Also, in the status bar,
the message <TT>Index xxxxx was successfully activated.</TT> is
displayed.
<LI>Press Back. The Indexes for Table xxxxx screen is displayed.
<LI>Press Cancel to return to the Dictionary: Table/Structure:
Display Fields screen.
</OL>
<P>
You have learned how to create a secondary index. Indexes improve
the efficiency of the <TT>select</TT> statement.<P>
<CENTER>
<TABLE BORDERCOLOR=#000000 BORDER=1>
<TR VALIGN=TOP><TD WIDTH=600><B>NOTE</B></TD></TR>
<TR VALIGN=TOP><TD WIDTH=600>
<BLOCKQUOTE>
If you tickmark the Unique Index check box, the combination of fields in the index is guaranteed to be unique; this is enforced by the RDBMS. An <TT>insert </TT>or <TT>modify </TT>statement will fail if the combination of values in index fields is already present in the table.
</BLOCKQUOTE>

</TD></TR>
</TABLE>
</CENTER>
<P>
<H3><A NAME="DeletingaSecondaryIndex">
Deleting a Secondary Index</A></H3>
<P>
<IMG SRC="../button/screencam.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/screencam.gif">
<P>
Start the ScreenCam &quot;How to Delete a Secondary Index&quot;
now.
<P>
To delete a secondary index:
<OL>
<LI>Display the table. You should begin on the Dictionary: Table/Structure:
Display Fields screen.
<LI>Press the Indexes. . . button on the Application toolbar (or
choose the menu path Goto-&gt;Indexes. . .). If you see the Create
Index dialog box, no secondary indexes exist for the table, so
you cannot delete one. If secondary indexes already exist, a list
of them will be displayed in a dialog box named Indexes for Table
xxxxx.
<LI>Double-click on the index you want to delete. The ABAP/4 Dictionary:
Table: Display Index screen is shown.
<LI>Press the Display &lt;-&gt; Change button on the Application
toolbar. The screen switches to change mode, and the title of
the screen reads ABAP/4 Dictionary: Table: Maintain Index.
<LI>Choose the menu path Index-&gt;Delete. The Delete Index Definition
dialog box appears asking you to confirm the delete request.
<LI>Press the Yes button to delete the index. You are returned
to the Dictionary: Table/Structure: Display Fields screen. The
message <TT>Index xxxxx deleted</TT> appears in the status bar.
</OL>
<P>
In step six, pressing the Yes button causes the index to be deleted
from the database. When you return to the Dictionary: Table/Structure:
Display Fields screen, you do not need to save the table. Also
note that you cannot undo the delete by pressing the Cancel button
on the table display screen.
<H3><A NAME="DeterminingWhichIndexIsUsed">
Determining Which Index Is Used</A></H3>
<P>
When a <TT>select</TT> statement is executed, the optimizer attempts
to choose the most appropriate index to speed up data retrieval.
If you have several indexes on a table, how can you tell which
index is actually being used, or even if it is using any at all?
<P>
To do this you can use the SQL trace tool.<P>
<CENTER>
<TABLE BORDERCOLOR=#000000 BORDER=1>
<TR VALIGN=TOP><TD WIDTH=600><B>CAUTION</B></TD></TR>
<TR VALIGN=TOP><TD WIDTH=600>
<BLOCKQUOTE>
Only one person can perform an SQL trace at a time. Remember to turn off the trace when you are finished. SQL trace slows the system down.</BLOCKQUOTE>

</TD></TR>
</TABLE>
</CENTER>
<P>
<P>
<IMG SRC="../button/screencam.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/screencam.gif">
<P>
Start the ScreenCam &quot;How to Use SQL Trace to Determine the
Index Used&quot; now.
<P>
To determine which index is being used for a <TT>select</TT> statement:
<OL>
<LI>Create a small ABAP/4 program that contains only the <TT>select</TT>
statement. Before proceeding, test it to ensure that it works.
<LI>Open that program in the editor so that it is ready and waiting
to execute.
<LI>Open a new session using the menu path System-&gt;Create session.
<LI>Run transaction ST05 (enter <B>/nst05</B>-zero-five, not oh-five-in
the Command field, or choose the menu path System-&gt;Utilities-&gt;SQL
Trace). The Trace SQL Database Requests screen is displayed.
<LI>If the Trace SQL Status Information box reads <TT>Trace SQL
is switched off</TT>, go to step 7.
<LI>At this point, the Trace SQL Status Information box contains
<TT>Trace SQL switched on by</TT>, followed by the user id who
turned on the trace and the date and time it was started. You
must switch it off before you can proceed. If the trace was started
within the past hour, it is possible that it is still being used.
Contact the indicated user or try again later. If the trace was
started hours or days ago, the user probably left it on by mistake
and it can be safely turned off. To turn off the trace, press
the Trace Off pushbutton. The message in the Trace SQL Status
Information box should now read <TT>Trace SQL is switched off</TT>.
<LI>Press the Trace On pushbutton. The Trace SQL Database Requests
dialog box is displayed. The DB-Trace for User field should contain
your user ID. If your user ID is not in this field, enter it now.
<LI>Press the OK button. You are returned to the Trace SQL Database
Requests screen and the status information reads <TT>Trace SQL
switched on by</TT>, indicating that you turned on the trace.
<LI>Switch back to the window containing your editor session (the
one with your program waiting to execute).
<LI>Press F8 to run your program. (Only press F8, do not do anything
else, do not even press the Back button.)
<LI>When your program has run and the hourglass is no longer displayed,
switch back to the trace window.
<LI>Press the Trace Off pushbutton. The status information reads