对通过index range scans访问的表的优化

表authors有1706123行,39677个blocks,259个empty blocks,在au_fname有索引INDEX_AUTHORS.
对表进行分析.
Analyze Table AUTHORS
Compute Statistics
For All Indexes;
SQL> select CLUSTERING_FACTOR from dba_indexes where INDEX_NAME='INDEX_AUTHORS';

CLUSTERING_FACTOR
-----------------
          1706061

CLUSTERING_FACTOR的大小为1706061

SQL> select * from authors t where au_fname like 'W%';

已选择68000行。

已用时间:  00: 00: 44.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3817 Card=85306 Byte
          s=9639578)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AUTHORS' (Cost=3817 Card
          =85306 Bytes=9639578)

   2    1     INDEX (RANGE SCAN) OF 'INDEX_AUTHORS' (NON-UNIQUE) (Cost
          =71 Card=15355)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      72818  consistent gets
      10844  physical reads
          0  redo size
   11388161  bytes sent via SQL*Net to client
      50358  bytes received via SQL*Net from client
       4535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68000  rows processed

SQL> /

已选择68000行。

已用时间:  00: 00: 29.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3817 Card=85306 Byte
          s=9639578)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AUTHORS' (Cost=3817 Card
          =85306 Bytes=9639578)

   2    1     INDEX (RANGE SCAN) OF 'INDEX_AUTHORS' (NON-UNIQUE) (Cost
          =71 Card=15355)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      72818  consistent gets
       4632  physical reads
          0  redo size
   11388161  bytes sent via SQL*Net to client
      50358  bytes received via SQL*Net from client
       4535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68000  rows processed

这里有72818  consistent gets和4632  physical reads.

对表AUTHORS按au_fname进行重组:

SQL> create table authors_2 as select * from authors order by au_fname;

Table created
SQL>CREATE INDEX INDEX_AUTHORS2 ON HR.AUTHORS_2 (AU_FNAME) NOLOGGING;

Index created

分析表AUTHORS_2
Analyze Table AUTHORS_2
Compute Statistics
For All Indexes;

SQL> select CLUSTERING_FACTOR from dba_indexes where INDEX_NAME='INDEX_AUTHORS2';

CLUSTERING_FACTOR
-----------------
            39677

SQL> select * from authors_2 t where au_fname like 'W%';
已选择68000行。

已用时间:  00: 00: 11.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=429 Card=85306 Bytes
          =9639578)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AUTHORS_2' (Cost=429 Car
          d=85306 Bytes=9639578)

   2    1     INDEX (RANGE SCAN) OF 'INDEX_AUTHORS2' (NON-UNIQUE) (Cos
          t=71 Card=15355)

 

 

Statistics
----------------------------------------------------------
       1228  recursive calls
          0  db block gets
      11051  consistent gets
          1  physical reads
          0  redo size
   11388161  bytes sent via SQL*Net to client
      50358  bytes received via SQL*Net from client
       4535  SQL*Net roundtrips to/from client
         28  sorts (memory)
          0  sorts (disk)
      68000  rows processed

SQL> /

已选择68000行。

已用时间:  00: 00: 11.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=429 Card=85306 Bytes
          =9639578)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'AUTHORS_2' (Cost=429 Car
          d=85306 Bytes=9639578)

   2    1     INDEX (RANGE SCAN) OF 'INDEX_AUTHORS2' (NON-UNIQUE) (Cos
          t=71 Card=15355)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10832  consistent gets
          0  physical reads
          0  redo size
   11388161  bytes sent via SQL*Net to client
      50358  bytes received via SQL*Net from client
       4535  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68000  rows processed
可以看到重组后只有10832  consistent gets和0  physical reads,效率提高很多.

Oracle Indexing Basics

The main goal of an index is to speed the process of finding data. An index file contains a data value for a specific field in a table and a pointer that identifies the record that contains a value for that field. In other words, an index on the last_name field for a table would contain a list of last names and pointers to specific records-just as an index to a book lists topics and page numbers to enable readers to access information quickly. When processing a request, the database can use some or all of the available indexes to efficiently locate the requested rows. Oracle uses several indexing schemes, but B-tree indexes are the most common.

Figures 1 and 2 illustrate some of the concepts of a B-tree index. The upper blocks contain index data that points to lower-level index blocks. The lowest-level blocks contain every indexed data value and a corresponding row ID used for locating the actual row of data.

To illustrate how resequencing can reduce response times, consider a table in which the rows are not in the same sequence as the index. When the index is used to retrieve a series of rows that are adjacent to each other in the indexed version of the table, the index tree points to widely scattered locations among the physical blocks where the row data is stored (see Figure 1). Because the system must access many blocks to retrieve the data, it requires many I/O operations. If you resequence the table, however, the rows will match the order of the primary-key index. Thus, the data from adjacent rows in the indexed table is stored in a single physical location on the disk (see Figure 2), and I/O is reduced because the system needs to access fewer blocks in order to retrieve the data.

 

Figure 1: The rows of a table on disk rarely map to the primary index; data in adjacent rows may be scattered across many blocks. Disk I/O occurs each time the instance needs to retrieve a new row.

Figure 2: Once you have resequenced the table so that its rows are stored in the same order as the primary-key index, data in adjacent rows is consolidated in a minimum number of blocks.

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/51726/viewspace-65958/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/51726/viewspace-65958/