Four major categories of the index scan Oracle

2011-09-14  来源:本站原创  分类:Database  人气:165 

http://database.51cto.com/art/200911/162180.htm
Here are statistical values ​​based on CBO that full-index scan Oracle full-table scan is more than effective only when the full Oracle index scan, and then check out the data can be obtained directly from the index.

Learn Oracle, you may encounter problems Oracle index scan, index scan here will introduce Oracle solution to the problem, out here and share with you. Where the index type and the different constraints, there are four types of Oracle index scan:
◆ index unique scan (index unique scan)
◆ index range scan (index range scan)
◆ index full scan (index full scan)
◆ index quick scan (index fast full scan)

(1) index unique scan (index unique scan)

Find a value through a unique index always returns a single ROWID. If the unique index has multiple columns (ie combination of the index), then at least in the composite index of the guide columns to the query, such as creating an index: create index idx_test on emp (ename, deptno, loc). Then select ename from emp where ename = 'JACK' and deptno = 'DEV' statement can use the index. If the statement returns only one row, the index unique scan access method is called. And select ename from emp where deptno = 'DEV' statement does not use the index, because the species does not lead where clause column. If there is a UNIQUE or PRIMARY KEY constraint (which guarantees access only the single statement), then, Oracle often achieve unique scanning.

A unique constraint example:

SQL> explain plan for
select empno, ename from emp where empno = 10;
Query Plan
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
(2) an index range scan (index range scan)

Use an index to access multiple rows of data, as above, if the index is a composite index, such as (1) below, and select ename from emp where ename = 'JACK' and deptno = 'DEV' statement returns multiple rows of data, although The statement or the use of the combination of indexes to search, you can access at this time is called the index range scan. Unique index on the index range scan using the typical case is the predicate (where restrictions) using the range operator (such as >,<,<>,>=,<=, between)

Use an index range scan example:

SQL> explain plan for select empno, ename from emp
where empno> 7876 order by empno;
Query Plan
SELECT STATEMENT [CHOOSE] Cost = 1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
In the non-unique index on the predicate col = 5 may return multiple rows of data, so use non-unique index on an index range scan.

Use index rang scan of three situations:
(A) in unique index column on the use of range operators (> <<>> = <= between)
(B) in the composite index on the query using only part of the column, resulting in a multi-line query
(C) the non-unique index column on any inquiry.

(3) index full scan (index full scan)

Correspond with the full table scan, Oracle also has a corresponding full-index scan. In some cases, Oracle may be full index scan instead of range scan, Oracle should be noted that full-index scan mode is only valid in the CBO. CBO based statistic that full-index scan Oracle full-table scan is more than effective only when the full Oracle index scan, and then check out the data can be obtained directly from the index.

Full index scan Oracle example:

An Index full scan will not perform single block i / o's and so it may prove to be inefficient.
eg
Index BE_IX is a concatenated index on big_emp (empno, ename)
SQL> explain plan for select empno, ename from big_emp order by empno, ename;
Query Plan
SELECT STATEMENT [CHOOSE] Cost = 26
INDEX FULL SCAN BE_IX [ANALYZED]
(4) index fast scan (index fast full scan)

Scanning index of all the data blocks, and index full scan is very similar, but a significant difference is that it does not check out the sort of data, that data is not the sort order is returned. In this access method, you can use the multi-block read function can also be read using the parallel for maximum throughput and shorten the execution time.

Index quick scan of the examples:

SQL> explain plan for select empno, ename from big_emp;
Query Plan
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]
SQL> explain plan for select ename from big_emp;
Query Plan
SELECT STATEMENT [CHOOSE] Cost = 1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

Recommended [edit]

Oracle Management Tools Oracle SQL Handler Glance master index scan six minutes chatting learn Oracle Oracle Oracle full table scan code to explain the choice of the chat sequence of steps Oracle implementation plan

相关文章
  • Four major categories of the index scan Oracle 2011-09-14

    http://database.51cto.com/art/200911/162180.htm Here are statistical values ​​based on CBO that full-index scan Oracle full-table scan is more than effective only when the full Oracle index scan, and then check out the data can be obtained directly f

  • We help do some experiments: like when the '%' position and whether the relationship between the index scan 2010-05-13

    I have personally asked before a question, when the percent sign in the query of whether the location with the index scan relationship and it's answer is probably yes:% of which the first position in the string, not adjusted index scan, if in the mid

  • SQL query optimization of index scan and full table scan Talk 2010-09-28

    SQL query optimization of index scan and full table scan Talk Read object: Novice Index scan --- Maybe you have had the experience, the first time, was invited to a friend's house, a friend told that you are happy home 132 1102, and then you went hom

  • By adjusting the Index Tuning Oracle relational query performance 2010-09-16

    This article refers to a Laboratory Information Management System (LIS) database used, suppose we want to do the query test in November 2008, patient records, on condition that more than 80 years old, surnamed Zhou of the patients, the final results

  • SQL query optimization of index scan and full table scans Talk 2010-09-28

    SQL query optimization of index scan and full table scans Talk Read object: junior user Index scan --- maybe you have had the experience, was invited for the first time one day to a friend's house, a friend told you happy home 132 1102, and then you

  • the learning index in oracle 2011-01-19

    Index is an optional structure associated with the table, it's just a quick way to access the data, it only affects the execution speed. Index simply head to: improve the query efficiency 1. The principle of fair use index: a) the conditions show oft

  • construction of the index statement oracle 2011-06-29

    construction of the index statement oracle create index a_index1 on cfmc_3mon (node_id) create index a_index2 on cfmc_3mon (during_time) Or this: create index a_index on cfmc_3mon (node_id, during_time)

  • The use of index in oracle 2011-03-24

    1 index applied to a large amount of data tables, it will greatly improve query performance. Use the index can improve database performance. (2) If no index, perform a search performs a full table search; Once the index is created, then all the table

  • oracle index type: Oracle of how to choose the appropriate index type 2011-03-24

    Index as if a dictionary catalog. With the dictionary catalog, we can very quickly find the entry we need. Database as well. With Oracle database index, the relevant statements can quickly locate the location of records, without having to locate the

  • Reproduced checks index fragmentation (oracle) 2011-03-22

    Morning received Olga Email, I hope I checked under an oracle database index fragmentation, and rebuild index. Reason: monthly index reconstruction task should be performed for some reason failed, and continues for six months, customers wonder if the

  • [Turn] to create an index in Oracle will appear in the actual operation of the restrictions 2011-09-03

    Turn: http://database.51cto.com/art/201003/191868.htm Oracle creates the index in practice there are some restrictions. That table or column index is not possible to establish the correlation, but the more relevant index built, it will affect the Ora

  • Joomla source code analysis (6) major categories of several commonly used methods 2010-04-22

    In the google search will find my site, because no attention to meta and title to all of the open source projects related to the same page title and meta, very user friendly, simply can not distinguish what that link is that, then here need to solve

  • Description of the index on oracle 2011-01-04

    Is about principle. . .

  • Optimization of SQL statement based on the index of 2010-03-15

    1 Introduction 2 2 Master 2 3 Eighteen 3 The first metacarpal to avoid the operation of the column 3 The second metacarpal to avoid unnecessary type conversions 4 The third metacarpal increased to limit the query 4 Get rid of the fourth metacarpal as

  • Reprinted-sql-index optimization 2010-07-02

    Preamble Index SQL Optimization Service by SQL statement of operations is very large, larger in size Bureau of points, often as a small optimization of SQL statements is not enough, resulting in a sharp decline in database performance, minicomputers

  • Index-based optimization of SQL statements 降龙十八掌 2011-05-18

    Index-based optimization of SQL statements The Dragon 18 Palm Master Plan Create the necessary index <br /> this taught The Dragon 18 Palm , a master only one sentence: to establish the necessary index, which is the strength behind The Dragon 18 Pal

  • Oracle Index and create a way to introduce various types of index [turn] 2010-12-02

    Overview of the index in a variety of relational database systems are an important part of its search for improving the speed of data play a vital role. In Oracle, the index base is divided into the following categories: B * Tree index, inverted inde

  • Affect the performance of Oracle on a little knowledge index 2011-01-21

    This article reproduced from In the process of using Oracle, we can not consider the performance and SQL optimization, and correct use of indexes is to optimize the SQL in a very critical factor. If you find that Oracle in the index case and did not

  • oracle index of order 2010-03-24

    1, oracle index trap a table with millions of pieces of data, a field added to the index, but the query performance and no increase, which may mainly be caused by oracle index limit. oracle index there are some restrictions on the index, index of res

  • Oracle Index Analysis and Comparison (2) 2010-07-12

    26.5 Schedule (when the index does not work) First of all, to declare two knowledge points: (1) RBO & CBO. There are two implementation of the Oracle optimizer, one is RBO (Rule Based Optimizer) rule-based optimizer, this optimizer is based on the wo