Oracle table access by index rowid description

 

 

I. Test Environment

SQL> select * from V $ version where rownum = 1;

 

Banner

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition release11.2.0.3.0-64bit Production

 

SQL> Create Table Dave as selectobject_id, object_name, object_type, created, timestamp, status from all_objects;

The table has been created.

 

SQL> Create Table dave2 as select * from Dave;

The table has been created.

 

-- Collect statistics. No histogram is collected here:

SQL> exec dbms_stats.gather_table_stats (ownname => 'sys ', tabname => 'Dave', estimate_percent => 10, method_opt => 'forcolumns size 1', Degree => 10, cascade => true );

 

The PL/SQL process is successfully completed.

 

SQL> exec dbms_stats.gather_table_stats (ownname => 'sys ', tabname => 'dave2', estimate_percent => 10, method_opt => 'forcolumns size 1', Degree => 10, cascade => true );

 

The PL/SQL process is successfully completed.

 

 

-- To avoid other impacts, refresh the buffer cache first:

SQL> alter system flush buffer_cache;

 

The system has been changed.

 

-- View the execution plan for full table scan:

SQL> set autot traceonly

 

SQL> select d1.object _ name, d2.object _ type fromdave D1, dave2 D2 where d1.object _ id = d2.object _ id;

 

Row 72762 has been selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3613449503

 

Bytes ------------------------------------------------------------------------------------

| ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time |

Bytes ------------------------------------------------------------------------------------

| 0 | SELECT statement | 72520 | 3824k | 695 (1) | 00:00:09 |

| * 1 | hash join | 72520 | 3824k | 2536k | 695 (1) | 00:00:09 |

| 2 | table access full | dave2 | 71990 | 1687k | 213 (1) | 00:00:03 |

| 3 | table access full | Dave | 72520 | 2124k | 213 (1) | 00:00:03 |

Bytes ------------------------------------------------------------------------------------

 

Predicate information (identified by Operation ID ):

---------------------------------------------------

 

1-access ("d1". "object_id" = "D2". "object_id ")

 

 

Statistics

----------------------------------------------------------

0 recursive cballs

0 dB block gets

6353 consistent gets

1558 physical reads

0 redo size

3388939 bytes sent via SQL * Net toclient

53874 bytes received via SQL * netfrom Client

4852 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

72762 rows processed

-- 1558 of physical reads are generated.

SQL>

 

 

-- Create an index on object_id:

 

SQL> Create index idx_dave_object_idon Dave (object_id );

The index has been created.

SQL> Create index idx_dave_object_id2 ondave2 (object_id );

The index has been created.

 

-- View the execution plan at the following time:

 

SQL> select d1.object _ name, d2.object _ type fromdave D1, dave2 D2 where d1.object _ id = d2.object _ id;

 

Row 72762 has been selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3613449503

 

Bytes ------------------------------------------------------------------------------------

| ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | time |

Bytes ------------------------------------------------------------------------------------

| 0 | SELECT statement | 72520 | 3824k | 695 (1) | 00:00:09 |

| * 1 | hash join | 72520 | 3824k | 2536k | 695 (1) | 00:00:09 |

| 2 | table access full | dave2 | 71990 | 1687k | 213 (1) | 00:00:03 |

| 3 | table access full | Dave | 72520 | 2124k | 213 (1) | 00:00:03 |

Bytes ------------------------------------------------------------------------------------

 

Predicate information (identified by Operation ID ):

---------------------------------------------------

 

1-access ("d1". "object_id" = "D2". "object_id ")

 

 

Statistics

----------------------------------------------------------

1 recursive cballs

0 dB block gets

6353 consistent gets

0 physical reads

0 redo size

3388939 bytes sent via SQL * Net toclient

53874 bytes received via SQL * netfrom Client

4852 SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

72762 rows processed

 

The physical reading here is 0, but the full table scan is still used.

 

-- Refresh the buffer and add the index conditions:

SQL> alter system flush buffer_cache;

 

The system has been changed.

 

SQL> select d1.object _ name, d2.object _ type fromdave D1, dave2 D2 where d1.object _ id = d2.object _ id and d1.object _ id <100;

 

98 rows have been selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 504164237

 

Bytes ----------------------------------------------------------------------------------------------------

| ID | operation | Name | rows | bytes | cost (% CPU) | time |

Bytes ----------------------------------------------------------------------------------------------------

| 0 | SELECT statement | 3600 | 189k | 23 (5) | 00:00:01 |

| * 1 | hash join | 3600 | 189k | 23 (5) | 00:00:01 |

| 2 | table access by index rowid | dave2 | 3600 | 86400 | 11 (0) | 00:00:01 |

| * 3 | index range scan | idx_dave_object_id2 | 648 | 3 (0) | 00:00:01 |

| 4 | table access by index rowid | Dave | 3626 | shard k | 11 (0) | 00:00:01 |

| * 5 | index range scan | idx_dave_object_id | 653 | 3 (0) | 00:00:01 |

Bytes ----------------------------------------------------------------------------------------------------

 

Predicate information (identified by Operation ID ):

---------------------------------------------------

 

1-access ("d1". "object_id" = "D2". "object_id ")

3-access ("D2". "object_id" <100)

5-access ("d1". "object_id" <100)

 

 

Statistics

----------------------------------------------------------

1 recursive cballs

0 dB block gets

20 consistent gets

6 physical reads

0 redo size

3317 bytes sent via SQL * Net toclient

590 bytes received via SQL * netfrom Client

8. SQL * Net roundtrips to/fromclient

0 sorts (memory)

0 sorts (Disk)

98 rows processed

 

SQL>

 

After the index is performed, physical reads are reduced from 1558 to 6.

 

 

Ii. Description

In the above test, we see the index scan type and multi-Table association type. For details about these types, refer:

 

Five types of Oracle index Scanning

Http://blog.csdn.net/tianlesoftware/article/details/5852106

 

Three methods for multi-table join: Hash join merge joinnested Loop

Http://blog.csdn.net/tianlesoftware/article/details/5826546

 

From the execution plan, when we take the index, the corresponding table will appear:

Table access by index rowid

 

The oraclerowid is described in the following article.

Oracle rowid

Http://blog.csdn.net/tianlesoftware/article/details/5020718

 

 

Rowid is a pseudo column. It is constructed when the query result is output. Rowid does not actually exist in the data block of the table. It is stored in the index and used to find row data in the table through rowid.

 

Rowid consists of the following parts:

1. Data Object number: each data object (such as a table or index) is assigned with this number when it is created, and this number is unique in the database.

2. Related File number: This number is unique for each data file in the tablespace.

3. block number: indicates the position of the block containing this row in the data file.

4. row number: identify the location of the row directory in the block Header

 

The Oracle index stores the rowid corresponding to the value of our field and the value. When we search for the value based on the index, the rowid of the block will be returned, then we directly block the data we need based on rowid, so the following occurs:

Table access by index rowid

 

Because rowid corresponds to one block, only one block can be read at a time when table access by index rowid is used.

 

Suppose our data returns 100 rowids, 10 of which are located in the same block. Then we only need to access 91 blocks to get the data we need.

 

For details about how to determine the block in which row records are stored, refer:

Oracle rdba and DBA description

Http://blog.csdn.net/tianlesoftware/article/details/6529346

 

 

Summary:

(1) Table access by index rowid only appears when the index is used.

(2) Table access by index rowid is a single block read, and only one block can be read at a time.

 

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940