How to add index in plsql?

Method to add index in plsql: 1. Right-click the data table directly on the SQL editing page of the PLSQL interface and select "Edit"; 2. In the pop-up interface, obtain the owner and name information; 3. Click Index, fill in the relevant information, and then click "..." below the column; 4. In the pop-up window, specify the index column and click "OK"; 5. Click "Apply".

#An index is a structure that sorts the values ​​of one or more columns in a table. Indexes can be used to quickly access specific information in a database table. If you want to find a specific employee by his or her last name, an index can help you get the information faster than searching for all the rows in the table.

One of the main purposes of the index is to speed up the method of retrieving data in the table, that is, an auxiliary data structure that can help information searchers find record IDs that meet the restrictions as quickly as possible.

To put it simply, based on the sql statement, an index is added to the database for the condition after where. Then every time when searching, the condition will be found in the index first, saving search time.

How to add an index in plsql

Step one: You need to create an index on the table "linshibiao".

You can use the object on the left side of the PLSQL interface—>Table—>linshibiao—>right-click—>click to edit

(You can also right-click the table linshibiao directly on the SQL editing page— >Edit, sometimes you can’t click it, maybe it’s your own fault)

Step 2: Go directly to the following interface, the owner and name here are useful.

Step 3: Click on the index, fill in the relevant information, owner, name obtained in the second step, and then click below the column...as shown in the figure below

Step 4: I need to set cardid as the index, click cardid, click >, click Confirm

## Step 5: Click the "Apply" button below to complete index creation.

Note: Occasionally an error message will appear when clicking "Apply". In this case, just restart PLSQL.

1 Advantages of Index

1. By creating a unique index, the uniqueness of each row of data in the database can be guaranteed.

2. It can greatly speed up data access. This is the main reason why indexing is needed.

3. When grouping and sorting queries, the grouping and sorting time can also be significantly reduced (the sorting rules must be the same as the index sorting).

4. When querying based on table connections, the speed of table connections is greatly accelerated (data integrity reference).

2 Disadvantages of index

1. Reduces the speed of data maintenance, such as DELETE, UPDATE, and INSERT operations.

2. Creating and maintaining indexes takes time, and it continues to increase as the amount of data increases.

3. Excessive or inappropriate indexing will actually reduce data retrieval efficiency.

4. The index requires disk storage space. If it is a clustered index, the disk overhead will be greater.

3 The concept of index

1. In a relational database, the physical location of row storage is irrelevant, but when the row needs to be found, the row location is crucial. Oracle uses a RowID to identify each row of data, which stores the exact location of the row (the file where the row is located, the block in the file, and the row address in the block).

2. An index is an Oracle internal structure that provides quick location of a row of data in a table. An index is a data structure within Oracle that stores multiple rows of data (including a copy of an index column and the corresponding RowID in the indexed table). It can be understood as a miniaturized copy of the Oracle table;

3. The purpose of the index is to speed up data retrieval.

The above is the detailed content of How to add index in plsql?. For more information, please follow other related articles on the PHP Chinese website!