INDEX
Index provides a faster access path to table data.
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space.
You can create or drop an index without affecting the base tables, database, applications, or other indexes.
The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.
Indexes in columns containing character data are based on the binary values of the characters in the database character set.
For a unique index, one rowid exists for each data value.
For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls without violating a unique index.
Oracle sometimes creates indexes for you. When you define a primary key constraint, Oracle will create an index for you. When you define a unique constraint, Oracle will also create an index for you.
Index provides a faster access path to table data.
Indexes are logically and physically independent of the data in the associated table. Being independent structures, they require storage space.
You can create or drop an index without affecting the base tables, database, applications, or other indexes.
The database automatically maintains indexes when you insert, update, and delete rows of the associated table. If you drop an index, all applications continue to work. However, access to previously indexed data might be slower.
Indexes in columns containing character data are based on the binary values of the characters in the database character set.
For a unique index, one rowid exists for each data value.
For a nonunique index, the rowid is included in the key in sorted order, so nonunique indexes are sorted by the index key and rowid. Key values containing all nulls are not indexed, except for cluster indexes. Two rows can both contain all nulls without violating a unique index.
Oracle sometimes creates indexes for you. When you define a primary key constraint, Oracle will create an index for you. When you define a unique constraint, Oracle will also create an index for you.
Different Types of Index in Oracle
Ascending Index : default type
Descending Index: If in the sql, first column always order by descending.
2. BITMAP Index
- Used in data warehouses
- Compact; work best for columns with a small set of values
- Where repeating values arise, bitmap would be a good choice in OLAP.
3. PARTITIONED Index
Partitioned index consist of partitions containing an entry for each value that appears in the indexed column(s) of the table. Types -
Global indexes
Local indexes
4. FUNCTION based Index
Which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
5. DOMAIN Index
Application-specific index.
6. CLUSTER Index
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes: defined specifically for a hash cluster
There are more indexes like -
Key compressed Index
Reverse key index : Mostly useful for Oracle RAC applications
Index Organized Table
There are several types of index
available for use in Oracle databases, the most common ones are B-tree
(balanced tree) indexes, function-based indexes and bitmap indexes.
B-tree indexes are more common in databases supporting OLTP systems and Bitmap indexes are more commonly used in data warehouses.
1. Normal Index
By default, Oracle Database creates B-tree indexes/Normal index.1. Normal Index
Ascending Index : default type
Descending Index: If in the sql, first column always order by descending.
2. BITMAP Index
- Used in data warehouses
- Compact; work best for columns with a small set of values
- Where repeating values arise, bitmap would be a good choice in OLAP.
3. PARTITIONED Index
Partitioned index consist of partitions containing an entry for each value that appears in the indexed column(s) of the table. Types -
Global indexes
Local indexes
4. FUNCTION based Index
Which are based on expressions. They enable you to construct queries that evaluate the value returned by an expression, which in turn may include built-in or user-defined functions.
5. DOMAIN Index
Application-specific index.
6. CLUSTER Index
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes: defined specifically for a hash cluster
There are more indexes like -
Key compressed Index
Reverse key index : Mostly useful for Oracle RAC applications
Index Organized Table
SYNTAX
Create Index
Create B-tree or default Index on single column -
CREATE INDEX emp_deptno ON empinfo (deptno);
CREATE INDEX emp_deptno ON empinfo (deptno);
CREATE INDEX emp_deptno ON empinfo (deptno) TABLESPACE INDX02_16K;
Multi column -
CREATE INDEX emp_seniority ON empinfo (deptno, hire_date);
Unique Index -
CREATE UNIQUE INDEX dname_uix ON dept (dname);
Alter Index
Rebuilding Index -
ALTER INDEX LB_SUBMITTER REBUILD;
ALTER INDEX LB_SUBMITTER REBUILD;
Moving Index to a new tablespace -
ALTER INDEX test REBUILD TABLESPACE INDX02_16K;
ALTER INDEX test REBUILD TABLESPACE INDX02_16K;
Renaming Index -
ALTER INDEX SYS_C007109 RENAME TO LOGIN_ID;
Quick-and-Dirty rebuild -
ALTER INDEX uniq_payroll_id COALESCE;
Dropping Index
DROP INDEX Index_name;
DROP INDEX Index_name;
Data dictionary Views for Indexes
ALL_INDEXES SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
FROM all_indexes
ALL_INDEXES SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
FROM all_indexes
WHERE TABLE_NAME='LOGIN';
USER_INDEXES
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
USER_INDEXES
SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME
FROM user_indexes
WHERE TABLE_NAME='NY_IP';
USER_IND_COLUMNS SELECT *
USER_IND_COLUMNS SELECT *
FROM user_ind_columns ic
WHERE ic.table_name='LB_LOG_BOOK';
