Shulou(Shulou.com)06/01 Report--

The access path refers to the way that Oracle finds the data that users need, which are rare, including:

The infamous full table scan-the (misinterpreted) access path that people avoid at all costs.

Various types of index scans-this is the access path that people feel good about (in most cases it is misinterpreted).

Direct access through hash or rowid is usually the fastest for single data rows.

No access path is the best, and if there is, then Oracle only needs to provide this access path.

Full table scan

A full scan is a sequential reading of all data blocks in a table. Using multi-block reading, scan the blocks in the table from scratch to the high water mark. Full scan is an effective method to deal with a large amount of data. Keep in mind that full scanning is not evil, and in most cases full scanning is the fastest way to get results.

The number of blocks read per full scan is specified by the parameter db_file_multiblock_read_count

SQL > show parameter db_file_muNAME TYPE VALUE -db_file_multiblock_read_count integer 128

2.rowid access

Rowid is the physical location of a row of data, and the speed of accessing a single row of data is the fastest.

SQL > select * from emp where rowid = 'AAASZHAAEAAAACXAAN'; 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

Access the data through the index, in fact, also through the index, first find the rowid of this row of data, and then access the data through rowid.

SQL > set autotrace on traceonlySQL > select * from emp where empno=7934 Execution Plan---Plan hash value: 2949544139 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | | 0 (0) | 00:00:01 |-|

Rowid can also perform range scanning.

SQL > select * from emp where rowid between 'AAASZHAAEAAAACXAAA' and' AAASZHAAEAAAACXAAN' 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981/ 09/28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00: 00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.

3. Index scan

Index scanning is one of the most common data accesses, such as

SQL > set autotrace on traceonlySQL > select * from emp where empno=7934 Execution Plan---Plan hash value: 2949544139 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | | 0 (0) | 00:00:01 |-|

Let's mainly take the b-tree index as an example.

Index uniqueness scan

The optimizer knows that the value of the index column is unique and that the query result returns only one row. The access speed of this kind of index is the fastest, and when a row of data is found, the index is no longer scanned and returned directly.

SQL > select * from emp where empno=7934 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10Execution Plan---Plan hash value: 2949544139- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -- | 0 | SELECT STATEMENT | | 1 | 38 | 1 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0) | 00:00:01 | | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) | 00:00:01 |-- -

There is actually no non-unique index in Oracle, and in a non-unique index, Oracle adds the rowid of the data to the index key to make it unique.

Index range scan

SQL > set autot traceonlySQL > select empno from emp where empno select empno from emp where empno select * from emp where ename='KING' Execution Plan---Plan hash value: 2929622481 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 2 (0) | 00:00 : 01 | | * 2 | INDEX RANGE SCAN | IND_EMP_ENAME | 1 | | 1 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-2-access ("ENAME" = 'KING')

Index full scan

SQL > select empno from emp 14 rows selected.Execution Plan---Plan hash value: 179099197Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0) | 00:00:01 |- -

Full index scan, not all indexes are scanned. It actually only needs to scan the leaf nodes of the index. However, in order to find the location of the leaf nodes, some branch nodes are also scanned.

Let's look at the following query

SQL > select empno,ename from emp 14 rows selected.Execution Plan---Plan hash value: 3956160932 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 140 | 3 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | EMP | 14 | 140 | 3 (0) | 00:00:01 |- -

The query column ename is not in the index, so it is a full table scan. But if we modify the statement as follows.

SQL > select empno,ename from emp order by empno 14 rows selected.Execution Plan---Plan hash value: 4170700152 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 140 | 2 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 14 | 140 | 2 (0) | 00:00:01 | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) | 00:00:01 |-

Oracle uses full index scans to avoid sorting operations. Because the index is ordered data, and the index full scan is a sequential single-block read operation.

Max and min

SQL > select max (empno) from emp Execution Plan---Plan hash value: 1707959928 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0) | 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 4 | | 2 | INDEX FULL SCAN (MIN/MAX) | PK_EMP | 1 | | 4 | 1 (0) | 00:00:01 |

This scan uses a full index scan, but it's not really a full scan. The max and min qualifiers let Oracle know when to stop, it just scans the highest or lowest blocks.

Index jump scan

An index is usually used, and the leading column of the index must appear in the query condition.

SQL > create table t (an int,b int, c int,d int,e int,f int,g int); SQL > create index t_idx on t

In general, only the following queries will use the index

Select * from t where a =: a TX select * from t where a =: an and b =: BX select * from t where a =: an and b =: b and c =: C

However, the following query does not use the index (except for forcing a full scan of the index using hint)

Select * from t where b =: BTX select * from t where c =: CTX select * from t where b =: b and c =: C

Jump index scanning is implemented after Oracle 9i, and the conditions are as follows:

Other columns in the index are used in the predicate.

The DISTINCT_NUM of leading column values is relatively small.

Let's look at the following example

SQL > create table t as 2 select mod (rownum,3) a from all_objects;Table created.SQL > create index t_idx on t; Index created.SQL > analyze table t compute statistics;Table analyzed.SQL > select * from t where bpen1 and and 1 Execution Plan---Plan hash value: 2053318169 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 1 | 34 | 5 (0) | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 34 | 5 (0) | 00:00:01 | | * 2 | INDEX SKIP SCAN | T_IDX | 1 | | 4 (0) | 00:00:01 |-Predicate Information (identified by operation id):- -- 2-access ("B" = 1 AND "C" = 1) filter ("B" = 1 AND "C" = 1) Statistics -1 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 724 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

Because there are only three values of a, Oracle treats the index (a _ r _ b ~ C) as three small indexes.

Index fast full scan

Index fast full scan is obviously different from index full scan, it has the following characteristics

It reads every block in the index, including all branch blocks.

It uses multi-block reading, just like a full table scan.

It does not scan the index in sort order.

Let's first create a table and insert a large amount of data.

SQL > create table big_table as select * from dba_objects;Table created.SQL > insert into big_table select * from big_table;74577 rows created.SQL > insert into big_table select * from big_table;223731 rows created.SQL > / 447462 rows created.SQL > commit;Commit complete.SQL > alter table big_table modify object_id not null;Table altered.SQL > create index idx_big_table_objid on big_table (object_id); Index created.SQL > analyze table big_table compute statistics;Table analyzed.

Execute the following query

SQL > set autot traceonlySQL > select object_id from big_table 894924 rows selected.Execution Plan---Plan hash value: 205523069 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 894K | 3495K | 544 (2) | 00:00:07 | 1 | INDEX FAST FULL SCAN | IDX_BIG_TABLE_OBJID | 894K | 3495K | 544 (2) | 00:00:07 |- -Statistics-- -15 recursive calls 0 db block gets 61534 consistent gets 2 physical reads 0 redo size 15755358 bytes sent via SQL*Net to client 656794 bytes received via SQL*Net from client 59663 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 894924 rows processed

The query uses a fast full scan of the index.

People who are interested can think about why the following query does not use index fast full scan, but index full scan.

SQL > select empno from emp 14 rows selected.Execution Plan---Plan hash value: 179099197Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0) | 00:00:01 |- -Statistics--- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 686 bytes sent via SQL*Net To client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed

Index join

An index join (index join) is the index path selected for a query when there are multiple indexes in the table.

Let's look at the following example

SQL > create table T1 as select * from dba_objects;Table created.SQL > create index t1_idx1 on T1 (object_id); Index created.SQL > create index t1_idx2 on T1 (owner,object_type); Index created.SQL > analyze table T1 compute statistics; Table analyzed.SQL > set autot traceonly SQL > select object_id,owner,object_type from T1 where object_id between 100 and 2000 3 and owner='SYS' 1478 rows selected.Execution Plan---Plan hash value: 2563395799 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 69 | 1173 | 18 (6) | 00:00:01 | | * 1 | VIEW | index$_join$_001 | 69 | 1173 | 18 (6) | 00:00:01 | | * 2 | HASH JOIN | | | * 3 | INDEX RANGE SCAN | T1_IDX1 | 69 | 1173 | 7 (15) | 00:00:01 | | * 4 | INDEX RANGE SCAN | T1_IDX2 | 69 | 1173 | 12 (9) | 00:00:01 |-- | -Predicate Information (identified by operation id):-1- Filter ("OBJECT_ID" = 100) 2-access (ROWID=ROWID) 3-access ("OBJECT_ID" > = 100 AND "OBJECT_ID"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.