Turbocharge SQL with advanced Oracle Indexing

Source: Internet
Author: User
Tags one table

 

Oracle tips by Burleson Consulting
March 26,200 2-updated June 28,200 7


For complete details on Oracle indexing for high performance, see my book "Oracle tuning: the definitive reference ".

Oracle distributed des numerous data structures to improve the speed of Oracle SQL queries. taking advantage of the low cost of disk storage, Oracle primary des indexing new indexing algorithms that dramatically increase the speed with which Oracle queries are serviced. this article attributes es the internals of Oracle indexing; reviews the Standard B-tree index, bitmap indexes, function-based indexes, and index-only tables (IOTs ); and demonstrates how these indexes may be dramatically increase the speed of Oracle SQL queries.

Oracle uses indexes to avoid the need for large-table, full-Table scans and disk sorts, which are required when the SQL optimizer cannot find an efficient way to service the SQL query. I begin our look at Oracle indexing with a review of standard Oracle B-tree index methodologies.

The Oracle B-Tree Index

The oldest and most popular type of Oracle indexing is a standard B-tree index, which excels at servicing simple queries. the B-tree index was introduced in the earliest releases of Oracle and remains widely used with Oracle.

B-tree indexes are used to avoid large sorting operations. for example, a SQL query requiring 10,000 rows to be presented in sorted order will often use a B-tree index to avoid the very large sort required to deliver the data to the end user.


An Oracle B-Tree Index

Oracle offers several options when creating an index using the default B-tree structure. it allows you to index on multiple columns (concatenated indexes) to improve access speeds. also, it allows for individual columns to be sorted in different orders. for example, we cocould create a B-tree index on a column called last_name in ascending order and have a second column within the index that displays the salary column in descending order.

Create Index
Name_salary_idxOnPerson(Last_name ASC,


Salary DESC );

While B-tree indexes are great for simple queries, they are not very good for the following situations:

  • Low-cardinality ColumnsUsing olumns with less than 200 distinct values do not have the selecti1_required in order to benefit from Standard B-tree index structures.
  • No support for SQL FunctionsIndexes-tree indexes are not able to support SQL queries using Oracle's built-in functions. oracle9IProvides a variety of built-in functions that allow SQL statements to query on a piece of an indexed column or on any one of a number of transformations against the indexed column.


Prior to oracle9I, The Oracle SQL optimizer had to perform time-consuming long-table, full-Table scans due to these tables comings. Consequently, it was no surprise when Oracle introduced more robust types of indexing structures.

Bitmapped Indexes

Oracle Bitmap indexes are very different from standard B-tree indexes. in bitmap structures, a two-dimenstmarray is created with one column for every row in the table being indexed. each column represents a distinct value within the bitmapped index. this two-dimen1_array represents each value within the index multiplied by the number of rows in the table. at row retrieval time, Oracle decompresses the bitmap into the ram data buffers so it can be rapidly scanned for matching values. these matching values are delivered to Oracle in the form of a row-ID list, and these row-ID values may directly access the required information.

The real benefit of bitmapped indexing occurs when one table between des multiple bitmapped indexes. each individual column may have low cardinality. the creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.

For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. each column contains less than 100 distinct values by themselves, and a B-tree index wocould be fairly useless in a database of 20 million vehicles. however, combining these indexes together in a query can provide blistering response times a lot faster than the traditional method of reading each one of the 20 million rows in the base table. for example, assume we wanted to find old blue Toyota Corollas manufactured in 1981:

Select
License_plat_nbrFromVehicleWhereColor = Lue? /Font>
AndMake = export oyota? /Font>
And


Year = 1981;

Oracle uses a specialized optimizer method called a bitmapped index merge to service this query. in a bitmapped index merge, each row-ID, or RID, list is built independently by using the bitmaps, and a special merge routine is used in order to compare the RID lists and find the intersecting values. using this methodology, Oracle can provide sub-second response time when working against multiple low-cardinality columns:

Oracle bitmap merge join

Function-based indexes

One of the most important advances in Oracle indexing is the introduction of function-based indexing. function-based indexes allow creation of indexes on expressions, internal functions, and user-written functions in PL/SQL and Java. function-based indexes ensure that the oracle designer is able to use an index for its query.

Prior to oracle8, the use of a built-in function wocould not be able to match the performance of an index. consequently, Oracle wocould perform the dreaded full-Table scan. examples of SQL with function-based queries might include the following:

Select * from customer where substr (cust_name, 1, 4) = Response URL?
Select * from customer where to_char (order_date, interval M? =? 1;
Select * from customer where upper (cust_name) = orders ones?
Select * from customer where initcap (first_name) = policike?

In Oracle, Oracle always interrogatesWhereClause of the SQL statement to see if a matching index exists. By using function-based indexes, the oracle designer can create a matching index that exactly matches the predicates within the SQLWhereClause. This ensures that the query is retrieved with a minimal amount of disk I/O and the fastest possible speed.

Index-only tables

Beginning with oracle8, Oracle recognized that a table with an index on every column did not require table rows. in other words, Oracle recognized that by using a special table-access method called an index fast full scan, the index cocould be queried without actually touching the data itself.

Oracle codified this idea with its use of index-only table (IOT) structure. when using an IOT, Oracle does not create the actual table but instead keeps all of the required information inside the Oracle index. at query time, the Oracle SQL optimizer recognizes that all of the values necessary to service the query exist within the index tree, at which time the Oracle cost-based optimizer has a choice of either reading through the Index Tree nodes to pull the information in sorted order or invoke an index fast full scan, which will read the table in the same fashion as a full table scan, using sequential prefetch (as defined by the db_file_multiblock_read_count parameter ). the multiblock read facility allows Oracle to very quickly scan index blocks in linear order, quickly reading every block within the index tablespace. here is an example of the syntax to create an Iot.

Create Table emp_iot (
Emp_id number,Ename varchar2 (20 ),Sal number (9, 2 ),Deptno number,Constraint pk_emp_iot_index primary key (emp_id ))Organization IndexTablespace spc_demo_ts_01


Pcthreshold 20 including ename;

Index performance

Oracle indexes can greatly improve query performance but there are some important indexing concepts to understand.

  • Index Clustering
  • Index blocksizes
Indexes and blocksize

Indexes that experience lots of index range scans of index fast full scans (as evisponby multiblock reads) will greatly benefit from residing in a 32 K blocksize.

Today, most Oracle tuning experts utilize the multiple blocksize feature of oracle because it provides buffer segregation and the ability to place objects with the most appropriate blocksize to reduce buffer waste. some of the world record Oracle benchmarks use very large data buffers and multiple blocksizes.

According to an article by Christopher foot, author of The OCP instructors guide for Oracle DBA certification, larger block sizes can help in certain situations:

"A bigger block size means more space for key storage in the branch nodes of B-tree indexes, which has CES index height and improves the performance of indexed queries ."

In any case, there appears to be evidence that block size affects the tree structure, which supports the argument that data blocks affect the structure of the tree.

Indexes and clustering

The CBO's demo-to perform a full-Table. an index range scan is influenced by the clustering_factor (located inside the dba_indexes view), db_block_size, and avg_row_len. it is important to understand how the CBO uses these statistics to determine the fastest way to deliver the desired rows.
 
Conversely, a high clustering_factor, where the value approaches the number of rows in the table (num_rows), indicates that the rows are not in the same sequence as the index, and Additional I/O will be required for index range scans. as the clustering_factor approaches the number of rows in the table, the rows are out of sync with the index.

Oracle Metalink Note: 223117.1 has some great advice for tuning-down into B file sequential read? Waits by table reorganization in row-order:
 

-If index range scans are involved, more blocks than necessary cocould be being visited if the index is unselective: By forcing or enabling the use of a more selective index, we can access the same table data by visiting fewer index blocks (and doing fewer physical I/OS ).

-If the index being used has a large clustering factor, then more table data blocks have to be visited in order to get the rows in each index block: by rebuilding the table with its rows sorted by the participating index columns we can reduce the clustering factor and hence the number of table data blocks that we have to visit for each index block.


This validates the assertion that the physical ordering of table rows can reduce I/O (and stress on the database) for each SQL queries.

 

Also see:

  • {
    Function onmousedown ()
    {
    Return CLK (this. href, '','', 'res', '1', '& sig2 = mt5uwtoz0z6whaotlgb2pq ')
    }
    } "Href =" art_otn_cbo_p5.htm "> Oracle SQL clustering_factor tuning index access
  • {
    Function onmousedown ()
    {
    Return CLK (this. href, '','', 'res', '2', '& sig2 = 9k1k6ematril2f-wiva5fg ')
    }
    } "Href =" art_index_clustering_factor.htm "> Oracle SQL clustering_factor
  • {
    Function onmousedown ()
    {
    Return CLK (this. href, '','', 'res', '3', '& sig2 = GyLhQ3zZQiWw6K_a-YrUaQ ')
    }
    } "Href =" oracle_tips_db_file_sequential_read_waits.htm "> the importance of clustering_factor in Multi-block I/O
  • {
    Function onmousedown ()
    {
    Return CLK (this. href, '','', 'res', '4', '& sig2 = pjd7blvar0tk7nx4a4jjjag ')
    }
    } "Href =" art_dbazine_idx_rebuild.htm "> Oracle index rebuilding-indexes rebuild script
  • {
    Function onmousedown ()
    {
    Return CLK (this. href, '','', 'res', '5', '& sig2 = qg_og7tmddh4s _-izv1_ra ')
    }
    } "Href =" oracle_tips_proof_table_index_rebuilding.htm "> Oracle table index rebuilding benefits

 

 

Conclusion

Oracle dominates the market for relational database technology, so Oracle designers must be aware of the specialized index structures and fully understand how they can be used to improve the performance of all Oracle SQL queries. keys of these techniques are discussed my book "Oracle tuning: the definitive reference ".

This text details the process of creating all of Oracle's index tree structures and offers specialized tips and techniques for ensuring SQL queries are serviced using the fastest and most efficient indexing structure.

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.