Oracle 9i Index

An index is a list of keywords for several data rows that, when queried for data, can be quickly positioned through the keywords in the index to the block of data to which the record is to be accessed, greatly reducing the number of I/O to the data block, and thus significantly improving performance.

The principle of indexing

The following statement is executed in "Sqlplus Worksheet" by querying the rowID list "Scott.student" of the datasheet, and the query results are shown in Figure 7.38.
―――――――――――――――――――――――――――――――――――――
Select rowid,student_id,name,professional,birthday,director_id from Scott.student;
―――――――――――――――――――――――――――――――――――――
"See CD files": \ 7th chapter \ Selectrowid.sql.

Taking the index of the datasheet "Scott.student" as an example, the following data is part of the data that is indexed after the "student_id" data is listed as an example.
―――――――――――――――――――――――――――――――――――――
ROWID student_id
------------------ ----------
AAAH2OAAIAAAABSAAA 20020101
Aaah2oaaiaaaabsaaj 20020102
Aaah2oaaiaaaabsaak 20020103
Aaah2oaaiaaaabsaal 20020104
Aaah2oaaiaaaabsaan 20030102
Aaah2oaaiaaaabsaao 20030103
Aaah2oaaiaaaabsaap 20030104
―――――――――――――――――――――――――――――――――――――

Oracle 9i-Supported indexes

The indexes in Oracle 9i can be grouped into two broad categories: B-Tree indexes and bitmap indexes.

Table 7.9 B-Tree Index classification

Name	Meaning
Non-unique	Non-unique index, default B-Tree index, indexed column value can not be unique
Unique	A unique index that specifies the "unique" keyword when creating an index to create a unique index. A unique index is also established when the Master code constraint is established, and the indexed column value is unique
Reverse Key	Reverse keyword index, you can create a reverse keyword index by specifying the "REVERSE" keyword when you create an index, and the data in each data column that is indexed is stored in reverse, but remains in the order of the original data column
Function-based	A function-based index that uses an expression for a data column and creates the nodes of a B-tree index, according to the expression result, for an organization that is specific to the index of a datasheet that frequently uses the expression for similar queries

2. Bitmap indexing
The possible instances of creating a bitmap cable for the data column "professional" of the "Scott.student" datasheet are as follows.
―――――――――――――――――――――――――――――――――――――
Record number Professional value bitmap gourmands ?br> 1 Software Engineering 1
2 Computer Security 2
3 Graphic Images 3
7 Web Security 2
―――――――――――――――――――――――――――――――――――――

Index automatically built by main code

(1) as shown in Figure 7.40.

(2) The General Information tab of the editing index appears as shown in Figure 7.41.


How to create an index

(1) as shown in Figure 7.42.

(2) The General Information tab that creates the index appears as shown in Figure 7.43.

(3) switch to the Partition tab of the index as shown in Figure 7.44.
(4) Switch to the Storage tab of the index creation shown in Figure 7.45.

(5) Switch to the Options tab of the index as shown in Figure 7.46.
(6) When the index is successfully created, the interface shown in Figure 7.47 appears.

(7) You can view the index created in Enterprise Manager, as shown in Figure 7.48.

(8) The SQL code corresponding to the above creation index is as follows.
―――――――――――――――――――――――――――――――――――――
CREATE INDEX "SCOTT". Name field index "
On "SCOTT". " STUDENT "(" NAME ")
Tablespace "INDX";
―――――――――――――――――――――――――――――――――――――
"See CD files": \ 7th chapter \ Createindex.sql.
(9) The reader can also perform the creation of the index Createindex.sql file directly in "Sqlplus Worksheet", as shown in Figure 7.49.


How to delete an index

(1) as shown in Figure 7.50.
(2) The "Index deletion confirmation" interface appears as shown in Figure 7.51.