What is an Index?
----------------
An index is a performance-tuning method of allowing faster retrieval of records.
An index creates an entry for each value that appears in the indexed columns.
By default, Oracle creates B-tree indexes.
SELECT index_name, index_type
FROM user_indexes;
Create an Index:
----------------
The syntax for creating a index is:
SQl> CREATE INDEX index_name
ON table_name (column1, column2, . column_n)
[ COMPUTE STATISTICS ];
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index.
The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.
Example:
--------
SQl> CREATE INDEX Contract_idx ON Contract (Policynumber);
In this example, we've created an index on the Contract table called Contract_idx.
It consists of only one field - the Policynumber field.
We could also create an index with more than one field as in the example below:
SQl> CREATE INDEX Contract_indx ON Contract (Policynumber,product_code);
The above statement is called as composite index.
B-Tree Index:
------------
By default, the Oracle creates a b_tree index.
In a b-tree, you walk the branches until you get to the node that has the data you want to use.
B-tree indexes are created to decrease the amount of I/O required to find and load a set of data
SQL>create index indx_product on product(productid,product_name);
SQl> select * from product where productid='P01';
above query uses the index scan by using the above index.
SQl> select product_name from product where productid='P01';
above query uses the index scan by using the above in.
It will not go to table scan level because we have created index on two columns productid and product_name.
SQl> select product_name from product where product_name='Product1';
above query does not use the index scan because WHERE clause does not contain the index lead column means
"productid" is the lead column in the index.
Note:--> Never create balance tree(B-Tree) index on low cardinality column.
Bitmap Index:
------------
Oracle's two major index types are Bitmap indexes and B-Tree indexes.
B-Tree indexes are the regular type that OLTP systems make much use of,
and bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses.
Characteristic of Bitmap Indexes:
--------------------------------
For columns with very few unique values (low cardinality) Columns that have low cardinality are good candidates
Tables that have little insert/update are good candidates (static data in warehouse)
Syntax:
------
create bitmap index person_region on person (region);
Row Region North East West South
1 North 1 0 0 0
2 East 0 1 0 0
3 West 0 0 1 0
4 West 0 0 1 0
5 South 0 0 0 1
6 North 1 0 0 0
Advantage of Bitmap Indexes :
----------------------------
The advantages of them are that they have a highly compressed structure,
making them fast to read and their structure makes it possible for the system to combine multiple indexes
together for fast access to the underlying table.
Disadvantage of Bitmap Indexes :
------------------------------
The reason for confining bitmap indexes to data warehouses is that the overhead on maintaining them is enormous.
A modification to a bitmap index requires a great deal more work on behalf of the
system than a modification to a b-tree index.
Unique Index :
------------
Indexes can be unique or non-unique. Unique indexes guarantee
that no two rows of a table have duplicate values in the key column.
Use the CREATE UNIQUE INDEX statement to create a unique index.
The following example creates a unique index:
SQL> CREATE UNIQUE INDEX dept_unique_index ON dept (dname);
The database enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key.
Function-Based Index:
--------------------
In Oracle, you are not restricted to creating indexes on only columns.
You can create function-based indexes.
Example:
--------
CREATE INDEX Index_name ON Tablename (UPPER(columnname));
SQL> CREATE INDEX Index_name ON PRODUCT (UPPER(PRODUCTNAME));
However, to be sure that the Oracle optimizer uses this index when executing your SQL statements,
be sure that UPPER(PRODUCTNAME) does not evaluate to a NULL value.
To ensure this, add UPPER(PRODUCTNAME) IS NOT NULL to your WHERE clause as follows:
The query must be guaranteed not to need any NULL values from the indexed expression,
since NULL values are not stored in indexes.
SELECT PRODUCT_id, UPPER(PRODUCTNAME)
FROM PRODUCT
WHERE UPPER(PRODUCTNAME) IS NOT NULL
ORDER BY UPPER(PRODUCTNAME);
To illustrate a function-based index, consider
the following statement that defines a function-based index (area_index) defined on the function area(geo):
SQL> CREATE INDEX area_index ON rivers (area(geo));
In the following SQL statement, when area(geo) is referenced in the WHERE clause,
the optimizer considers using the index area_index.
SQL> SELECT id, geo, area(geo) FROM rivers WHERE Area(geo) >5000;
Invisible Index:
---------------
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index
that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES
initialization parameter to TRUE at the session or system level.
Making an index invisible is an alternative to making it unusable or dropping it.
SQL> CREATE INDEX emp_ename ON emp(ename) INVISIBLE;
Reverse index:
------------
There is an option to create index entries as reversed, which is called reverse key indexes.
Oracle stores the index entries as their bytes reversed, except rowids
SQL> CREATE INDEX test_indexi ON test_table (a,b,c) REVERSE;
SQL> ALTER INDEX test_index REBUILD NOREVERSE;
Cluster Index:
------------
A cluster is simply a method for storing more then 1 table on the same block. Normally
-- a block contains data for exactly 1 table. In a cluster -- you have data from many
tables sharing the same block.
Collect Statistics on an Index:
------------------------------
If you forgot to collect statistics on the index when you first created it or you want to update the statistics,
you can always use the ALTER INDEX command to collect statistics at a later date.
The syntax for collecting statistics on an index is:
SQl> ALTER INDEX index_name REBUILD COMPUTE STATISTICS;
INDEX REBUILD:
-------------
By using index rebuild we can improve the performance.
SQL> alter index index_name rebuild;
Rename an Index:
---------------
The syntax for renaming an index is:
SQl> ALTER INDEX index_name RENAME TO new_index_name;
Drop an Index:
-------------
The syntax for dropping an index is:
SQl> DROP INDEX index_name;
B-tree cluster indexes : defined specifically for cluster
Reverse key indexes : most useful for Oracle Real Application Clusters applications
Ur's
AmarAlam
0 comments:
Post a Comment