Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL 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
Local index
Local prefixed
Local non-prefixed
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
Share this article :

3 comments:

  1. Hi There,

    In debt to you for making my learning on the SQL Indexes area so hassle-free! I lay my faith on your writings.

    I've the attached table structure & data.

    From which I have a list of screen Ids from tbl_screen, and would like to get a list of users assigned, their privilege & access
    e.g
    Get the digits from the ID Student.mod.456.std in tbl_screen
    this digit is prefixed with @ and added in tbl_user field user_screen @456
    then get which rights is attached to this users (tbl_user & tbl_user_rights id)
    then get the action priviledge from tbl_user_access

    Any idea.. how to build the select statement pls?
    Very useful post !everyone should learn and use it during their learning path.

    Kind Regards,
    Preethi

    ReplyDelete
  2. Hi Buddy,


    I genuinely do look forward for the time where you post some new write ups. Your blog make me feel so educated! Continue soaring and writing please.


    i created one alert that picks employees that have probation end date before one month and mail is sent to supervisor and it works fine but a new requirement is that when the probation initiation begins it should pick from hr_api_transactions table and stop the alert from sending afte probation confirmation please help. below is the query

    SELECT papf.employee_number,
    papf.full_name employee_name,
    pjt.NAME job,
    pp.NAME position_name,
    papf.start_date,
    paaf.date_probation_end,
    paaf.probation_period, notice_period,
    DECODE (probation_unit,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) probation_unit,
    DECODE (notice_period_uom,
    'D', 'DAYS',
    'W', 'WEEKS',
    'H', 'HOURS',
    'M', 'MONTHS',
    'Y', 'YEARS'
    ) notice_period_uom,
    hl_freq.meaning, paaf.effective_start_date, paaf.effective_end_date,
    mg.full_name supervisor_name, mg.email_address
    supervisor_email
    FROM per_all_assignments_f paaf,
    per_jobs_tl pjt,
    per_grades_tl pgt,
    per_all_people_f papf,
    hr_lookups hl_freq,
    per_all_positions pp,
    per_all_people_f mg
    WHERE 1 = 1
    AND paaf.person_id = papf.person_id
    AND paaf.business_group_id = papf.business_group_id
    AND pgt.grade_id(+) = paaf.grade_id
    AND pjt.job_id = paaf.job_id
    AND paaf.frequency = hl_freq.lookup_code
    AND papf.business_group_id = pp.business_group_id
    AND pp.position_id = paaf.position_id
    AND paaf.person_id = papf.person_id
    AND paaf.job_id = pp.job_id
    AND paaf.supervisor_id = mg.person_id(+)
    AND TRUNC (SYSDATE) BETWEEN TRUNC (mg.effective_start_date)
    AND TRUNC (mg.effective_end_date)
    AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND hl_freq.lookup_type = 'FREQUENCY'
    AND paaf.probation_period IS NOT NULL
    AND TRUNC (paaf.date_probation_end) BETWEEN paaf.effective_start_date
    AND NVL (paaf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (paaf.date_probation_end) BETWEEN papf.effective_start_date
    AND NVL (papf.effective_end_date,
    hr_general.end_of_time
    )
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN papf.effective_start_date
    AND papf.effective_end_date
    AND TRUNC (NVL (paaf.date_probation_end, SYSDATE))
    BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    AND TO_CHAR (papf.original_date_of_hire, 'yyyy') =
    TO_CHAR (SYSDATE, 'yyyy')
    AND TO_DATE (SYSDATE, 'DD-MM-RRRR') =
    TO_DATE (paaf.date_probation_end, 'DD-MM-RRRR')
    - 30





    Great effort, I wish I saw it earlier. Would have saved my day :)


    Obrigado,
    Radhey

    ReplyDelete
  3. Hi There,

    So bloody thorough! Ah! So happy and blissed out! I feel redeemed by reading out SQL Indexes. Keep up the good work!

    I got a list of the INDEXES from DBA Team which are non value added to the Database with the following conditions

    Quote:

    1:Low selectivity indexes.
    2:The number of distinct key values is less than 10 and the number of rows in table in greater than 100000.
    3:All these normal(Balances -Tree )indexes.

    They are asking the App Team to review these and to drop those indexes.
    But when I analyzed those indexes some of the columns are using in the where conditions of the so many queries.
    After applying the HINT of those indexes also the cost of query was decreasing.

    Here my confusion was,if we really drop those indexes, will it increase the performance?
    or
    Do we need to convert those as BITMAP as they have low cardinality ?

    Please help me to understand the concept behind this .

    Thank you very much and will look for more postings from you.

    Thanks and Regards,
    Preethi.

    ReplyDelete