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;
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?
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
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
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.
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.
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.
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);
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.
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