Test the reading of 10046 data blocks in the table after the index is created in Oracle

Purpose
1. Table scanning before index creation
1. Which data blocks are scanned?
2. Relationship between data blocks
3. Physical read
4. Logical read
5. Test the differences: full table scan and partial Table Record Scan
6. Do I scan data blocks for single-block reading, multiple-block reading, or multiple-block reading after one-block reading?
2. Summary:
1. Relationship between table scan speed and data block size
2. Relationship between table scan and parallelism settings
3. Relationship between table scan and db cache

In the previous article, we tested the reading of data blocks scanned in the entire table. If an index is set up on the table, we first read the index and then read the data blocks recorded in the corresponding table based on the ROWID.

SQL> create table t_detail (a int );

Table created.
-- Insert 10000 records
SQL> insert into t_detail select level from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t_detail on t_detail ();

Index created.

-- Trace the query of created Indexes
SQL> alter system set events '10046 trace name context level 8 ';

System altered.

-- Because the table contains 10000 million data records, 10046 trace may affect the query speed.
SQL & gt; select count (a) from t_detail where a = 2000;

COUNT ()
----------
1

-- Disable 10046 trace
SQL> alter system set events '10046 trace name context off ';

System altered.

-- Extract only 10046 Important trace content

WAIT #2: nam = 'disk file operations I/o' ela = 886 FileOperation = 2 fileno = 10 filetype = 2 obj # = 69559 tim = 31824399508 -- first a WAIT event
WAIT #2: nam = 'db file sequential read 'ela = 20687 file # = 10 block # = 276483 blocks = 1 obj # = 69559 tim = 31824420353-single block read file # = 10 block # = 276483 blocks = 1 obj # = 69559
WAIT #2: nam = 'db file sequential read 'ela = 823 file # = 10 block # = 276488 blocks = 1 obj # = 69559 tim = 31824421542 -- continue reading a single file # = 10 block # = 276488 blocks = 1 obj # = 69559
FETCH #2: c = 0, e = 23170, p = 2, cr = 2, cu = 0, mis = 0, r = 1, dep = 0, og = 1, plh = 1976055679, tim = 31824421699 -- then extract the data
STAT #2 id = 1 cnt = 1 pid = 0 pos = 1 bj = 0 p = 'sort AGGREGATE (cr = 2 pr = 2 pw = 0 time = 0 us )'
STAT #2 id = 2 cnt = 1 pid = 1 pos = 1 bj = 69559 p = 'index range scan IDX_T_DETAIL (cr = 2 pr = 2 pw = 0 time = 0 us cost = 1 size = 13 card = 1)'

-- What are the two read data blocks? Table or index data block
-- We can see that the objects in the above TRACE are not tables
SQL> select owner, object_name, object_id from dba_objects where object_name ='t _ DETAIL 'and wner = 'Scott ';
 
OWNER OBJECT_NAME OBJECT_ID
------------------------------------------------------------------------------------------------------------------------
SCOTT T_DETAIL 69558

-- Is it an index? It is an index. Therefore, a single read reads the index data block first.
SQL> select owner, object_name, object_id from dba_objects where object_name = 'idx _ T_DETAIL 'and wner = 'Scott ';
 
OWNER OBJECT_NAME OBJECT_ID
------------------------------------------------------------------------------------------------------------------------
SCOTT IDX_T_DETAIL 69559

-- Since the index data block is read, where is the index data block?

-- The header block of the index is 276482.
SQL> select segment_name, HEADER_FILE, header_block from dba_segments ds where ds. segment_name = 'idx _ T_DETAIL ';
 
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------------------------------------------------------------------------------------
IDX_T_DETAIL 10 276482

-- Blocks occupied by tables
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where wner = 'Scott 'and segment_name = 't_ DETAIL ';
 
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------------------------------------
T_DETAIL 0 220392 8
T_DETAIL 1 276336 8
T_DETAIL 2 276344 8

-- Block occupied by index
SQL> select segment_name, extent_id, block_id, blocks from dba_extents where wner = 'Scott 'and segment_name = 'idx _ T_DETAIL ';
 
SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS
--------------------------------------------------------------------------------------------------------------
IDX_T_DETAIL 0 276480 8
IDX_T_DETAIL 1 276488 8
IDX_T_DETAIL 2 276496 8
IDX_T_DETAIL 3 276504 8

In the above 10046 trace, a single block Read file # = 10 block # = 276483 blocks = 1 obj # = 69559, exactly within the first extent
The other one reads file # = 10 block # = 276488 blocks = 1 obj # = 69559, which is exactly the first block of the second extent.

1 2 3 Next Page