This topic will describe how Oracle works internally with Index and Full Table Scan.
- Full Table Scan (No Index) when all blocks are available in Buffer Cache.
- Full Table Scan (No Index) when Blocks are not available in Buffer Cache.
- Select query Using index when get block from index and table segments.
- Select query Using index when get blocks / read only index segment.
SQL> CREATE TABLE SAMAD.TBL_INDEX_TEST AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM <= 1500;
Table created.
SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TBL_INDEX_TEST';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 328 8
1 336 8
2 344 8
3 352 8
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBL_INDEX_TEST';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
94044 94044
SQL> SELECT MIN(OBJECT_ID), MAX(OBJECT_ID)
FROM SAMAD.TBL_INDEX_TEST;
MIN(OBJECT_ID) MAX(OBJECT_ID)
-------------- --------------
2 1503SQL> SELECT OBJECT_ID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID IN (2,400, 700, 1000, 1300, 1500);
OBJECT_ID FILE# BLOCK#
---------- ---------- ----------
2 6 331
400 6 336
700 6 340
1000 6 346
1300 6 350
1500 6 353
6 rows selected.
Full Table Scan (No Index) when all blocks are available in Buffer Cache:
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 328 1st level bmb xcur 00000000827D8410
6 329 2nd level bmb xcur 00000000827F1348
6 330 segment header xcur 0000000082BEC7D0
6 331 data block scur 0000000082BE2FC8
6 332 data block scur 0000000082BF7900
6 333 data block scur 0000000082BDC8F8
6 334 data block scur 0000000082BF1230
6 335 data block scur 00000000827F8B98
6 336 data block scur 00000000827D8CD0
6 337 data block scur 00000000827F1C08
6 338 data block scur 0000000082BED090
6 339 data block scur 0000000082BE3888
6 340 data block scur 0000000082BF81C0
6 341 data block scur 00000000827D9590
6 342 data block scur 00000000827F24C8
6 343 data block scur 0000000082BED950
6 344 1st level bmb xcur 0000000082BE2708
6 345 data block scur 0000000082BF8A80
6 346 data block scur 0000000082BDD1B8
6 347 data block scur 0000000082BF1AF0
6 348 data block scur 00000000827F9458
6 349 data block scur 00000000827D9E50
6 350 data block scur 00000000827F2D88
6 351 data block scur 0000000082BEE210
6 352 data block scur 0000000082BE4148
6 353 data block scur 0000000082BF9340
26 rows selected.SQL> COL OBJECT_NAME FOR A30
SQL> COL OWNER FOR A10
SQL> SET LINESIZE 1000
SQL> SET AUTOTRACE ON
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=2;
OWNER OBJECT_NAME OBJECT_ID
---------- ------------------------------ ----------
SYS C_OBJ# 2
Execution Plan
----------------------------------------------------------
Plan hash value: 2020841153
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL_INDEX_TEST | 1 | 26 | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Full Table Scan (No Index) when Blocks are not available in Buffer Cache:
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
no rows selectedSQL> SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=2;
OWNER OBJECT_NAME OBJECT_ID
---------- ------------------------------ ----------
SYS C_OBJ# 2
Execution Plan
----------------------------------------------------------
Plan hash value: 2020841153
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 9 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TBL_INDEX_TEST | 1 | 26 | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
23 physical reads
0 redo size
694 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 330 segment header scur 0000000087BF1C08
6 331 data block scur 0000000087BE46C0
6 332 data block scur 0000000087BDB778
6 333 data block scur 0000000087BED4F0
6 334 data block scur 0000000087BF54E8
6 335 data block scur 00000000877D8410
6 336 data block scur 0000000087BFAE98
6 337 data block scur 0000000087BF9110
6 338 data block scur 0000000087BF24C8
6 339 data block scur 0000000087BE4F80
6 340 data block scur 0000000087BDC038
6 341 data block scur 0000000087BFB758
6 342 data block scur 0000000087BF99D0
6 343 data block scur 0000000087BF2D88
6 345 data block scur 0000000087BDC8F8
6 346 data block scur 0000000087BEDDB0
6 347 data block scur 0000000087BF5DA8
6 348 data block scur 00000000877D8CD0
6 349 data block scur 0000000087BFC018
6 350 data block scur 0000000087BFA290
6 351 data block scur 0000000087BF3648
6 352 data block scur 0000000087BE5840
6 353 data block scur 0000000087BDD1B8
23 rows selected.Select query Using index when get block from index and table segments:
SQL> CREATE INDEX SAMAD.IDX_OBJ_ID ON SAMAD.TBL_INDEX_TEST(OBJECT_ID);
Index created.
SQL> CREATE INDEX SAMAD.IDX_OBJ_ID_NAME ON SAMAD.TBL_INDEX_TEST(OBJECT_ID,OBJECT_NAME);
Index created.
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'IDX_OBJ_ID';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
94045 94045
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'IDX_OBJ_ID_NAME';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
94046 94046
SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'IDX_OBJ_ID';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 360 8
SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'IDX_OBJ_ID_NAME';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 368 8
1 384 8
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94045
old 9: where objd = &data_obj_id
new 9: where objd = 94045
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94046
old 9: where objd = &data_obj_id
new 9: where objd = 94046
no rows selected
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=&OBJECT_ID;
Enter value for object_id: 2
old 1: SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=&OBJECT_ID
new 1: SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=2
OWNER OBJECT_NAME OBJECT_ID
---------- ------------------------------ ----------
SYS C_OBJ# 2
Execution Plan
----------------------------------------------------------
Plan hash value: 3768653396
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TBL_INDEX_TEST | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJ_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
111 recursive calls
0 db block gets
90 consistent gets
6 physical reads
0 redo size
694 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SET AUTOTRACE OFF
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS
WHERE OBJECT_ID IN (94044, 94045, 94046);
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- ------------------------------
94044 94044 TBL_INDEX_TEST
94045 94045 IDX_OBJ_ID
94046 94046 IDX_OBJ_ID_NAME
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 331 data block scur 000000007AFE6BF0
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94045
old 9: where objd = &data_obj_id
new 9: where objd = 94045
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 363 data block scur 000000007ABDB548
6 364 data block scur 000000007ABE01D8
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94046
old 9: where objd = &data_obj_id
new 9: where objd = 94046
no rows selected
Select query Using index when get blocks / read only index segment:
Data object ID for corresponding table and index.
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS
WHERE OBJECT_ID IN (94044, 94045, 94046);
DATA_OBJECT_ID OBJECT_ID OBJECT_NAME
-------------- ---------- ------------------------------
94044 94044 TBL_INDEX_TEST
94045 94045 IDX_OBJ_ID
94046 94046 IDX_OBJ_ID_NAME
No blocks for these table or index is in Buffer Cache.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94045
old 9: where objd = &data_obj_id
new 9: where objd = 94045
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94046
old 9: where objd = &data_obj_id
new 9: where objd = 94046
no rows selected
Executed a select query with two columns (OBJECT_ID, OBJECT_NAME) and there is an index on this table with these columns. So the query read only index segment and didn’t touch the table segment.
SQL> SET AUTOTRACE ON
SQL> SELECT OBJECT_ID, OBJECT_NAME FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=&OBJECT_ID;
Enter value for object_id: 2
old 1: SELECT OBJECT_ID, OBJECT_NAME FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=&OBJECT_ID
new 1: SELECT OBJECT_ID, OBJECT_NAME FROM SAMAD.TBL_INDEX_TEST WHERE OBJECT_ID=2
OBJECT_ID OBJECT_NAME
---------- ------------------------------
2 C_OBJ#
Execution Plan
----------------------------------------------------------
Plan hash value: 438204577
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_OBJ_ID_NAME | 1 | 21 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
80 recursive calls
0 db block gets
82 consistent gets
10 physical reads
0 redo size
623 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
After executed above query, we see index segment (IDX_OBJ_ID_NAME) related blocks are in buffer cache means all data has been retrieved from datafile.
SQL> SET AUTOTRACE OFF
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94044
old 9: where objd = &data_obj_id
new 9: where objd = 94044
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94045
old 9: where objd = &data_obj_id
new 9: where objd = 94045
no rows selected
SQL> @obj_buffer.sql
Enter value for data_obj_id: 94046
old 9: where objd = &data_obj_id
new 9: where objd = 94046
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 371 data block scur 000000007ABF7E78
6 372 data block scur 000000007A7DA5F8
6 373 unused scur 000000007ABE3428
6 374 unused scur 000000007ABDE220
6 375 unused scur 000000007ABE7398
