Loading

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

1 Responses to “ORACLE SQL TUTORIAL: INDEX PART 1”

Sridevi K said...
October 8, 2016 at 12:57 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.