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.
Some Imp Notes:
1. Bitmap indexes are most appropriate for columns having low
distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption
is not completely accurate, however. In reality, a bitmap index is always
advisable for systems in which data is not frequently updated by many
concurrent systems. In fact, as I'll demonstrate here, a bitmap index on a
column with 100-percent unique values (a column candidate for primary key) is
as efficient as a B-tree index.
2. When to Create an Index
3. You should create an index if:
4.
A column contains a wide range of values
5.
A column
contains a large number of null values
6.
One or more
columns are frequently used together in a WHERE clause or a join condition
7. The table is large and most queries are expected to retrieve
less than 2 to 4 percent of the rows
By default if u create
index that is nothing but b-tree index.
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);
HOW A TEXT QEURY WORKS?
When a function such as CONTAINS or CATSEARCH is used in query, the
text portion of the query is processed by oracle text. The remainder of the
query is processed just like a regular query within the database. The result of
the text query processing and the regular query processing are merged to return
a single set of records to the user.
SEARCHING FOR AN EXACT MATCH
OF A WORD
The following queries
will search for a word called ‘prperty’ whose score is greater than zero.
SQL> select * from books where
contains(info, ‘property’) > 0;
SQL> select * from books where
catsearch(info, ‘property’, null) > 0;
Suppose if you want to
know the score of the ‘property’ in each book, if score values for individual
searches range from 0 to 10 for each occurrence of the string within the text
then use the score function.
SQL> select title, score(10)
from books where contains(info, ‘property’, 10) > 0;
SEARCHING FOR AN EXACT MATCH
OF MULTIPLE WORDS
The following queries
will search for two words.
SQL> select * from books where
contains(info, ‘property AND harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property AND harvests’, null) > 0;
Instead of using AND you could hae used an
ampersand(&). Before using this method, set define off so the &
character will not be seen as part of a variable name.
SQL> set define off
SQL> select * from books where
contains(info, ‘property & harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property harvests’,
null) > 0;
The following queries
will search for more than two words.
SQL> select * from books where
contains(info, ‘property AND harvests AND workers’) > 0;
SQL> select * from books where
catsearch(info, ‘property harvests workers’, null) > 0;
The following queries
will search for either of the two words.
SQL> select * from books where
contains(info, ‘property OR harvests’) > 0;
Instead of OR you can use a vertical
line (|).
SQL> select * from books where
contains(info, ‘property | harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property | harvests’, null) > 0;
In the following queries
the ACCUM(accumulate) operator
adds together the scores of the individual searches and compares the
accumulated score to the threshold value.
SQL> select * from books where
contains(info, ‘property ACCUM harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property ACCUM harvests’, null) > 0;
Instead of OR you can use a comma(,).
SQL> select * from books where
contains(info, ‘property , harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property , harvests’, null) > 0;
In the following queries
the MINUS operator subtracts the
score of the second term’s search from the score of the first term’s search.
SQL> select * from books where
contains(info, ‘property MINUS harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property NOT harvests’, null) > 0;
Instead of MINUS you can use – and
instead of NOT you can use ~.
SQL> select * from books where
contains(info, ‘property - harvests’) > 0;
SQL> select * from books where
catsearch(info, ‘property ~ harvests’, null) > 0;
SEARCHING FOR AN EXACT MATCH
OF A PHRASE
The following queries
will search for the phrase. If the search phrase includes a reserved word
within oracle text, the you must use curly braces ({}) to enclose text.
SQL> select * from books where
contains(info, ‘transactions {and} finances’) > 0;
SQL> select * from books where
catsearch(info, ‘transactions {and} finances’, null) > 0;
You can enclose the
entire phrase within curly braces, in which case any reserved words within the
phrase will be treated as part of the search criteria.
SQL> select * from books where
contains(info, ‘{transactions and finances}’) > 0;
SQL> select * from books where
catsearch(info, ‘{transactions and finances}’, null) > 0;
SEARCHING FOR WORDS THAT ARE
NEAR EACH OTHER
The following queries
will search for the words that are in between the search terms.
SQL> select * from books where
contains(info, ‘workers NEAR harvests’) > 0;
Instead of NEAR you can use ;.
SQL> select * from books where
contains(info, ‘workers ; harvests’) > 0;
In CONTEXT index queries, you can
specify the maximum number of words between the search terms.
SQL> select * from books where
contains(info, ‘NEAR((workers, harvests),10)’ > 0;
USING WILDCARDS DURING
SEARCHES
You can use wildcards to
expand the list of valid search terms used during your query. Just as in
regular text-string wildcard processing, two wildcards are available.
% - percent
sign; multiple-character wildcard
_ - underscore;
single-character wildcard
SQL> select * from books where
contains(info, ‘worker%’) > 0;
SQL> select * from books where
contains(info, ‘work___’) > 0;
SEARCHING FOR WORDS THAT
SHARE THE SAME STEM
Rather than using
wildcards, you can use stem-expansion capabilities to expand the list of text
strings. Given the ‘stem’ of a word, oracle will expand the list of words to
search for to include all words having the same stem. Sample expansions are
show here.
Play - plays
playing played playful
SQL> select * from books where
contains(info, ‘$manage’) > 0;
SEARCHING FOR FUZZY MATCHES
A fuzzy match expands
the specified search term to include words that are spelled similarly but that
do not necessarily have the same word stem. Fuzzy matches are most helpful when
the text contains misspellings. The misspellings can be either in the searched
text or in the search string specified by the user during the query.
The following queries
will not return anything because its search does not contain the word
‘hardest’.
SQL> select * from books where
contains(info, ‘hardest’) > 0;
It does, however,
contains the word ‘harvest’. A fuzzy match will return the books containing the
word ‘harvest’ even though ‘harvest’ has a different word stem thant the word
used as the search term.
To use a fuzzy match,
precede the search term with a question mark, with no space between the
question mark and the beginning of the search term.
SQL> select * from books where
contains(info, ‘?hardest’) > 0;
SEARCHING FOR WORDS THAT
SOUND LIKE OTHER WORDS
SOUNDEX, expands search
terms based on how the word sounds. The SOUNDEX expansion method uses the same
text-matching logic available via the SOUNDEX function in SQL.
To use the SOUNDEX
option, you must precede the search term with an exclamation mark(!).
SQL> select * from books where
contains(info, ‘!grate’) > 0;
INDEX SYNCHRONIZATION
When using CONTEXT indexes, you need to
manage the text index contents; the text indexes are not updated when the base
table is updated. When the table was updated, its text index is out of sync
with the base table. To sync of the index, execute the SYNC_INDEX procedure of the CTX_DDL package.
SQL> exec CTX_DDL.SYNC_INDEX(‘book_index’);
INDEX SETS
Historically, problems
with queries of text indexes have occurred when other criteria are used
alongside text searches as part of the where clause. To improve the mixed query
capability, oracle features index sets. The indexes within the index set may be
structured relational columns or on text columns.
To create an index set,
use the CTX_DDL package to create the index set and add indexes to it. When you
create a text index, you can then specify the index set it belongs to.
SQL> exec CTX_DDL.CREATE_INDEX_SET(‘books_index_set’);
The add non-text
indexes.
SQL> exec CTX_DDL.ADD_INDEX(‘books_index_set’,
‘title_index’);
Now create a CTXCAT text index. Specify
ctxsys.ctxcat as the index type, and list the index set in the parameters
clause.
SQL> create index book_index
on books(info) indextype is ctxsys.ctxcat parameters(‘index set
books_index_set’);
INDEX-ORGANIZED TABLE
An index-organized table
keeps its data sorted according to the primary key column values for the table.
Index-organized tables store their data as if the entire table was stored in an
index.
An index-organized table
allows you to store the entire table’s data in an index.
Ex:
SQL> create table student (sno number(2),sname varchar(10),smarks
number(3) constraint
pk primary key(sno) organization
index;
PARTITION INDEX
Similar to partitioning
tables, oracle allows you to partition indexes too. Like table partitions, index partitions could be in different
tablespaces.
LOCAL INDEXES
Ø Local keyword tells
oracle to create a separte index for each partition.
Ø In the local prefixed
index the partition key is specified on the left prefix. When the underlying
table is partitioned baes on, say two columns then the index can be prefixed on
the first column specified.
Ø Local prefixed indexes
can be unique or non unique.
Ø Local indexes may be
easier to manage than global indexes.
Ex:
SQL> create index stud_index on student(sno) local;
GLOBAL INDEXES
Ø A global index may
contain values from multiple partitions.
Ø An index is global
prefixed if it is partitioned on the left prefix of the index columns.
Ø The global clause allows
you to create a non-partitioned index.
Ø Global indexes may
perform uniqueness checks faster than local (partitioned) indexes.
Ø You cannot create global
indexes for hash partitions or subpartitions.
Ex:
SQL> create index stud_index on student(sno) global;
Similar to table
partitions, it is possible to move them from one device to another. But unlike
table partitions, movement of index partitions requires individual
reconstruction of the index or each partition (only in the case of global
index).
Ex:
SQL> alter index stud_ind rebuild partition p2
Ø Index partitions cannot
be dropped manually.
Ø They are dropped
implicitly when the data they refer to is dropped from the partitioned table.
MONITORING USE OF INDEXES
Once you turned on the
monitoring the use of indexes, then we can check whether the table is hitting
the index or not.
To monitor the use of
index use the follwing syntax.
Syntax:
alter index index_name monitoring usage;
then check for the
details in V$OBJECT_USAGE view.
If you want to stop
monitoring use the following.
Syntax:
alter index index_name nomonitoring usage;
