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>

PT - SQL Operators and joins

                         SQL Operators: Access Paths and Joins

• Access path: a technique to retrieve rows from a row source
• Full Table Scan 
• Table Access by ROWID 
• Sample Table Scan 
• Index Unique Scan 
• Index Range Scan 
• Index Full Scan 
• Index Fast Full Scan
• Index skip Scan
• Index Join Scan
• Bitmap Index Join Value
• Bitmap Index Range Scan
• Bitmap Merge
• Cluster Scan
• Hash Scan

• JOINS :how the row sources of two steps are joined
• Join Methods: Nested loop, Hash join, Sort merge, Cartesian 
• Join Order: defines which table should be accessed first (outertable or driving table). The other table is the driven-in or inner table. 
• Join Types: InnerJoins, Outer Joins, Semijoins, Antijoins

MS SQL Fill backup and validation check

• 
  
• 
  
• Create Backup database

--Create a test database--

Use master

go

Create database BackupDatabase

go

•  Create table on backup database

use BackupDatabase 

go

--Create table

Create table Products

(ProductID int IDENTITY (1,1) Primary Key,

ProductName varchar (100),

Brand varchar (100))

go

--Insert data into table

insert into Products values ('Bike','Genesis')

insert into Products values ('Hat','Nike')

insert into Products values ('Shoe','Payless')

insert into Products values ('Phone','Apple')

insert into Products values ('Book','Green')

insert into Products values ('Cup','Large')

--View data

select * from Products

• --Take full database backup of backupdatabase

• --Verify that the database backup is valid (not that the data within is valid)

declare @backupSetId as int

select @backupSetId = position 

from msdb..backupset 

where database_name=N'BackupDatabase' 

and backup_set_id=(select max(backup_set_id) 

from msdb..backupset where database_name=N'BackupDatabase' )

if @backupSetId is null 

begin 

raiserror(N'Verify failed. Backup information for database ''BackupDatabase'' not found.', 16, 1) 

end

RESTORE VERIFYONLY 

FROM  

DISK = N'c:\fullbackups\BackupDatabase.bak' 

WITH  FILE = @backupSetId

GO

• 
  

Use msdb

go

SELECT logical_name,physical_name,file_number,backup_size,file_type, * 

FROM dbo.backupfile

ORDER BY 1  -- Contains one row for each data or log file that is backed up 

--SELECT * FROM dbo.backupmediaset -- Contains one row for each backup media set 

--SELECT * FROM dbo.backupset  -- Contains a row for each backup set 

--SELECT * FROM dbo.backupfilegroup -- Contains one row for each filegroup in a database at the time of backup 

• --SELECT * FROM dbo.backupmediafamily  

• --Extented backup script wtih options

BACKUP DATABASE [smrmindev] 

TO  DISK = N'C:\FullBackups\smrmindev.bak' 

WITH 

--NOFORMAT, 

INIT,   --<< Specifies that all backup sets should be overwritten 

NAME = N'smrmindev-Full Database Backup', 

--SKIP, 

--NOREWIND, 

--NOUNLOAD, 

COMPRESSION,        --<< Compresses the database

STATS = 10, CHECKSUM  --<< Specifies that the backup operation will verify each page for checksum and torn page

GO

• --Verifies that the database backup is valid

declare @backupSetId as int

select @backupSetId = position from msdb..backupset

 where database_name=N'smrmindev' and backup_set_id=(select max(backup_set_id) 

 from msdb..backupset 

 where database_name=N'smrmindev' )

if @backupSetId is null 

begin 

raiserror(N'Verify failed. Backup information for database ''smrmindev'' not found.', 16, 1) 

end

RESTORE VERIFYONLY 

FROM  DISK = N'C:\FullBackups\smrmindev.bak' 

WITH  FILE = @backupSetId,  

NOUNLOAD,  

NOREWIND

GO