Common Oracle database objects-indexes and synonyms

Common Oracle database objects (mdash); indexes and synonyms

Common Oracle database objects (mdash); indexes and synonyms

I. Index

A) advantages of using Indexes

I. A table-independent schema object that can be stored in a disk or tablespace different from the table

Ii. If the index is deleted or damaged, it does not affect the table. Only the query speed is affected.

Iii. Once an index is created, the Oracle management system automatically maintains the index.

Iv. The system determines when to use the index. You do not need to specify which index to use in the query statement.

V. When you delete a table, all indexes based on the table are automatically deleted.

Vi. Accelerate the query speed of the Oracle server through pointers

Vii. Reduce disk I/O by quickly Locating data

B) how to create an index

I. Automatic Creation: when the PRIMARY KEY or UNIQUE constraint is defined, the system automatically creates a UNIQUE index on the corresponding column.

Ii. Manual creation: You can create non-unique indexes on other columns to accelerate queries.

C) Create an index instance

I. Create an index on one or more columns

Create index indexname on table (column [, column]...);

Ii. Create an index on LAST_NAME In the EMPLOYEES column of the table

Create index emp_last_name_idx on employees (last _ name );

Index create.

D) index creation period

I. index creation

1. Wide data distribution in Columns

2. Columns often appear in the where clause or join condition.

3. Tables are frequently accessed with a large amount of data. The amount of data accessed is about 2% to 4% of the total site data.

Ii. Do not create indexes

1. The table is small.

2. columns are not often used as join conditions or appear in the where clause

3. The queried data is about 2% to 4%

4. Frequent table updates

E) query Indexes

I. You can use the data dictionary views USER_INDEXES and USER_IND_COLUMNS to view the index information.

I. SELECT ic. index_name, ic. column_name,

I. ic. column_position col_pos, ix. uniqueness

I. FROM user_indexes ix, user_ind_columns ic

I. WHERE ic. index_name = ix. index_name

I. AND ic. table_name = 'ployees ';

F) delete an index

I. Use the DROP INDEX command to delete an INDEX

Drop index index;

Ii. Delete the index UPPER_LAST_NAME_IDX

Drop index upper_last_name_idx;

Iii. Only the INDEX owner or users with the drop any index permission can delete the INDEX.

2. Synonyms

A) advantages of using Indexes

I. convenient access to other user objects

Ii. Shorten the Object Name Length

Iii. CREATE [PUBLIC] SYNONYM synonym

Iii. FOR object;

B) Create a synonym

I. Create a synonym for view DEPT_SUM_VU

Create synonym d_sum

FOR dept_sum_vu;

Synonym Created.

C) delete Synonyms

Drop synonym d_sum;

Synonym dropped.