Friday, October 28, 2011
ORACLE SQL TUTORIAL: INDEX PART 1
Index is typically a listing of keywords accompanied by the
location of information on a subject. We can create indexes explicitly to speed
up SQL statement execution on a table. The index points directly to the
location of the rows containing the value.
WHY INDEXES?
Indexes are most useful on larger tables, on columns that are
likely to appear in where clauses as simple equality.
TYPES
Ø Unique index
Ø Non-unique index
Ø Btree index
Ø Bitmap index
Ø Composite index
Ø Reverse key index
Ø Function-based index
Ø Descending index
Ø Domain index
Ø Object index
Ø Cluster index
Ø Text index
Ø Index organized table
Ø Partition index
v Local index
ü Local prefixed
ü Local non-prefixed
v Global index
ü Global prefixed
ü
Global non-prefixed
UNIQUE INDEX
Unique indexes guarantee that no two rows of a table have
duplicate values in the columns that define the index. Unique index is
automatically created when primary key or unique constraint is created.
Ex:
SQL> create unique index stud_ind on student(sno);
NON-UNIQUE INDEX
Non-Unique indexes do not impose the above restriction on the
column values.
Ex:
SQL> create index stud_ind on student(sno);
BTREE INDEX or ASCENDING INDEX
The default type of index used in an oracle database is the btree
index. A btree index is designed to provide both rapid access to individual
rows and quick access to groups of rows within a range. The btree index does
this by performing a succession of value comparisons. Each comparison
eliminates many of the rows.
Ex:
SQL> create index stud_ind on student(sno);
BITMAP INDEX
This can be used for low cardinality columns: that is columns in
which the number of distinct values is snall when compared to the number of the
rows in the table.
Ex:
SQL> create bitmap index stud_ind on student(sex);
COMPOSITE INDEX
A composite index also called a concatenated index is an index
created on multiple columns of a table. Columns in a composite index can appear
in any order and need not be adjacent columns of the table.
Ex:
SQL> create bitmap index stud_ind on student(sno,
sname);
REVERSE KEY INDEX
A reverse key index when compared to standard index, reverses each
byte of the column being indexed while keeping the column order. When the
column is indexed in reverse mode then the column values will be stored in an
index in different blocks as the starting value differs. Such an arrangement
can help avoid performance degradations in indexes where modifications to the
index are concentrated on a small set of blocks.
Ex:
SQL> create index stud_ind on student(sno, reverse);
We can rebuild a reverse key index into normal index using the
noreverse keyword.
Ex:
SQL> alter index stud_ind rebuild noreverse;
FUNCTION BASED INDEX
This will use result of the function as key instead of using
column as the value for the key.
Ex:
SQL> create index stud_ind on
student(upper(sname));
DESCENDING INDEX
The order used by B-tree indexes has been ascending order. You can
categorize data in B-tree index in descending order as well. This feature can
be useful in applications where sorting operations are required.
Ex:
SQL> create index stud_ind on student(sno desc);
TEXT INDEX
Querying text is different from querying data because words have
shades of meaning, relationships to other words, and opposites. You may want to
search for words that are near each other, or words that are related to thers.
These queries would be extremely difficult if all you had available was the
standard relational operators. By extending SQL to include text indexes, oracle
text permits you to ask very complex questions about the text.
To use oracle text, you need to create a text index on the column in which the text is stored. Text index is
a collection of tables and indexes that store information about the text stored
in the column.
TYPES
There are several different types of indexes available in oracle
9i. The first, CONTEXT is supported in oracle 8i as well as oracle 9i. As of
oracle 9i, you can use the CTXCAT text index fo further enhance your text index
management and query capabilities.
Ø CONTEXT
Ø CTXCAT
Ø CTXRULE
The CTXCAT index type supports the transactional synchronization
of data between the base table and its text index. With CONTEXT indexes, you
need to manually tell oracle to update the values in the text index after data
changes in base table. CTXCAT index types do not generate score values during
the text queries.
HOW TO CREATE TEXT INDEX?
You can create a text index via a special version of the create
index comman. For context index, specify the ctxsys.context index type and for
ctxcat index, specify the ctxsys.ctxcat index type.
Ex:
Suppose you have a table called BOOKS with the following columns
Title, Author, Info.
SQL> create index book_index on books(info)
indextype is ctxsys.context;
SQL> create index book_index on books(info) indextype
is ctxsys.ctxcat;
TEXT QUERIES
Once a text index is created on the info column of BOOKS table,
text-searching capabilities increase dynamically.
CONTAINS & CATSEARCH
CONTAINS function takes two parameters – the column name and the
search string.
Syntax:
Contains(indexed_column,
search_str);
If you create a CTXCAT index, use the CATSEARCH function in place
of CONTAINS. CATSEARCH takes three parameters – the column name, the search
string and the index set.
Syntax:
Contains(indexed_column,
search_str, index_set);
PART 2: http://www.shareoracleapps.com/2011/10/oracle-sql-tutorial-index-overview-part.html
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “ORACLE SQL TUTORIAL: INDEX PART 1”
Post a Comment