PT - Table Access Examples while executing queries

Table Access Examples  (plus trace already enabled)

1. FULL TABLE  SCAN
2. INDEX UNIQUE SCAN
3. INDEX RANGE SCAN
4. INDEX RANGE SCAN DESCENDING
5. INDEX FULL SCAN
6. INDEX FAST FULL SCAN

C:\Users\varunyadav>sqlplus soe/soe@smrplm

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 30 20:02:44 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Mar 30 2020 18:20:13 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> sho user
USER is "SOE"
SQL> SET LINESIZE 180
SQL> SET AUTOT TRACE EXP  (Already enabled plustrace)


###################### FULL TABLE  SCAN #########################

SQL> SELECT * FROM WAREHOUSES;

Execution Plan
----------------------------------------------------------
Plan hash value: 3585052864

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1000 | 27000 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| WAREHOUSES |  1000 | 27000 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL>

###################### INDEX UNIQUE SCAN #########################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2784191864

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WAREHOUSES    |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | WAREHOUSES_PK |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("WAREHOUSE_ID"=10)

SQL>


################### INDEX RANGE SCAN ##############################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID>180;

Execution Plan
----------------------------------------------------------
Plan hash value: 3585052864

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   821 | 22167 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WAREHOUSES |   821 | 22167 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("WAREHOUSE_ID">180)

SQL> SELECT * FROM WAREHOUSES WHERE LOCATION_ID=7244;

Execution Plan
----------------------------------------------------------
Plan hash value: 3486536519

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| WAREHOUSES      |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | WHS_LOCATION_IX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LOCATION_ID"=7244)

SQL>

################# "INDEX RANGE SCAN DESCENDING" ######################

SQL> SELECT * FROM WAREHOUSES WHERE WAREHOUSE_ID>180 ORDER BY WAREHOUSE_ID DESC;

Execution Plan
----------------------------------------------------------
Plan hash value: 2083944140

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   821 | 22167 |     5  (20)| 00:00:01 |
|   1 |  SORT ORDER BY     |            |   821 | 22167 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| WAREHOUSES |   821 | 22167 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("WAREHOUSE_ID">180)

SQL>

################## "INDEX FULL SCAN" ###############################

SQL> SELECT DEPT_NO, DNAME FROM DEPT ORDER BY DEPT_NO;

Execution Plan
----------------------------------------------------------
Plan hash value: 1726130703

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     7 |    91 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

SQL>

################## INDEX FAST FULL SCAN ##################################

SQL> SELECT WAREHOUSE_ID FROM WAREHOUSES WHERE WAREHOUSE_ID>180;

Execution Plan

----------------------------------------------------------

Plan hash value: 2133609682

----------------------------------------------------------------------------------------------------------------------

| Id  | Operation               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

----------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |               |   821 |  3284 |     2   (0)| 00:00:01 |        |   |       |

|   1 |  PX COORDINATOR         |               |       |       |            |          |        |   |       |

|   2 |   PX SEND QC (RANDOM)   | :TQ10000      |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR    |               |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |

|*  4 |     INDEX FAST FULL SCAN| WAREHOUSES_PK |   821 |  3284 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |

----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   4 - filter("WAREHOUSE_ID">180)

SQL>