Document Type | Technical Information
Category | Administration
Document Number | TADTI074
Overview
Even when performing a Table Full Scan, Current mode reads can occur to read the extent information of the segment. This article forces a Full Scan using a hint and checks the Current mode reads (db block gets) that occur at this time.
Method
Extent Information of Segment during Table Full Scan
A Table Full Scan may seem like a simple query, but during the process, Current mode reads may be involved to check extent distribution information of the segment.
Below is an example using the FULL(emp1) hint to induce a Full Scan on the emp1 table.
SQL> select /*+ full(emp1) */ * from emp1 where empno = 7788; EMPNO ENAME DEPTNO SAL ------ ----------- -------- ---------- 7788 TIBERO7788 8 7546894 1 row selected.
SQL ID: 7dkrdnp2q5hr6
Child number: 133
Plan hash value: 1734378360
Execution Plan
--------------------------------------------------------
1 TABLE ACCESS (FULL): EMP1 (Cost:1883, %%CPU:1, Rows:1)
Predicate Information
--------------------------------------------------------
1 - filter: ("EMP1"."EMPNO" = 7788) (0.000)
NAME VALUE
------------------------------ -------
db block gets 42
consistent gets 4424
physical reads 4407
redo size 0
sorts (disk) 0
sorts (memory) 3
rows processed 1- db block gets = 42 โ This means that some blocks including segment extent information were read in Current mode.
- consistent gets and physical reads indicate the cost of reading the entire table range.