How to find data in Oracle

There are several data search methods in Oracle 10 Gb. Understanding these data search methods helps us understand the execution plan and optimize our Oracle Database System through the execution plan. You can search for data in the following ways:

Full table scan and rowid search data

1.1 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.

1.2 rowid query data (table access by rowid or rowid 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.

Search for data through Indexes

Currently, index search mainly supports five index search methods:

1. Unique index search (index unique scan)

2. index range search)

3. Full index Scan)

4. Index fast full scan)

5. Index skip search (index skip scan)

2.1 unique index search (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.

2.2 index range Lookup (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)

2.3 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. As follows: Explain plan for select empno, ename from big_emp order by empno, ename

2.4 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, the multi-block READ function can be used, or parallel reading can be used to obtain the maximum throughput and shorten the execution time. The index can be quickly scanned globally and is often generated without order. As follows: Explain plan for select empno, ename from big_emp

2.5 index skip search (index skip scan)

A Where condition column is a non-indexed leading column. 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 index root block, 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.