INDEXES
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);
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;
DATA MODEL
Ø ALL_INDEXES
Ø DBA_INDEXES
Ø USER_INDEXES
Ø ALL_IND-COLUMNS
Ø DBA-IND_COLUMNS
Ø USER_IND_COLUMNS
Ø ALL_PART_INDEXES
Ø DBA_PART_INDEXES
Ø USER_PART_INDEXES
Ø V$OBJECT_USAGE
No comments:
Post a Comment