Summary of data search methods for Oracle 10 Gb Database

We know that,Oracle 10gThe database provides more powerfulSearch for DataTo improve the efficiency of database query. So how does it find data? How can I find data? In this article, we will introduce this part of content. Next let's take a look at it.

Oracle 10g Data Query mainly includes the following methods:

Full table scan and ROWID search data

Full Table scan Full Table Scans ):

Sometimes, when the Oracle database evaluates the optimal execution plan and retrieves a large amount of data, it will give priority to the full table scan, because the full table scan is the best. Generally, the Retrieved Data accounts for about 5% of the table's data-about 10% of the table's data. Table scanning is generally avoided in the OLTP system.

In order to better use table scanning and improve efficiency, there are several partitioning methods in oracle to improve the efficiency of table scanning, so that tables can be partitioned by specific business logic, and partitions should be eliminated as far as possible, reduces the table scan size and improves performance.

ROWID query data Table Access by ROWID or R owid lookup ):

Row ROWID is the fastest way to locate row data in a database file, where data blocks and rows are located.

This method can only read one IO at a time and does not involve multiple IO.

Currently, index search mainly supports five index search methods:

• Index unique search index unique scan)
• Index range scan)
• Index all scans index full scan)
• Index quick scan index fast full scan)
• Index skip search index skip scan)

Search for data through Indexes

Index unique scan ):

If the query column has a UNIQUE or primary key constraint (which ensures that the statement only accesses a single row), Oracle often implements a UNIQUE scan. This search method is very efficient.

Index range scan ):

A single index is used to access multiple rows of data. A typical use of index range scanning on a unique index is a predicate (where condition) range operators (such as >,<>, >=, <=, and between) are used ).

Index full scan ):

Full index scan. Sort the queried data, and the queried data must be directly obtained from the index. At the same time, data sorted by order by should be read in the order of indexes. For example, explain plan for select empno, ename from big_emp order by empno, ename.

Index quick scan index fast full scan ):

It is similar to index full scan, but a significant difference is that it does not sort the queried data, that is, the data is not returned in the order of sorting. In this access method, you can use the multi-block READ function or the parallel READ function to obtain the maximum throughput and shorten the execution time. The index is quickly scanned globally and is often generated without order by, for example: explain plan for select empno, ename from big_emp.

Index skip search index skip scan ):

A where condition column is a non-indexed leading column. As follows:

 
 

  
  
create   index i_emp on emp(empno, ename);  
  
  
 
  
  
select   /* + index_ss(emp i_emp) */   job from emp where ename = ' SMITH ' ; 
 
 

Summary:

Index full scan): Sorting is achieved by using the index sorting function. Principle: root-to-intermediate-leaf-level.

Index fast full scan: without sorting, you can use multiple reading methods to increase the query speed because the data requirements are unordered. That is, you can use multiple queries. The retrieved data is the order in which the data is stored.

When index full scan is performed, oracle locates the root block of the index, then branch block, if any), and then the first leaf block, then read the data in sequence based on the two-way linked list of the leaf block. The read blocks are ordered and sorted.

The index fast full scan is different. It reads bitmap blocks, root blocks, all branch blocks, and leaf blocks from the segment header. The read sequence is determined by the physical storage location, multiple reads are used to read db_file_multiblock_read_count each time.

We have introduced so much about Oracle 10 GB Data search methods. I hope this introduction will be helpful to you!