📄 ch05.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<SCRIPT LANGUAGE="JavaScript">
<!--
function popUp(pPage) {
popUpWin = window.open('','popWin','resizable=yes,scrollbars=no,width=525,height=394');
figDoc= popUpWin.document;
zhtm= '<HTML><HEAD><TITLE>' + pPage + '</TITLE>';
zhtm += '</head>';
zhtm += '<BODY bgcolor="#FFFFFF">';
zhtm += '<IMG SRC="' + pPage + '">';
zhtm += '<P><B>' + pPage + '</B>';
zhtm += '</BODY></HTML>';
figDoc.write(zhtm);
figDoc.close();
popUpWin.focus();
}
//-->
</SCRIPT>
<META NAME="GENERATOR" Content="Symantec Visual Page 1.0.1">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<TITLE>Sams Teach Yourself ABAP/4® in 21 Days -- Day 5- The Data Dictionary, Part 3</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../button/sams.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/sams.gif" BORDER="0"></H1>
</CENTER>
<CENTER>
<P><A HREF="../ch04/ch04.htm" tppabs="http://pbs.mcp.com/ebooks/0672312174/ch04/ch04.htm"><IMG SRC="../button/previous.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/previous.gif" BORDER="0"></A>
<A HREF="../index.htm" tppabs="http://pbs.mcp.com/ebooks/0672312174/index.htm"><IMG SRC="../button/contents.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/contents.gif" BORDER="0"></A>
<A HREF="../ch06/ch06.htm" tppabs="http://pbs.mcp.com/ebooks/0672312174/ch06/ch06.htm"><IMG SRC="../button/next.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/next.gif" BORDER="0"></A>
<H1>Sams Teach Yourself ABAP/4<sup>®</sup> in 21 Days</H1></CENTER>
<HR SIZE=4>
<H1>Day 5</H1>
<H1>The Data Dictionary, Part 3</H1>
<UL>
<LI><A HREF="#ChapterObjectives">
Chapter Objectives</A>
<LI><A HREF="#UnderstandingTableIndexes">
Understanding Table Indexes</A>
<UL>
<LI><A HREF="#DisplayingIndexes">
Displaying Indexes</A>
<LI><A HREF="#ConsiderationsWhenCreatinganIndex">
Considerations When Creating an Index</A>
<LI><A HREF="#CreatingaSecondaryIndex">
Creating a Secondary Index</A>
<LI><A HREF="#DeletingaSecondaryIndex">
Deleting a Secondary Index</A>
<LI><A HREF="#DeterminingWhichIndexIsUsed">
Determining Which Index Is Used</A>
</UL>
<LI><A HREF="#DisplayingTechnicalSettings">
Displaying Technical Settings</A>
<UL>
<LI><A HREF="#DataClass">
Data Class</A>
<LI><A HREF="#SizeCategory">
Size Category</A>
<LI><A HREF="#DisplayingtheNumberofExtentsAllocatedtoaTable">
Displaying the Number of Extents Allocated to a Table</A>
<LI><A HREF="#BufferingaTable">
Buffering a Table</A>
</UL>
<LI><A HREF="#Summary">
Summary</A>
<LI><A HREF="#QampA">
Q&A</A>
<LI><A HREF="#Workshop">
Workshop</A>
<UL>
<LI><A HREF="#Quiz">
Quiz</A>
<LI><A HREF="#Exercise">
Exercise 1</A>
</UL></UL>
<HR>
<H2><A NAME="ChapterObjectives"><FONT SIZE=5 COLOR=#FF0000>
Chapter Objectives</FONT></A></H2>
<P>
After you complete this chapter, you should be able to:
<UL>
<LI>Create and use secondary indexes appropriately
<LI>Set up the technical attributes for transparent tables
<LI>Set up buffering for tables
</UL>
<H2><A NAME="UnderstandingTableIndexes"><FONT SIZE=5 COLOR=#FF0000>
Understanding Table Indexes</FONT></A></H2>
<P>
An <I>index</I> is an efficiency mechanism for quickly finding
rows within a table. It is needed because data is stored in a
table in the order in which it was added (the <I>order of arrival</I>).
<P>
Picture yourself ripping all the pages out of this book and then
throwing them into the air and mixing them all up. You then gather
them into a stack without regard to any sort of order. If you
were to look for a single page number within that stack, how would
you do it?
<P>
Some people might just hunt and peck though the stack hoping to
find it. I'm sure that most would soon give up and then turn the
pages one by one. Obviously, this job would be easier if the pages
were all back in order. With the pages in order, you could start
somewhere in the middle and knowing which way to turn, you could
quickly zero in on the page you want.
<P>
Imagine that you have now put the pages back in order. Picking
up the newly ordered book, perhaps you begin to look for the page
that describes SE16. Where do you look? You don't know the page
number, so you look in the index. The index contains the most
important words sorted in alphabetical order. You quickly locate
SE16 in the index and obtain a page number. Using that, search
through the book to find that page.
<P>
To create an index, the publisher duplicates important information
and then sorts it, placing it at the end of the book apart from
the rest. When you look at it, are you looking at the original
information? No, of course not. You are looking at a copy of the
original information; it has been duplicated. The book takes up
a little more space on your bookshelf because of the index, it
adds a little to the price of the book because of the time and
effort required to create it, but no one complains because it
saves so much time when you want to find something.
<P>
A table in the database is a lot like this book. Each row is given
a sequential number when it is added to a table, much like a page
number. The important columns are copied to an index and sorted,
much like the index at the back of the book. To find any piece
of data, the system searches the index first to find the row number
and then quickly finds the row.
<P>
Each R/3 table has a primary index built on the primary key of
the table. This index is needed because the data in the table
is stored in the order it was added to the table. When a row is
added, the key information is sorted and copied to the index,
along with a pointer to the original row. Figure 5.1 shows an
example using table <TT>ztxlfa1</TT>. The data from the <TT>lifnr</TT>
field in <TT>ztxlfa1</TT> is copied to the index, sorted alphabetically,
and stored along with the row number of the original row. (The
<TT>mandt</TT> field has been left out to simplify the example.)
<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>
The row number is internally assigned by the database; you never see it in the DDIC. It is used by the index to point back to the original row from which the data came.</BLOCKQUOTE>
</TD></TR>
</TABLE>
</CENTER>
<P>
<P>
When the statement <TT>select * from ztxlfa1 where lifnr = '0000001050'</TT>
is executed, the system recognizes that it has <TT>lifnr</TT>
already sorted in an index, and thus uses it automatically. It
finds <TT>1050</TT> in the index and obtains row number <TT>2</TT>.
It then looks in the table for row number <TT>2</TT>.
<P>
Now suppose the statement <TT>select * from ztxlfa1 where land1
= 'US'</TT> is executed. The index is sorted by <TT>lifnr</TT>,
not <TT>land1</TT>. The <TT>land1</TT> column is not in any particular
order in the table. The system will have to scan the table from
the beginning to the end looking for all rows where <TT>land1
= 'US'</TT>. The table in Figure 5.1 is very small, but this is
a very time-consuming process on a larger table.
<P>
<A HREF="javascript:popUp('f5-1.gif')"><B>Figure 5.1 :</B> <I>A table index is a copy of one or more columns
stored in sorted order with pointers to the original rows</I>.</A>
<P>
Now think back to the book example. Sometimes a book has more
than one index. For example, a book on the medicinal uses of herbs
might have the usual index, plus an index only for common plant
names, another only for botanical names, and a third only for
medicinal effects. To create these indexes, information has to
be sorted and duplicated in three more places at the back of the
book. Then, there are four ways to find a page number quickly:
via the commonly used index or any of the three additional indexes.
<P>
<IMG SRC="../button/newterm.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/newterm.gif">
<P>
The commonly used index is analogous to the primary index of a
table. The additional indexes are analogous to <I>secondary indexes</I>.
To support a <TT>where</TT> clause on a non-primary key field,
a secondary index can be defined. A table can have multiple secondary
indexes.
<P>
Figure 5.2 illustrates a secondary index on <TT>ztxlfa1-land1</TT>.
<P>
<A HREF="javascript:popUp('f5-2.gif')"><B>Figure 5.2 :</B> <I>A secondary index enables you to search
quickly on columns other than those in the primary key</I>.</A>
<P>
An index increases performance tremendously. In a table containing
a million rows, a <TT>select</TT> on a column containing unique
values that is not supported by an index would read, on average,
500,000 records to find the matching one, with a worst-case scenario
of one million compares.
<P>
If you add an index, the system can use a binary search algorithm
to find the record after comparing an average of only 10 records,
with a worst-case scenario of 20 records! That's 50,000 times
faster. For this reason, <I>you should ensure that fields in a
</I><TT>where</TT><I> clause are always supported by an index</I>.
This can be done either by displaying existing indexes and then
organizing your logic to use these fields, or by creating a secondary
index.
<H3><A NAME="DisplayingIndexes">
Displaying Indexes</A></H3>
<P>
Each index in R/3 has a unique one to three character identifier,
or <I>id</I>. The primary index always has an id of 0 (zero).
A secondary index id can be any other number or an alphabetic
character.
<P>
<img src="../button/screencam.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/screencam.gif">
<P>
Start the ScreenCam "How to Display Secondary Indexes"
now.
<P>
To display the existing secondary indexes on a database table,
follow this procedure:
<OL>
<LI>Display the table.
<LI>Press the Indexes. . . button on the Application toolbar.
If you do not see this button, your window might be too narrow.
Widen it or choose the menu path Goto->Indexes. . ..
<LI>If secondary indexes exist, a list of them will be displayed
in a dialog box. If none exist, a message will be displayed.
<LI>To display one of them, double-click on it. The index is displayed
in the ABAP/4 Dictionary: Table: Display Index screen, as shown
in Figure 5.3.
</OL>
<P>
<P>
<A HREF="javascript:popUp('f5-3.gif')"><B>Figure 5.3 :</B> <I>The secondary index Z in table ztxlfa1 is
composed of the fields mandt and land1</I>.</A>
<P>
The status of the index is displayed in the Status field. Whether
the index exists in the database is indicated by a message appearing
below the Status field. In the DB Index Name field is the name
of the index as it is known in the database. The index name in
the database is formed from the table name, followed by underscores,
and then the index id. In the lower part of the window are the
fields that comprise the index. The index sort order is the same
as the field order.
<P>
The preceding procedure will only show secondary indexes. The
index on the primary key (index 0) does not appear in this list.
<P>
<IMG SRC="../button/screencam.gif" tppabs="http://pbs.mcp.com/ebooks/0672312174/button/screencam.gif">
<P>
Start the ScreenCam "How to Display All Indexes" now.
<P>
To display all indexes, including the primary index, follow these
steps:
<OL>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -