Type of Indexes

Oracle8i also allows you to rebuild your indexes while online. In the past, creating or rebuilding the index required a full lock on the table. On a large table, this could mean that an application is unusable for several hours.
Now, however, Oracle allows you to create or rebuild the index while users can still perform the full range of data processes. To do this, Oracle creates the index structure before populating it. While populating, all changes to the table are recorded in a journal table. As the index is completed, the journal table changes are then built in. 
Brief table locks are made while the index structure is made and the journal table bought into the index.

To build an index on line, you use the following syntax:
CREATE INDEX  my_index ON my_table (my_field) ONLINE;

The importance of PCTFREE
For a B-Tree index, PCTFREE determines the extent of a leaf-split. In other words, PCTFREE indicates the amount of free-space within a block for “future updates”. However in the case of an index (unlike a table), these updates do not make sense, since an update anyway forces a delete and subsequent insert. In the case of indexes, PCTFREE plays a role mostly at the time of index-creation. A non-zero value specifies the block-splitting ratio. If PCTFREE is set to 20, during index-creation, up to 80% of the leaf may contain key-information.
However the remaining 20% should be available for future inserts of key-information into that leaf-block. This ensures that during run-time inserts, there is minimal overhead in terms of incurring leaf-block splits.
Even though a higher PCTFREE may cause index-creation time to increase, it prevents the performance-hit from being deferred to actual usage time. So, the end-user, who is waiting for a row to be inserted, does not incur the performance penalty associated with a leaf-block split.
Based on this information, the following points emerge:
· PCTFREE for an index is used primarily during creation. It is ignored during actual usage.
· Specify a higher PCTFREE for OLTP applications, if the table is a popular one, incurring a lot of DML changes (via interactive user-screens);
· If the index-creation time is critical specify a lower PCTFREE. This will pack in more rows per leaf-block, thereby avoiding the need for further splitting at creation-time. This is of paramount significance to shops that have “24x7” availability requirements. Index-creation in most cases requires considerable downtime (especially if the table is a multi-million row table). Lesser the amount of index-creation time, smaller can be the maintenance-window. I have seen this tiny, often unnoticed parameter save around 20% of index-creation time. At a high-availability site, an index on a table containing around 11 million rows took me about 80 minutes to build using PCTFREE of 30 and a parallel degree of 4. The same index on the same table, with 13.5 million rows took me around 90 minutes to create with a PCTFREE of 0 (without any hardware/software enhancements). Nologging (minimal redo) was on during both creations.
· For any column where the values are constantly increasing, it is probably a good idea to set a very low PCTFREE (even, zero). This is because only the rightmost leaf-block will always be inserted into. This will make the tree grow towards the right. The leftmost leaves would remain static. So, there is no sense in leaving any part of those blocks empty with a non-zero PCTFREE.

1- Partitioned Indexes
Like tables, indexes can also be partitioned; but with indexes you have a more options because the underlying table might or might not also be partitioned. The objective of this type of index is to separate the index into smaller partitions, just as we do now for a database table. There are essentially two different types of partitioned indexes available: 
•	Global indexes--These are created in a manner different from the underlying partitioning of the table that is indexed. 
•	Local indexes--These are partitioned in the same manner as the underlying table partitioning. 

Global Indexes
To create a global partitioned index, use the CREATE INDEX parameter GLOBAL. This specifies that the index will be a global index. Further partitioning of the index is accomplished by using the following parameters: 
•	GLOBAL--This parameter specifies a global partitioned index. 
•	PARTITION part_name--This parameter is used to identify the partition. If you do not specify the partition name, a default name will be provided. It is not usually necessary to provide the partition name. 
•	VALUES LESS THAT--This parameter is used to specify the range that is allocated for that particular partition in the same way as the partition was specified in the CREATE TABLE statement (discussed yesterday). 

NOTE: The last partition should contain the keyword MAXVALUE for its range. 

Creating a partitioned index.
CREATE INDEX "ETW".dogs_ix1
ON  DOGS (ID)
PARTITION BY RANGE (ID)
PARTITION pt1 VALUES LESS THAN (`1000') TABLESPACE ts1,
PARTITION pt2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2);

ANLYSIS:
This create two partitions, the first holding values of ID that are less than 1,000, the second holding the remaining values of ID. If you do not specify the partition name, as is the case here, a default name will be provided. 
While this does offer a great deal of flexibility, it is not without cost.  Since global partitioned indexes are not directly related to any single table partition, operations that affect any partition in the table can render all global partitioned indexes unusable.  Specifically the operations:

ADD (HASH) - COALESCE (HASH) – DROP – EXCHANGE – MERGE  - MOVE  -SPLIT - TRUNCATE

Local Indexes
In contrast to the global index, a local partitioned index is individually created on each partition. If you specify a local partitioned index, Oracle automatically maintains the index's partitioning along with that of the underlying table.
Local partitioned indexes are created through the use of the LOCAL parameter with the CREATE INDEX statement. It is unnecessary to provide partitioning information because the underlying table partitioning will be used. A local index can be created with the following syntax:

CREATE INDEX "ETW".dogs_ix1
ON  DOGS (ID)
LOCAL;

Because the index is local, all partition changes to the table will be automatically reflected on the index partitions as well.
Local partitioned indexes have some inherent advantages that are similar to the advantages you get from partitioned tables. These advantages include the following: 
•	Because the index exists entirely on one partition, any maintenance operations affect only that one partition. 
•	The Oracle optimizer can use the local index to generate better query plans based on the fact that a local index is used. 
•	If a partition is lost and must be recovered, only the data and index for that particular partition needs to be recovered. With a global index, the entire index would need recovery. 
As you can see, there are many advantages of using both global and local partitioned indexes. 

2- Index-Only Tables
Many systems contain several small tables (1 to 3 columns) where all of the elements form the primary key. This is typically the case of tables created to physically implement conceptual relations of the type O,n « O,n. However, there exists an extremely efficient way to create such tables, by using a B*-Tree structure. In ORACLE 8, an index-organized table, that is a table with the same physical structure as an index, allows us to do exactly that. In an index-organized table, the database engine will place the data values in a « table » segment, but with a B*-tree structure.
An index-only table is a schema object introduced in Oracle8. An index-only table is similar to an index, but whereas an index contains the primary key value and a ROWID pointing to where the data is kept, the index-only table stores the column data in the leaf block of the index.
Because the leaf blocks of the Oracle index are traditionally very small and tightly packed, there can be some drawbacks to having large rows stored there. Oracle has developed a way to compensate for this: If rows become too large (by a set threshold), the row data is stored in an overflow area as specified in the CREATE TABLE statement. This creates storage more like the traditional index and table relationship.
An index-only table contains the same structure as the Oracle B*-tree index. Only the leaf blocks have changed. Index-only tables have many of the attributes of both indexes and tables, but there are a few exceptions: 
•	Because it is part index and part table, no other indexes can be added to the index-only table. 
•	The UNIQUE constraint is not allowed on an index-only table. 
•	A trigger can be added to the index-only table. 
•	An index-only table cannot be stored in a cluster. 
•	Replication is not supported at this time. 
As you can see, there are some restrictions on index-only tables, but there is also a great deal of benefits. 
When performing inserts, there is a performance improvement, since only a single segment needs to be updated, which is the table itself. Data retrieval is also faster since the optimizer processes the table just as if it were an index. Furthermore, one read operation is saved since ORACLE doesn't have to read the index prior to performing the read in the table. 

When to Use Index-Only Tables
Index-only tables are very useful whenever data is always accessed via the primary key index. If this is the case with your data, the index-only table will cut down on the space used by both the index and the table (by combining them) and improve performance. Performance is improved because, by the time the ROWID would have been retrieved, you have the data.
Tables that are not accessed via the primary key value are not good candidates for index-only tables. Also, tables whose primary key values are updated and tables that have frequent insertions are not good candidates for index-only tables. 

How to Create Index-Only Tables
Index-only tables are created with the CREATE TABLE command; the ORGANIZATION INDEXED qualifier is used to identify the table as index-only. The following qualifiers are used in creating index-only tables: 
•	ORGANIZATION INDEXED--This qualifier specifies an index-only table organization. 
•	OVERFLOW TABLESPACE ts_name--This qualifier specifies the overflow tablespace name. 
•	PCTTHRESHOLD threshold--This qualifier specifies the percent of a block that a row must be larger than in order to be offloaded to the overflow tablespace. 

Creating an index-only table with the CREATE TABLE command.
CREATE TABLE "ETW".DOGS (
ID NUMBER,
NAME VARCHAR2(40),
OWNER_ID NUMBER,
BREED_ID NUMBER,
RANK NUMBER NULL,
NOTES VARCHAR2(80)
PRIMARY KEY(ID) )
ORGANIZATION INDEXED
PCTTHRESHOLD 40
OVERFLOW TABLESPACE "DOGS2"
TABLESPACE "DOGS"; 

ANLYSIS:
This specifies that the index-only tablespace be created on the DOGS tablespace, whereas the overflow tablespace used is DOGS2. 

NOTE: It is necessary to specify the PRIMARY KEY value when creating an index-only table. This is the value on which the index is created. 

Unfortunately, there are also some disadvantages to using index-organized tables. For example, it is not possible to create additional indexes since the occurrences of this type of table do not have a ROWID. It is also impossible to add new columns or to modify existing columns. The message « ORA-25182 feature not currently available for index-organized table » is used to alert us to these restrictions. Another factor to consider is the case where updating key values is allowed. ORACLE
must delete the old record and do an insert based on the new key value, that is relocate a leaf of the B*-tree to another location in the B*-tree. The net result is 2 SQL operations. Therefore, one should weight the advantages and disadvantages before choosing this type of table, even though it is relatively easy to convert an index-organized table back to a conventional format using create table as and then doing an Import/Export to that table.

3- Bitmap Indexes
Another type of index available the bitmap index. With the traditional index you saw earlier, Oracle uses a B*-tree method to traverse the index to find the leaf block. With a bitmap index, a bitmap of ROWIDs is kept; this bitmap indicates which rows correspond to the index item. If the bit is set, this indicates that the corresponding row contains the key value; if the bit is not set, the opposite is true.
As you can probably tell, bitmap indexes can probably be quite useful under the right circumstances, and useless overhead otherwise. 

When to Use Bitmap Indexes
As you can probably guess, the bitmap index works well on items with low cardinality. Low cardinality means there is a small amount of variance in the possible values stored in that column. For example, the column representing the sex of the dog is said to have low cardinality because only two values are possible. Other column types that might have low cardinality include 
•	Marital status 
•	Account status (good or bad) 
•	Sales region (if there are only a few) 
•	Rank (if there are only a few) 
•	Special notes (whether there is a note) 
With columns that have low cardinality, the bitmap index can greatly improve performance. Columns with high cardinality are not candidates for bitmap indexes. 
Locking issues affect data manipulation operations in Oracle. As a result, bitmapped indexes are not appropriate for OLTP applications that have a high level of concurrent insert, update and delete operations. Concurrency is usually not an issue in a data-warehousing environment where the data is maintained by bulk loads, inserts and updates. 
In addition, bitmapped index maintenance is deferred until the end of the bulk DML operation. If 100 rows are inserted into a table, the inserted rows are placed into a sort buffer and the updates of all 100-index entries are applied as a group. As a result, bitmapped indexes are appropriate for most decision support applications (even those that have bulk updates applied on a regular basis). 
Mass updates, inserts and delete will run faster if you drop the bitmapped indexes, execute the DML and recreate the bitmapped indexes when the DML completes. Run timings using the straight DML and compare it to the total time consumed by the drop bitmapped index/execute DML/recreate bitmapped index process.

How to Create Bitmapped Indexes
A bitmap index is created with the CREATE INDEX command with the BITMAP qualifier. For example, the following will create a bitmap index:
CREATE BITMAP INDEX
To create a bitmap index on the SEX field in the DOGS table, you can use the following syntax:
CREATE BITMAP INDEX "ETW".dogs_bx1
ON  DOGS (SEX);
This simple statement will create the bitmap index on the column specified. At this time, bitmap indexes cannot be created with the graphical tools. 

4- Reverse Key Indexes
When a column that contains an incrementally increasing value (like those provided by sequences) is indexed, there is a very poor distribution of that value across the B-tree, and hot spots can be introduced into the index.  Reverse-key indexes can help eliminate these hot spots by providing a much wider distribution of values than would occur in a regular B-tree index. In this situation, simultaneous inserts requested by the users are done in the same leaf of the index. This will generate contention on the same Oracle block buffer. This problem is exacerbated when several tasks are initiated in parallel, all processing updates against the database. Another problem is the phenomena of « sliding » when the system deletes older numbers and inserts new ones. In this case, the index becomes increasingly large as the space occupied by the deleted index entries never gets reused.
The answer to this situation is the reverse-key index whereby different index leafs is used, even if the key sequence is chronological. 

How to Create Reverse Key Indexes
CREATE INDEX my_index ON my_table (my_fields) REVERSE;

To make an existing index a reverse index use:
ALTER INDEX index_name REBUILD REVERSE; 

Substitute no reverse if you want to rebuild you index as a non-reversed one. 
No options are without a certain cost.  While reverse-key indexes can help in the distribution of data across an index, this very strength can be a weakness.  Since the actual (non-reversed) values of the index are not stored sequentially in the index, index range-scans cannot be performed.  Only fetch-by-key and full index scans can be performed on a reverse-key index.  Therefore, it is not a good idea to build a reverse-key index on a column that might use range-scans.

5- Function Based Indexes
Function based indexes are really going to improve the performance of all those queries which use functions such as SUM, AVG in the select or when the where clause includes some kind of other function such as some mathematical calculation on two columns. 
Suppose you have an application that routinely selects the sum of the two columns SALARY and BONUS from the PAYMENTS table. In the past, such a select may have been fairly slow, because of the calculation being performed on the select. Now, however, the calculation is stored as part of the index, making the overall selection process that much quicker.

How to Create Function Based Indexes
CREATE INDEX my_ind ON payments (salary + bonus);

So, this query would now utilize the index:

SELECT employee_id
FROM payments
WHERE (salary + bonus) > 1000;

You need to enable function-based indexes before you can use them, as follows:

ALTER SESSION SET QUERY_REWRITE_ENABLED = true
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = trusted

Or
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (or greater)

Also, to use a function-based index:
	The must be analyzed before it can be used
	The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

6- Descending Indexes
	Descending indexes are a special type of function-based index, in which index entries are sorted in descending order.  Using the DESC clause does this. When creating an index, as in the example below:
	create index ix_desc
	on phonebook2(zip desc);
	
There are a couple special considerations when using descending indexes:
•	Descending indexes cannot be used on reverse indexes
•	The DESC option is ignored if used in the creation of bitmap indexes
•	The COMPATIBLE initialization parameter must be set to 8.1.0