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 page first.Buffer cache:

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

--In this example1558Physical read

SQL>

--InObject_idCreate an index on: 

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: 

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.

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

1 2 Next Page