Indexes and Sequences

Unique and Nonunique Indexes
Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns).
For performance reasons, Oracle recommends that unique indexes be created explicitly, and not through enabling a unique constraint on a table. (Unique integrity constraints are enforced by automatically defining an index.)
You can create many indexes for a table as long as the combination of columns differs for each index.
CREATE INDEX emp_idx1 ON emp (ename, job);
CREATE INDEX emp_idx2 ON emp (job, ename);
The absence or presence of an index does not require a change in the wording of any SQL statement. An index is merely a fast access path to the data.
The query optimizer can use an existing index to build another index. This results in a much faster index build.
Index multiple columns
A composite index is an index that you create on multiple columns in a table.
This can speed retrieval of data if the SQL WHERE clause references all (or the leading portion) of the columns in the index. Therefore, the order of the columns used in the definition is important - the most commonly accessed or most selective columns go first.
Rebuilding indexes
Although indexes can be modified with ALTER INDEX abc REBUILD it is a commonly held myth about rebuilding indexes that performance will automatically improve.
By contrast redesigning an index to suit the SQL queries being run will give measurable results.
Function-Based Indexes
You can create indexes on functions and expressions that involve columns in the table being indexed.
A function-based index precomputes the value of the function or expression and stores it in an index (B-tree or bitmap).
Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches. For example, the following index:
CREATE INDEX uppercase_idx ON emp (UPPER(empname));
can facilitate processing queries such as this:
SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';
To use function-based indexes you must gather optimizer statistics.
(Not compatible with Rule-based optimization.)
If the function is a PL/SQL function or package function, any changes to the function specification will cause the index to be automatically disabled.
How Indexes Are Searched
Index unique scan used when all columns of a unique (B-tree) index are specified with equality conditions.
e.g.
name = 'ALEX'
Index range scan is used when you specify a wildcard or interval (bounded by a start key and/or end key.)
e.g.
name LIKE 'AL%'
order_id BETWEEN 100 AND 120
order_book_date > SYSDATE - 30
Key Compression
Like any form of compression, Key compression can lead to a huge saving in space, letting you store more keys in each index block, which can lead to less I/O and better performance. Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead.
Reverse Key Indexes
Creating a reverse key index, compared to a standard index, reverses the bytes of each column indexed (except the rowid) while keeping the column order. By reversing the keys of the index, the insertions become distributed across all leaf keys in the index.
CREATE INDEX i ON t (my_id) REVERSE;

The values 4771, 4772, 4773 in the index are reversed to 1774, 2774, 3774
The more even distribution of "hits" on the various leaf blocks is the RKI's best feature. In a heavy, concurrent insert environment, rather than having everyone wanting access to *the* block, you spread the blocks being
hit and hence reduce the potentially expensive buffer busy waits.
The main disadvantage is the inability to perform index range scans as such values are now distributed all over the place, only fetch-by-key or full-index (table) scans can be performed.
You can specify the keyword NOREVERSE to REBUILD a reverse-key index into one that is not reverse keyed: Indexes. Rebuilding a reverse-key index without the NOREVERSE keyword produces a rebuilt, reverse-key index. You cannot rebuild a normal index as a reverse key index. You must use the CREATE statement instead.
Bitmap Indexes ( Enterprise Edition only.)
In a bitmap index, a bitmap for each key value is used instead of a list of rowids.
Each bit in the bitmap corresponds to a possible rowid. If the bit is set, then it means that the row with the corresponding rowid contains the key value. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, then bitmap indexes are very space efficient. Bitmap indexing is of great benefit to data warehousing applications.
Bitmap indexes are good for:
Low cardinality columns have a small number of distinct values (compared to the number of rows)
e.g. Gender or Marital Status
High cardinality columns have large numbers of distinct values (over 100).
Bitmap indexes include rows that have NULL values, and can dramatically improve the performance of ad hoc queries.
Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all, conditions are filtered out before the table itself is accessed. This improves response time, often dramatically.
Unlike traditional a B-tree indexes, Bitmap indexes are typically only a fraction of the size of the
indexed data in the table.
Bitmap indexes are also not suitable for columns that are primarily queried with less than or greater than comparisons. For example, a salary column that usually appears in WHERE clauses in a comparison to a certain value is better served with a B-tree index.
Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.
The advantages of bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. (See the Oracle concepts manual for an example of this)
Unlike most other types of index, Bitmap indexes include rows that have NULL values. This can be useful for queries such as SELECT COUNT(*) FROM EMP;
You can create bitmap indexes local to a partitioned table (not a global index).
Bitmap Join Indexes
A join index is an index on one table that involves columns of one or more different tables through a join.
Similar to the materialized join view, a bitmap join index precomputes the join and stores it as a database object. The difference is that a materialized join view materializes the join into a table while a bitmap join index materializes the join into a bitmap index. See the Oracle concepts manual for a full example.
Dimensions
Define hierarchical relationships between pairs of columns or column sets. (Typically data warehouse parent-child relationships.)
The columns in a dimension can come either from the same table (denormalized) or from multiple tables (fully or partially normalized).
To define a dimension over columns from multiple tables, connect the tables using the JOIN clause of CREATE DIMENSION HIERARCHY.
Sequences
The sequence generator provides a sequential series of numbers. The sequence generator is especially useful for generating unique sequential ID numbers.
Individual sequence numbers can be skipped if they were generated and used in a transaction that was ultimately rolled back.
A sequence generates a serial list of unique numbers for numeric columns of a database's tables. Sequences simplify application programming by automatically generating unique numerical values for the rows of a single table or multiple tables.
For example, assume two users are simultaneously inserting new employee rows into the EMP table. By using a sequence to generate unique employee numbers for the EMPNO column, neither user has to wait for the other to enter the next available employee number. The sequence automatically generates the correct values for each user.
Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. After creation, a sequence can be accessed by various users to generate actual sequence numbers.

"Mathematicians have tried in vain to this day to discover some order in the sequence of prime numbers, and we have reason to believe that it is a mystery into which the human mind will never penetrate" - Leonhard Euler

CREATE INDEX

Create an index.

Syntax:

Table Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col [ASC | DESC]) index_clause index_attribs

Bitmap Join Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON [schema.]table_name [tbl_alias]
         (col_expression [ASC | DESC])
            FROM [schema.]table_name [tbl_alias]
               WHERE condition [index_clause] index_attribs

Cluster Index
   CREATE [UNIQUE|BITMAP] INDEX [schema.]index_name
      ON CLUSTER [schema.]cluster_name index_attribs

index_clauses:

LOCAL STORE IN (tablespace)

LOCAL STORE IN (tablespace)
  (PARTITION [partition
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause]
       [STORE IN {tablespace_name|DEFAULT]
       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

LOCAL (PARTITION [partition
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause]
       [STORE IN {tablespace_name|DEFAULT]
       [SUBPARTITION [subpartition [TABLESPACE tablespace]]]])

GLOBAL PARTITION BY RANGE (col_list)
   ( PARTITION partition VALUES LESS THAN (value_list)
       [LOGGING|NOLOGGING]
       [TABLESPACE {tablespace|DEFAULT}]
       [PCTFREE int]
       [PCTUSED int]
       [INITRANS int]
       [MAXTRANS int]
       [STORAGE storage_clause] )

INDEXTYPE IS indextype [PARALLEL int|NOPARALLEL] [PARAMETERS ('ODCI_Params')]
 {This for table index only, not bitmap join Index}

index_attribs:
any combination of the following

    NOSORT|SORT
    REVERSE
    COMPRESS int
    NOCOMPRESS
    COMPUTE STATISTICS
    [NO]LOGGING
    ONLINE
    TABLESPACE {tablespace|DEFAULT}
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause
    PARALLEL parallel_clause

If the PARALLEL clause is used it should be the last option.
For example:
To create a function-based index which allows case-insensitive searches.

CREATE INDEX idx_case_ins ON my_table(UPPER(empname));

SELECT * FROM my_table WHERE UPPER(empname) = 'KARL'; 

"Anything you build on a large scale or with intense passion invites chaos" - Francis Ford Coppola 

ALTER INDEX

Change the properties of an index.

Syntax:

   ALTER INDEX [schema.]index options

Options:
The options used with this command can be any combination of the following

    ENABLE
    DISABLE
    COALESCE
    UNUSABLE
    RENAME TO new_index_name

    [NO]LOGGING
    PCTFREE int
    PCTUSED int
    INITRANS int
    MAXTRANS int
    STORAGE storage_clause

    ALLOCATE EXTENT [SIZE int K | M]
    ALLOCATE EXTENT [DATAFILE 'filename']
    ALLOCATE EXTENT [INSTANCE int]

    DEALLOCATE UNUSED
    DEALLOCATE UNUSED KEEP int K | M

    [NO]MONITORING USAGE
    UPDATE BLOCK REFERENCES

    NOPARALLEL
    PARALLEL int

    MODIFY PARTITION partition COALESCE
    MODIFY PARTITION partition UNUSABLE
    MODIFY PARTITION partition UPDATE BLOCK REFERENCES
    MODIFY PARTITION partition PARAMETERS ('alter_partition_params')
    MODIFY PARTITION partition partition_options

    partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    RENAME [SUB]PARTITION old_name TO new_name

    DROP PARTITION partition

    SPLIT PARTITION partition AT (value_list)
       [INTO (ptn_descr1, ptn_descr2)] [NOPARALLEL|PARALLEL int]

        ptn_descr:
           PARTITION [partition attrib_options]

    MODIFY DEFAULT ATTRIBUTES [FOR PARTITION partition] attrib_options

    attrib_options:
        TABLESPACE {tablespace|DEFAULT}
        [NO]LOGGING
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        STORAGE storage_clause

    MODIFY SUBPARTITION subpartition UNUSABLE
    MODIFY SUBPARTITION subpartition sub_partition_options

    sub_partition_options:
        ALLOCATE EXTENT [SIZE int K | M]
        ALLOCATE EXTENT [DATAFILE 'filename']
        ALLOCATE EXTENT [INSTANCE int]
        DEALLOCATE UNUSED
        DEALLOCATE UNUSED KEEP int K | M

    REBUILD [rebuild_options]
    REBUILD NOREVERSE [rebuild_options]
    REBUILD REVERSE [rebuild_options]
    REBUILD [SUB]PARTITION partition [rebuild_options]

    rebuild_options:
        ONLINE
        COMPUTE STATISTICS
        TABLESPACE tablespace_name
        NOPARALLEL
        PARALLEL int
        [NO]LOGGING
        COMPRESS int
        NOCOMPRESS
        PCTFREE int
        PCTUSED int
        INITRANS int
        MAXTRANS int
        PARAMETERS ('odci_parameters')
        STORAGE storage_clause

More than one ALLOCATE EXTENT option should be specified in the same clause e.g.
ALLOCATE EXTENT SIZE 200K Datafile 'MyFile.idx'

"We trained hard, but it seemed that every time we were beginning to form up into teams we would be reorganised. I was to learn later in life that we tend to meet any new situation by re-organising, and a wonderful method it can be for creating the illusion of progress while producing confusion, inefficency and demoralisation" - Caius Petronius (A.D. 66)

DROP INDEX

Drop an index.

Syntax:

   DROP INDEX [schema.]index [FORCE]

FORCE can be used to drop domain indexes that are marked as IN_PROGRESS or are reporting errors.

"Anything you build on a large scale or with intense passion invites chaos" - Francis Ford Coppola