Check the stats of the SQL Query already executed

First find the sql_id of the query executed in v$sql view

select v_sql.sql_text,v_sql.SQL_ID,v_sql.* 
from v$sql v_sql where 1=1
and v_sql.sql_text like 'select s.rowid,s.* from STUDENT_INFO_TEMP0718 %'
and 1=1;

From the sql_id from the result of above query search that sql_id in v$sql_plan view

select v_sql.sql_text,v_sql.SQL_ID,v_sql.* 
from v$sql v_sql where 1=1
and v_sql.sql_text like 'select s.rowid,s.* from STUDENT_INFO_TEMP0718 %'
and 1=1;

Indexes in Oracle SQL

What is index ?

  • Analogy : Index is a db object that makes a data retrieval faster.
  • Def : Index is a schema object that contains entry of each record of data present in indexed column which provides faster access to the rows of that table.
  • Only create for performance of data retrieval and there is no other advantages
  • Note : Oracle does not index NULL values

How indexes work ?

  • In general, it work just like a textbook index. So that we can directly navigate to that page. Similarly db index work in the similar way but for records.
  • Behind the schemes a BTree is created based on the indexed value and the leaf nodes contains the a very small set of data like (3 to 4) and each data is mapped to a rowid, rowid directly provides the access to the record of that table
    • What is RowId : It is a hexdecimal value used to uniquely represent the entry of the table

Types of Index in Oracle :

  • B-Tree index/Normal
  • BitMap index
  • Reverse key index
  • Function based index (Can be b-tree or bitmap)
  • Composite Index (Can be b-tree or bitmap)
  • Domain index.

How to create Index ?

B-Tree Index is created by default – Recommended for unique values

create index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE);

Bitmap Index – Recommended for values that are less distinct like (10,20,30 or true or false etc)

create bitmap index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE);

Function index – Recommended for values which has functions applied on it

create index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(UPPER(S_NAME));

Reverse key index – To Avoid index block contention

create bitmap index STUDENT_INFO_TEMP0718_INDEX on STUDENT_INFO_TEMP0718(S_AGE) REVERSE;

Types of Scan when you fire a select query in Oracle DB

  • Table Scan – (Checks on each record 1 by 1) hence takes more time
  • Index Scan

How to check if Oracle is using Index Scan or table Scan? Using below command

SET AUTOTRACE ON EXPLAIN --IN SQL PLUS
--F10 in SQL Developer Explain plan

How to find the list of indexes for a given table or columns or its stats?

select * from user_indexes
where 1=1
--and INDEX_NAME=''
and TABLE_NAME='STUDENT_INFO_TEMP0718'
and 1=1;


--List of index on column basis

select * from user_ind_columns where 1=1
and TABLE_NAME='STUDENT_INFO_TEMP0718'
and 1=1;

--Statistic of indexes 

select * from user_ind_statistics where 1=1
and 1=1;

--Function Indexes dependent column

select * from user_ind_expressions WHERE 1=1
and 1=1;

B-Tree Index

What is B-tree index?

  • B-Tree stands for Balanced Tree
  • Most common type of indexes in oracle
  • B-Tree indexes is an ordered list of values divided by ranges
  • By associating keys with a row or range of rows, B-Tree provides excellent retrieval performance.

How data is stored?

The table with rowid
b-tree dividing indexed column into ranges and adding rowid to small ranges
  • When a index is create on a column for example we created index on emp no here
  • So oracle takes all the column data of the column that has to be indexed along with its rowid, it orders the column data and divides it into a group of ranges and stored the rowid along with the ranges into a b-tree
  • When a table is create rowid is automatically assigned to each row

How to find if the index is being used or not?

  • Using explain plan – Can be used to check future plan
  • By checking the stats of the query which is executed in past and checking if index is being used, refer below post – Can be used to check past plan

Check the stats of the SQL Query already executed

  • By monitoring the index – Can be used to check if index is used at runtime

When to create a B-Tree index ?

  • Typically created when the distinct values of a column is very high
  • When distinct value is very low we usually prefer bitmap
  • Cardinality is the term used to check the distinct values
    • Cardinality is the ratio of distinct row by the total records in the table

What types of Scan operations will B-Tree do ?

  • Range Scan
  • Unique Scan
  • Full Scan
  • Full Scan (Min/Max)
  • Fast Full Scan – Combining two index scan
Auto Trace shows the Scan type is Full Scan

In the above example Index is not used since it has a small amount of data and optimizer has ignored the index

Can we create an Index for all the columns

  • We usually create index for only those columns which we use in where clause.
  • In case if you use all the columns are used in the where clause then we would create that table as an index organised table.
  • Index organized table is a table in which stored data in a organized order.

Advantages and Disadvantages of B-Tree index

  • Index increases the performance of select statement.
  • Indexes are nothing but extra segments where the data is stored so over the time when the data size increases the DML operations will take more time to complete since it has to shuffel or indexes in the correct order. Hence create index only on those tables which are used in the where condition.

Bitmap Index

  • Def : A bitmap index is a special kind of database index that uses bitmaps.
  • Bitmap index is recommended
    • when number of distinct values in a column is very less
    • And the number of insert and update operations on the records of that table is very less somethink like read only tables
    • Not recommended for frequent DML tables because the tables will get locked when an update operation is performed on any of the record and it is not yet committed.
Student table
Bitmap index on SResult column of Students table

How to check if bitmap is being used ?

using explain plan or by using v$sql and v$sql_plan

Function Based Index

  • In bitmap index, the column value is not stored directly into the index table but it is passed to a function and then the result of the function value is stored in the index segment.
  • So when you apply a function to a where clause like where upper(ename)=’IT’, the index of ename will not be applied(if index is create on ename is the assumption) here because oracle does not work that way, we have to create a separate index with upper(ename) then only the index will be applied. This is called as a function index.
  • If the index is used or not you can determine by using explain plan.
Function Based index

Advantages and Drawbacks :

  • We can now apply index of the result of functions
  • But on every DML operation this index has to re-iterate the values of all the new values inserted every time so there is a trade off between select statement and DML operation of that column in the table.

Reverse Key index

  • Reverse key index stored the key in a reverse way so that the data in the leaf node is not accumulated but it is distributed evenly. The same BTree is used behind the scenes here.
  • Reverse key index solves the problem of key contention in leaf block
  • In the above example 40% of the records are having marks more than 90 so when we do select a specific record like 92 in the where clause the index will not perform well because b-tree leaf that stores the 90’s data in it will have a lot of data in it and it will require that much amount of time as of full scan without index
  • Below is the screenshot of the BTree that will be created, so as you can imagine that the leaf 76-100 will be crouded.
  • So to fix the index we create reverse key index, i.e we reverse the actual value 100 is taken as 001 and 96 is 69 and 94 is 49 and so on…so after reversing the data, the data is distributed along the B-Tree, screenshot below for reference
  • The disadvantage of doing a reverse index is we cannot do a range scan on these index like marks is greater than 90, since the data is reversed and distributed oracle has to do a set of long calculation to get the value, instead the oracle optimiser goes for a full table scan to get the values faster.
  • The advantage of reverse index is when you have to get the exact value from the set of clustered data in a leaf.

Example : When reverse index scan is not used

Index Montoring

Check if index is used or no :

Benefits and Drawbacks of index ?

Reference :

B-Tree
BitMap Index

Constraints in Oracle SQL

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in an SQL table during creation of table or after creationg using the ALTER TABLE command. The constraints are:

  • NOT NULL – Restricts NULL value from being inserted into a column.
  • CHECK – Verifies that all values in a field satisfy a condition.
  • DEFAULT – Automatically assigns a default value if no value has been specified for the field.
  • UNIQUE – Ensures unique values to be inserted into the field.
  • PRIMARY KEY – Uniquely identifies each record in a table.
  • FOREIGN KEY – Ensures referential integrity for a record in another table.

Not null Constraint

create table temp20210302_0741
(
T_NAME VARCHAR(50) NOT NULL
);

create table temp20210302_0805
(
T_NAME VARCHAR(50)
);

ALTER TABLE temp20210302_0805 MODIFY ( T_NAME NOT NULL);

Check Constraint

create table temp20210302_0807
(
AGE NUMBER,
CITY VARCHAR2(20),
CONSTRAINT AGE_CHECK CHECK(AGE>=18 and CITY='MUMBAI')
);
insert into temp20210302_0807 values (18,'MUMBAI');
insert into temp20210302_0807 values (19,'MUMBAI');
insert into temp20210302_0807 values (15,'DELHI');


insert into temp20210302_0807 values (15,'DELHI')
Error report -
ORA-02290: check constraint (ROOT.AGE_CHECK) violated

Default Constrain

CREATE TABLE Persons20210302 (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

Unique Constrain

CREATE TABLE Persons20210302_0929 (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Primary Key Constraint

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Foreign Key Constraint

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Views in Oracle SQL

  • A VIEW in SQL is a logical subset of data from one or more tables. 
  • View can be used to restrict data access and also to represent the same data in multiple format.
  • We can create a view with read-only option to restrict access to the view.
  • Force option creates a view even if the table is not present.
  • Views will not hold data of its own, instead, it retrieves the data from the base table/view (expect materialised view)
  • Triggers can be created on views

Read only syntax :

CREATE or REPLACE FORCE VIEW view_name AS
    SELECT column_name(s)
    FROM table_name
    WHERE condition WITH read-only;

All views in a database

select * from user_views;

Types of View :

Simple Views

  • Simple view are based of single table.
  • The records are not group. (No grouping are allowed in Simple View).
  • Does not contain a distinct keyword or pseudocolumns or expression based column.
  • DMLs are allowed in Simple Views

Complex Views

  • Can have any numbers of tables joined
  • Records can be grouped. Grouping functions like Min, Max, Count, Sum, Avg can be used
  • Can contain “Distinct” or PSEDUDOCOUMNS and columns based expressions.

Materialized Views

  • In materialised view, the data is actually stored in it(at a physical location) unlike View where the underlying query is executed when the view is called at the runtime.
  • Even if the data from the main table is deleted the data from the materialised view is not deleted, you need to call a refresh to job manually to refresh the view.

BCFN Normal Form

  • When a prime or a non prime attributes start determining the prime attributes of a table then that table is not in BCNF Normal Form

Example

  • R(A, B, C)
  • Functional Dependency
    • AB–>C
    • C–>B
  • Determining candidate keys by checking closure
    • (A)+ = A –> Not a candidate key
    • (AB)+ = ABC –> Candidate key
    • (AC)+ = ABC –> Candidate key
  • Determining Prime and non prime attribute
    • Prime : ABC
    • Non Prime : None
  • If the relation is in 2NF
    • AB determines C but AB is not a part of candidate key but the entire candidate key
    • C determines B but B is not a non prime attribute.
    • So the relation R is in 2NF
  • If the relation is in 3NF
    • by definition when a non prime attributes starts determining other non prime attributes we call it Transitive dependency which is not allowed in 3NF
    • In our case C determines B but both B and C are prime attributes
    • So the relation R is in 3NF
  • If the relation is in BCNF
    • When a prime or a non prime attributes start determining the prime attributes of a table then that table is not in BCNF Normal Form
    • And yes C is a prime attribute that is determining B which is also a prime attribute then relation R is not in BCNF

Reference :

Example 1

Candidates Key for Normalization

Question 1 : Find all the possible candidate keys

Relation (A, B, C, D, E)

  • Below are the functional dependencies
    • A –> C
    • C –> D
    • D –> B
    • E –> F
  • Calculating Closure
    • Items which are not present in right side of Functional dependencies i.e A and E we will start with that, if A and E if not enough to find all the elements of the relation then we will add new attributes to find candidate keys, if A and E by them self can find all the attributes of the relation then we can say that we have only 1 candidate key
    • (AE)+ = AECDBF
    • So AE is the only candidate key for this relation

Question 2 : Find all the possible candidate keys

Relation (A, B , C , D, E, F, G, H)

  • Functional Dependencies
    • CH –> G
    • A –> BC
    • B –> CFH
    • E –> A
    • F –> EG

Calculating Closure

  • Items which are not present in right side of Functional dependencies is D, we will find D close and see if it is a candidate key, if not then we will keep adding one element at a time from the left side of functional dependencies to create candidate keys
  • (D)+ = D –> D Closure Not a candidate key
  • (DA)+ = DABCFHEG –> DA Closure is a candidate key
  • (DB)+ = DBCFHGEA –> DB Closure is a candidate key
  • (DC)+ = DC –> DC Closure not a candidate key
  • (DE)+ = DEA –> Since DA is already a candidate key hence DE closure is a candidate key
  • (DF)+ = DFEG –> Since DE is already a candidate key hence DF closure is a candidate key
  • So all the candidate keys are
    • DA, DB, DE, DF
  • Calculating prime and not prime attributes
    • Prime Attributes : If a attribute is a part of any of the candidate key it is a prime attribute in above example : ABDEF are the prime attributes and CGH are non prime attributes
    • So you want to put the relation in 2NF it would be like
      • R1(A, B, D, E, F)
      • R2(B ,C ,H ,G) SInce B –>CFH and CH–> G

  • By Definition you are part of any of the candidates key then you are a prime-attribute, other attributes which are not part of prime-attribute in a relation you call it a non prime attribute

Reference :

Example 1 (Simple)
Example 2 (Complex)

2NF Normal Form

  • To be in second normal form,
    • A relation must be in first normal form.
    • No non-prime attribute should be partially dependent on Candidate Key(Part of a candidate key and not as a whole).

R( A, B, C, D)

FD = { AB → CD, B → C }

Prime Attributes & Candidate Kets: AB

Non Prime Attributes : CD

So the 2NF of Relation R will be

R1(ABD) and R2(BC)

Reference :

Example1
Example2
Example3 – Part of a candidate key is to be considered for partial dependency