6.1. Index--oracle Mode Object

1. Create an index:

(1). Single-Column index

Create index name_index on outpatient expense record (ID)

(2). Composite Index

The same table can have multiple indexes, but the combination of required columns must be different.

-For example:

Create index emp_id on EMP (ename,job);

Create index emp_id on EMP (job,ename);

The search is based on a backward-forward order, so the index should be based on the column's sensitivity

2. Principle of Creation:

(1). Creating an index on a large table makes sense;

(2). On the WHERE clause or join condition, the index is indexed on the frequently referenced column ;

(3). The level of the index should not exceed 4 levels.

3. View Index

Show all indexes contained on the table:

Querying the Data dictionary view dba_indexs(Database all indexes) and User_indexs(index of the current user)

Select Index_name,index_type from User_indexs where table_name= ' table name ';

-For example:

SELECT * from user_indexes where table_name= ' outpatient expense Records '

To display the columns in the index:

Querying the Data dictionary view user_ind_columns

Select table_name,column_name from User_ind_columns where index_name= ' ind_name ';

-For example:

Select * from User_ind_columns where index_name in (' Outpatient fee record _ix_ registration time ', ' Outpatient fee record _ix_ Order number ')

4, the advantages and disadvantages of the index:

Advantages:

(1). By creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

(2). The retrieval speed of the data can be greatly accelerated, which is the most important reason to create the index.

(3). The connection between tables and tables can be accelerated, particularly in terms of the referential integrity of the data.

(4). When you use grouping and sort clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

(5). By using an index, you can improve the performance of your system by using an optimized hidden device during the query.

Disadvantages:

(1). Takes up hard disk and memory space;

(2). When the data is updated (modified), the index is maintained dynamically, and the system requires additional time and resources to participate in maintenance to maintain data and index consistency;

6.1. Index--oracle Mode Object