INDEX IN ORACLE
What is an index:-
Indexes are special lookup tables that
the database search engine can use to speed up data retrieval. Simply put, an
index is a pointer to data in a table. An index in a database is very similar
to an index in the back of a book.
For
example, if you want to reference all pages in a book that discusses a certain
topic, you first refer to the index, which lists all the topics alphabetically
and are then referred to one or more specific page numbers.
` An index helps to speed up SELECT
queries and WHERE clauses, but it slows down data input, with the UPDATE and
the INSERT statements. Indexes can be created or dropped with no effect on the
data.
Creating an index involves the CREATE
INDEX statement, which allows you to name the index, to specify the table and
which column or columns to index, and to indicate whether the index is in an
ascending or descending order.
Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.
Why we need to use index:-
Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.
What is the use of Index:-
·
It issused by most of Relational Database
Management Systems such as MySQL, SQL Server and Oracle
·
It can improve the performance of most types of SQL
queries, rather than specific types
·
Indexes are used in Oracle to provide quick access
to rows in a table. Indexes provide faster access to data for operations that
return a small portion of a table's rows.
· Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries.
How to find index being used:-
create table and insert records
create
table india(id int,name varchar2(20));
Table
created.
SQL>
insert into india values (1,'tn');
1
row created.
SQL> insert into india values (2,'kl');
1
row created.
SQL> insert into india select * from india;
SQL> select count(*)from india;
COUNT(*)
----------
512
SQL>
create index on table india
SQL> create index st_ind on india (name);
Index created.
check index usage its shows no usage on index
SQL> select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
------------------------------
---------------------------------
ST_IND INDIA NO
do any operation like select insert update on table
SQL> select count(*)from india where name='tn';
COUNT(*)
----------
256
now check index usage in object usage views its shows yes
SQL>
select index_name, table_name, used from v$object_usage;
INDEX_NAME TABLE_NAME USE
------------------------------
---------------------------------
ST_IND INDIA YES
SQL>
How to find index size:-
Dba segment view used to identified the index size mention the segment name/index name to get size of the index
SQL> select sum(bytes)/1024/1024 as "Index Size (MB)" from dba_segments where segment_name='ST_IND';
Index
Size (MB)
---------------
.125
SQL>
How to create index:-
Create index as syntax after that we give index name and mention the table and column name
SQL> create index st_ind on india (name);
Index created.
Types of
index:-
(1)B-Tree
(2)Compressed B-Tree
(3)Bitmap
(4)Function-Based
(5)Reverse Key (RKI)
(6)Index organized table (IOT).
B –
Tree Index:
B-Tree
Indexes (balanced tree) are the most common type of index.
B-Tree
index stored the ROWID and the index key value in a tree structure.
When
creating an index, a ROOT block is created, then BRANCH blocks are created and
finally LEAF blocks.
Each
branch holds the range of data its leaf blocks hold, and each root holds the
range of data its branches hold:
B-Tree indexes are most useful on columns that appear in the where clause (SELECT … WHERE EMPNO=1).
Syntax
CREATE <UNIQUE|NON
UNIQUE> INDEX <index_name>
ON <table_name>
(<column_name>,<column_name>…)
TABLESPACE <tablespace_name>;
Example
Create index vbt.btree_idx on vbt.btree( name) Tablespace users;
Compressed B-Tree:
Compressed B-Tree
Indexes are built on large tables, in a data warehouse environment. In this
type of index, duplicate occurrences of the same value are eliminated, thus
reducing the amount of storage space, the index requires.
In a compressed
B-Tree index, for each key value, a list of ROWIDs are kept
Specifying the
COMPRESS keyword when creating an index (CREATE INDEX … COMPRESS) will create a
compressed B-Tree index.
A regular B-Tree index can be rebuilt using the COMPRESS keyword to compress it.
Syntax:
CREATE <UNIQUE|NON
UNIQUE> INDEX <index_name>
ON <table_name>
(<column_name>,<column_name>…)
PCTFREE <integer>
TABLESPACE
<tablespace_name>
Compress <column number>
Example :
SQL> create index
vbt.cbtree_idx on vbt.cbtree (id)
tablespace users compress 1;
Index created.
Bitmap Indexes:
Bitmap Indexes are
most appropriate on low distinct cardinality data (as opposed to B-Tree
indexes).
This type of index,
creates a binary map of all index values, and store that map in the index
blocks, this means that the index will require less space than B-Tree index.
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
When there are
bitmap indexes on tables then updates will take out full table locks.So, Bitmap
index is useful on large columns with low-DML(infrequent updates) activity or
read only tables. This is reason you often find bitmap indexes are extensively
used in the data warehouse environment(DWH).
Bitmap Index
structure contain a map of bits which indicate the value in the column, for
example, for the name column, the index block will hold the starting id, the
ending id and the bit map:
Bitmap indexes are
very useful when created on columns with low cardinality, used with the AND
& OR operator in the query condition:
Syntax:
CREATE BITMAP INDEX <index_name>
ON <table_name>
(<column_name>,<column_name>…)
PCTFREE <integer>
TABLESPACE <tablespace_name>
Example
:
SQL> CREATE BITMAP INDEX vbt.bmap_idx ON vbt.bitmap(name);
Index created.
SQL> SELECT COUNT(*) FROM vbt.bitmap WHERE NAME='node';
COUNT(*)
----------
1
Function Based Indexes:
Function-Based
Indexes are indexes created on columns that a function is usually applied on.
When using a function on an indexed column, the index is ignored, therefore a function-based index is very useful for these operation
SQL> CREATE INDEX vbt.fun_idx on vbt.fun(UPPER(name));
Index created.
SQL> SELECT * FROM vbt.fun WHERE UPPER(name) like 'node';
no rows selected
SQL>
SELECT * FROM vbt.fun WHERE UPPER(name) like 'NODE';
ID NAME
----------
--------------------
2 node
Reverse-Key Indexes:
They are
special types of B-Tree indexes and are very useful when created on columns
contain sequential numbers.
When using a
regular B-Tree, the index will grow to have many branches and perhaps several
levels, thus causing performance degradation, the RKI solve the problem by
reversing the bytes of each column key and indexing the new data.
This method
distributes the data evenly in the index. Creating a RKI is done using the
REVERSE keyword:
CREATE INDEX … ON … REVERSE;
Example
:
SQL> create index vbt.rev_idx on vbt.rev(id,name) reverse;
Index created.
SQL>
select * from vbt.rev;
ID NAME
----------
--------------------
1 parent
2 node
No comments:
Post a Comment