Indexes:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1895
http://www.oracle.com/us/education/my-richardfoote-seminar-079059.html
A table dependent object. (Root — Branch –Leaf)
An index is an optional structure, associated with a table or table cluster.
Index properties:
Usability
Indexes are usable (default) or unusable. An unusable index is not maintained by DML operations and is ignored by the optimizer. An unusable index can improve the performance of bulk loads. Instead of dropping an index and later re-creating it, you can make the index unusable and then rebuild it. Unusable indexes and index partitions do not consume space. When you make a usable index unusable, the database drops its index segment.
When we move the table without index it will be unusable. A direct path load against a table or partition.
Visibility
Indexes are visible (default) or invisible. An invisible index is maintained by DML operations and is not used by default by the optimizer. Making an index invisible is an alternative to making it unusable or dropping it. Invisible indexes are especially useful for testing the removal of an index before dropping it or using indexes temporarily without affecting the overall application.
Types:
B tree index — the default and the most common.
Options for B tree { Index-organized tables – IOT ,Cluster index table, Composite index, Unique index, Non unique index, Descending indexes, Reverse key indexes }
| Normal Index (Default) | IOT | Cluster index Table |
| Optional | IOT must contain a primary key | No |
| Yes | An IOT cannot be in a cluster | Yes |
| Can modify | Cannot modify an IOT, Only ALTER TABLE | Can modify |
| Unique columns | Small tables can be good candidates for IOTs | Common columns of more table |
| It stores only an index key column, in a separate storage | IOT indexes store all the columns of the table, no separate storage needed. | It stores only index key column, no separate storage needed. |
| It stores as a logical order | IOT stores as PK values order will be faster | It stores a block, not point rows |
| No | Secondary indexes Can be created | No |
| Unlimited | Max columns 1000, Max PK column 32, Max index portion 255 | Maximum 32 columns & cluster Index table |
IOT – Initially it stores primary key & logical rowid as same order, when updates the PK might change but not logical. As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized.
Index-organized tables – An index-organized table differs from a heap-organized because the data is itself the index. (SQL aka cluster index). An INCLUDING clause, which can be used to specify the nonkey columns that are to be stored in the index block with the primary key.
Normal primary key indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table.
http://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg07iot.htm
CREATE TABLE tbl_clu_test (id int NOT NULL PRIMARY KEY, n1 int) ORGANIZATION INDEX
Cluster index Table – It is a table cluster that uses an index to locate data. Instead of pointing to a row, the key points to the block that contains rows related to the cluster key.
CREATE CLUSTER employees_departments_cluster (department_id NUMBER(4)) SIZE 512;
CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;
CREATE TABLE employees ( … ) CLUSTER employees_departments_cluster (department_id);
CREATE TABLE departments ( … ) CLUSTER employees_departments_cluster (department_id);
| Heap Table | Index Table |
| The rowid uniquely identifies a row | Primary key uniquely identifies a row. |
| Can be stored in a table cluster with other tables. | Cannot be stored in a table cluster. |
| Can contain LOB & LONG columns. | Can contain LOB columns but not LONG columns. |
A cluster is simply a method for storing more then 1 tables same column on the same block. The cluster key value is the value of the cluster key columns for a particular row.
Reverse key indexes — most useful for Oracle RAC and emp id: EE100. The bytes of the index key are reversed.
Bitmap index and bitmap join indexes— compact; work best for columns with a small set of values.
In a bitmap index, each index key stores pointers to multiple rows. It’s designed for data warehousing.
Function based index — contain the precomputed value of a function ()/expression.
A function-based index can be either a B-tree or a bitmap index.
Application domain indexes — This type of index is created by a user for data in an application-specific domain. The physical index need not use a traditional index structure and can be stored either in the Oracle database as tables or externally as a file.
It’s mostly used for Unstructured Data – Image, video etc..
Difference between B tree and Bitmap
| B tree | Bit Map |
| High cardinality column (Low duplicate) | Low cardinality column (High duplicate) |
| And, OR, Not will not be useful | And, OR, Not will be used |
| By default all indexes b tree |
B tree: Mostly created for unique columns/values
Sql> Create index <ind name> on <table> (col1) ;
Bit Map: – Mostly created for duplicate columns/values
Sql> Create bitmap index <ind name> on <table> (col1) ;
Composite Index: If you specify more than one columns
Sql> Create index <ind name> on <table> (col1,col2..) ;
Unique Index: Used for unique columns
Sql> Create unique index <ind name> on <table> (col1) ;
Reverse key Index: Mostly created for values has character and numeric values
Ex: Empid – AAA10… Normal index will serach all records, but revese index will search in reverse direction and improve the search.
Sql> Create index <ind name> on <table> (col1) reverse;
Function Based Index: Mostly created for arithmetic operations and predefined function.
Sql> Create index <ind name> on <table> (salary+ (salary0.05));
How to find the index is currently used or not
Sql> Alter index <ind name> monitoring usage;
Sql> Select * from v$object_usage where index_name =’index name’;
Sql> Alter index <ind name> no monitoring usage;
Query to find the index is in the database and present users
Sql> Select * from dba_indexes; — It will show all indexes
Sql> Select * from user_indexes; — present users indexes
Sql> Select * from dba_ind_columns; — It will show all indexes columns
Sql> Select * from user_ind_columns; — present users columns
Drop index
Sql> Drop index <ind name>;
Sql> Drop index <ind name> cascade;
Constraints
All are same as SQL.
PRIMARY KEY
Specifies the column or columns that uniquely identify a row in the table. NULL values are not allowed.
UNIQUE
Specifies that values in the columns must be unique. It will allow NULL.
FOREIGN KEY
Specifies that the values in the columns must correspond to values in referenced primary key or unique columns or that they are NULL.
Note: If the foreign key consists of multiple columns, and any column is NULL, the whole key is considered NULL. The insert is permitted no matter what is on the non-null columns.
CHECK
Specifies a wide range of rules for values in the table.
Column constraints and table constraints have the same function; the difference is in where you specify them. Table constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE, CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except for check constraints) refer to only one column.
