How Oracle Database Process Select Query Internally

This topic will describe how Oracle works internally with Index and Full Table Scan.

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           1503
SQL> 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 selected
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
         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

Leave a Reply