tcl transaction control language 1...7 dept: ce adbms (3340701) prof. vishal k. makwana 2) level of...

21
Unit 1 - Advanced SQL 1 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana This chapter describes three important aspects of the relational database management system Transactional Control, Data Control and Locks (Concurrent Access). Transactional Control provides way to save the successful work permanently or to undo the mistakes. Data Control provides way to restrict users from performing particular operations on specific tables. Lock provides concurrent access in proper way to prevent any kind of anomaly. TCL Transaction Control Language Definition: A Transaction is a set of database operations that performs a particular task. A transaction must be completely successful or completely fail without doing anything to maintain database consistency. Example, Successfully Complete Transaction: A task, say fund transfer from one account to another account. To complete a transaction needs to update balance in two accounts source and destination. It requires two database operations: one, to debit an amount in source account and to credit that amount in destination account. Example, Fail Transaction: In a fund transfer, if a system failure occurs after debiting amount from source account, but before crediting it to destination account then database will be in inconsistent state. So, balance should be updated in both the accounts in both accounts or not in anyone. We can say that a transaction is considered as a sequence of database operations. These operations involve various data manipulation operations such as insert, update and delete. These operations are performed in two steps: 1) Changes are made in memory only. 2) Changes are permanently saved to hard disk. A transaction begins with the execution of first SQL statement after a COMMIT and can be undone using ROLLBACK command. A transaction can be closed by using COMMIT or ROLLBACK command. When a transaction is closed, all the locks acquired during that transaction are released. TCL commands are used to manage transactions, that are given below: 1) Commit 2) Rollback 3) Savepoint COMMIT: Committing a Transaction There are two ways to commit a transaction: 1) Explicitly 2) Implicitly 1) Explicit Commit: o To commit a transaction explicitly, user needs to request COMMIT command explicitly.

Upload: others

Post on 08-Oct-2020

1 views

Category:

Documents


0 download

TRANSCRIPT

Unit 1 - Advanced SQL

1 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

This chapter describes three important aspects of the relational database management system –

Transactional Control, Data Control and Locks (Concurrent Access).

Transactional Control provides way to save the successful work permanently or to undo the mistakes.

Data Control provides way to restrict users from performing particular operations on specific tables.

Lock provides concurrent access in proper way to prevent any kind of anomaly.

TCL – Transaction Control Language

Definition: A Transaction is a set of database operations that performs a particular task.

A transaction must be completely successful or completely fail without doing anything to maintain

database consistency.

Example, Successfully Complete Transaction: A task, say fund transfer from one account to another

account.

To complete a transaction needs to update balance in two accounts – source and destination.

It requires two database operations: one, to debit an amount in source account and to credit that

amount in destination account.

Example, Fail Transaction: In a fund transfer, if a system failure occurs after debiting amount from source

account, but before crediting it to destination account then database will be in inconsistent state.

So, balance should be updated in both the accounts in both accounts or not in anyone.

We can say that a transaction is considered as a sequence of database operations.

These operations involve various data manipulation operations such as insert, update and delete.

These operations are performed in two steps:

1) Changes are made in memory only.

2) Changes are permanently saved to hard disk.

A transaction begins with the execution of first SQL statement after a COMMIT and can be undone using

ROLLBACK command.

A transaction can be closed by using COMMIT or ROLLBACK command. When a transaction is closed, all

the locks acquired during that transaction are released.

TCL commands are used to manage transactions, that are given below:

1) Commit

2) Rollback

3) Savepoint

COMMIT: Committing a Transaction

There are two ways to commit a transaction:

1) Explicitly

2) Implicitly

1) Explicit Commit:

o To commit a transaction explicitly, user needs to request COMMIT command explicitly.

Unit 1 - Advanced SQL

2 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

o A COMMIT command terminates the current transaction and makes all the changes permanent.

o Various data manipulation operations such as insert, update and delete are not effect permanently

until they are committed.

Syntax:

COMMIT;

Output:

Commit complete

2) Implicit Commit:

o There are some operations which forces a COMMIT to occur automatically, even user don’t specify

the COMMIT command.

o Some of commands are given below:

1. Quit Command:

To end SQL*PLUS session disconnecting from the Oracle.

2. Exit Command:

To end SQL*PLUS session disconnecting from the Oracle.

3. Data Definition Language(DDL) commands:

Commands like CREATE.., ALTER.., DROP.. are immediate and makes all prior changes, made

during current transaction permanent.

ROLLBACK: Canceling a Transaction Completely

A transaction can be cancelled using ROLLBACK command either completely or partially.

A ROLLBACK command terminates the current transaction and undone any changes made during the

transaction.

Oracle also performs auto rollback. In situation like, Computer failure, Oracle automatically rollbacks any

uncommitted work, when the database bought back next time.

SAVEPOINT: Cancelling a Transaction Partially

A ROLLBACK command can also used to terminate the current transaction partially.

Syntax:

ROLLBACK TO SAVEPOINT savepoint_name;

Output:

Rollback Complete.

It is required to create a savepoint to cancel transaction partially.

A savepoint marks and save the current point in the processing of a transaction.

A savepoint can be created using command SAVEPOINT as given below:

Syntax:

SAVEPOINT savepoint_name;

Output: Savepoint created.

Unit 1 - Advanced SQL

3 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

When a ROLLBACK is used with SAVEPOINT, part of the transaction is cancelled.

All the operations performed after creating a savepoint are undone.

It is also possible to create more than one savepoint within a single transaction.

DCL – Data Control Language

Security of information stored in database is one of the prime concerns for any database management

system.

An unauthorized access to a database must be prevented.

The rights allow the user to use database contents are called privileges.

Oracle provides security to database contents in two phases:

o User requiring an access to database must have valid user id and password to get login in the system.

o User must have privileges to access contents of the database.

In a multi-user system, different user needs to access different parts of the database.

The database designer determines which user needs access to which part of the database.

According to this, various privileges are granted to different users.

Example: In a banking system, customers need to access information about their own account only.

So, they should have access to view information about their own account. But they should not allow to

modify any information.

Any customer must not be allowed to change balance of an account. Also, they should be prevented from

accessing accounts of other customers.

A user owns the database objects, such as tables, created by his/her self. If any user needs to access

objects belonging to other user then the owner of the object requires to give permission for such access.

Oracle provides two commands- GRANT and REVOKE. To control the access of various database objects.

GRANT – Granting Privileges

GRANT command is used to granting privileges means to give permission to some user to access database

object or a part of a database object.

This command provides various types of access to database object such as tables, views and sequences.

Syntax:

GRANT object privileges

ON object name

TO user name

[ WITH GRANT OPTION ];

The owner of a database object can grant all privileges or specific privileges to other users.

The WITH GRANT OPTION allows the grantee. User to which privilege is granted to in turn grant object

privilege to other users.

User can grant all or specific privileges owned by him/her.

The table, given in below illustrates various object privileges.

Unit 1 - Advanced SQL

4 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Privilege Allows user…

ALL To perform all the operation listed below.

ALTER To change the table structure using ALTER command.

DELETE To delete records from the table using DELETE command.

INDEX To create an index on the table using CREATE INDEX command.

INSERT To insert records into the table using INSERT INTO command.

REFERENCES To reference table while creating foreign keys.

SELECT To query the table using SELECT command.

UPDATE To modify the records in the table using UPDATE command.

Example: Consider three users – user1, user2 and user3 and a table - Customer is owned by user1. As a

user1, grant user2 all the data manipulation permission on the table Customer, with allowing him to pass

privileges to other users.

Input:

GRANT ALL

ON Customer

TO user2

WITH GRANT OPTION;

Output:

Grant Succeeded.

Observe the use of WITH GRANT OPTION. If this option is not specified, user2 will have privileges, but he

cannot grant these privileges to other users.

Example: As a user2, grant select and insert privileges to user3.

Input:

Grant SELECT, INSERT

ON user1.Customer

TO user3;

Output:

Grant Succeeded.

In above example, the use of ‘owner_name.object_name’ with ON clause.

Example: As a user2, display all the information stored in Customer table.

Input: SELECT * from user1.Customer;

Unit 1 - Advanced SQL

5 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

To perform any operation on Customer table, user2 needs to suffix owner name, i.e user1, with Customer

table.

REVOKE – Revoking Privileges

Revoking privileges means to deny (decline) permission to user given previously.

The owner on an object can revoke privileges granted to another user. A user of the object, who is not an

owner, but has been granted privileges using WITH GRANT OPTION, can revoke the privilege from the

grantee.

Syntax:

REVOKE object privileges

ON object name

FROM user name;

Example: As a user2, revoke the select and insert privileges from user3.

Input:

Revoke SELECT, INSERT

ON user1.Customer

FROM user3;

Output:

Revoke succeeded.

Here user1 is owner of the Customer table.

Locks

A transaction is a set of database operation that performs a particular task.

A transaction may involve single SQL statement known as Single Query Transaction (SQT).

A transaction may involve multiple SQL statement known as Multiple Query Transaction (MQT).

For any database system, it is necessary to allow multiple users to access data simultaneously

(concurrently).

When multiple users are accessing data concurrently, it is difficult to ensure data integrity. Such kind of

access may result in concurrent access anomaly (irregularly).

The technique used to protect data when multiple users are accessing it concurrently is called

Concurrency Control.

Oracle uses a method called ‘locking’ to implement Concurrency Control.

Locking means to restrict other users from accessing data temporarily.

Lock also can be defined as a mechanism used to ensure data integrity while allowing maximum

concurrent access to data.

There are two type of locking:

o Implicit Locking

o Explicit Locking

Unit 1 - Advanced SQL

6 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Implicit Locking

Data in a table are locked automatically while executing SQL statements. This does not require any user

interference. Such types of locks are called Implicit Locks.

While applying lock on data, Oracle determines two issues:

1. Type of lock

2. Level of lock

Various types and levels of locks are described below:

1) Types of Locks:

o Oracle uses two different types of locks: Shared Lock and Exclusive Lock

Shared Locks:

o Shared locks are applied while performing read operations.

o Read operation allow to view data, mostly using SELECT statement.

o Multiple shared locks can be placed simultaneously on a table or other object.

o As read operation does not modify table data. So multiple read operations can be performed

simultaneously without causing any problem in data integrity.

o This means, multiple users can simultaneously read the same data.

Exclusive Locks:

o Exclusive locks are applied while performing write operations.

o Write operation allow to modify data using INSERT, UPDATE or DELETE statement.

o Only one exclusive lock can be placed on a table or other object.

o As write operation modifies table data, multiple write operations can affect the data integrity and

result in inconsistent database.

o This means, multiple users cannot modify the same data simultaneously.

Deadlocks

o A set of transaction is deadlocked, if each transaction in the set is waiting for a lock held by some

other transaction in the set.

o Here, each transaction is waiting to acquire some lock. But none of them will release any lock, as

they all are waiting to acquire locks.

o So, all the transactions will continue to wait forever.

o A process with two or more threads can deadlock when the following conditions hold:

Transactions that are already holding locks request new locks.

The requests for new locks are made concurrently.

Two or more transaction form a circular chain in which each transaction waits for a lock which

is held by the next transaction in the chain.

o Example: Transaction – A has acquired lock on X and is waiting to acquire lock on Y. While,

Transaction – B has acquire lock on Y and is waiting to acquire lock on X. But none of them can

execute further.

Unit 1 - Advanced SQL

7 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

2) Level of Locks:

o Multiple users may need to access different parts of the same table.

o For example, manager of ‘xyz’ branch may need to access only those accounts belonging to ‘xyz’

branch while other manager may have interest in other accounts.

o So, if entire table is locked by single user then others need to wait even though they have to access

other part of the table.

o To solve this problem and to allow maximum possible concurrent access, locks should be placed on

a part of the table or on entire table depending upon the requirement.

o So, if one customer is accessing its own account, other customers can access their own accounts.

o Oracle provides three different levels to place an implicit lock.

o These levels are determined depending upon the WHERE clause used in SQL statement.

Row Level Lock:

o This lock is used when a condition given in WHERE clause evaluate a single row.

Example, ….WHERE Student_ID = 1;

o In this case, only single row (record) is locked. Other record of the table can be accessed by other

users.

Page Level:

o This lock is used when a condition given in WHERE clause evaluate a set of rows.

Example, ...WHERE B_name = ‘CE’;

o In this case, only a particular set of rows are locked. Other records of the table can be accessed by

other users.

Table Level:

o This lock is used when a SQL statement does not contain WHERE clause.

o In this case, a query accesses entire table. So entire table is locked. Due to this reason, No any

other user can access other part of the table.

In implicit locking, a shared lock on the table is permitted even though the exclusive lock is placed on the

same table.

This means, if some user is modifying the contents of the table, other user can still read those contents

but they getting older data.

Example:

o Consider that one user has updated balance for some accounts in Account table, but has not save the

changes permanently using COMMIT command.

o Now, if other user reads the contents of the table, the result will be the older data without updated

balance.

o The reason is that change is not permanent on hard disk yet.

To solve this kind of problem, an explicit lock can be used.

Unit 1 - Advanced SQL

8 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Explicit Locks

User can lock data in a table on its own instead of automatic locking provided by Oracle. These types of

locks are called Explicit locks.

An owner of a table can place an explicit lock on the table. Some other users can also place an explicit lock

if they have privilege.

An explicit lock always overrides the implicit locks placed by oracle on its own.

An entire table or records of the table can be explicitly locked by using one of these two commands:

1) The SELECT …… FOR UPDATE Statement

Syntax: SELECT * FROM tableName FOR UPDATE [ NOWAIT ];

o This statement is used to acquire exclusive locks for performing updates on records.

o Based on WHERE clause used with SELECT statement level of lock will be applied.

o If table is already locked by other user then this command simply waits until that lock is released.

o But, if NOWAIT is specified and table is not free, this command will return with an error message

indicates “Resource is Busy”.

o Lock will be released on executing COMMIT or ROLLBACK.

o Other clauses such as DISTINCT, ORDER BY, GROUP BY and set operation cannot be used here with

SELECT statement.

Example: As a user1, place an exclusive lock on accounts of ‘XYZ’ branch using SELECT…FOR UPDATE

statement.

SELECT * FROM Account WHERE B_name = ‘XYZ’ FOR UPDATE;

Example: As a user2, update balance to 1000 for all accounts of ‘XYZ’ branch.

UPDATE Account SET balance = 1000 WHERE B_name = ‘XYZ’;

o In this case, user2 has to wait until user1 releases lock by using COMMIT or ROLLBACK.

2) The LOCK TABLE Statement

Syntax:

LOCK TABLE tableName

IN lockMode MODE [ NOWAIT ];

o This statement is used to acquire lock in one of the several specified modes of a given table.

o If NOWAIT is specified and table is not free, this command will return with an error message indicates

“Resource is Busy”.

o Various modes of lock are described below:

MODE Specifies…

EXCLUSIVE Allows query on a table, but prohibits any other operation. Other users can only view data of a table.

SHARED Allows concurrent queries, but no update operation is allowed.

ROW SHARED Specifies row level lock. User cannot lock the whole table. Allowing concurrent

Unit 1 - Advanced SQL

9 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

access for all users of the table.

SHARE UPDATE Same as above. Exists for compatibility with older versions.

ROW EXCLUSIVE Similar to ROW SHARE, but prohibit shared locking. So, only one user can access the table at a time.

Example: As a user1, place an exclusive lock on Account table using LOCK TABLE Statement.

LOCK TABLE Account

IN EXCLUSIVE MODE [ NOWAIT];

Example: Update the balance for account ‘A01’ to 1000 as a user1.

UPDATE Account SET balance = 1000 WHERE A_No = ‘A01’;

Now, as a user2 display the contents of the entire table and observe the balance for ‘A01’. It will

show the older balance because user1 does not committed their update operation.

As a user1, COMMIT the update operation and again as user2, display Account table. Now, it will

show the updated balance.

Here, user2 can view data of Account table, because the lock is applied in EXCLUSIVE mode, which

allows other user to view data. But no other operation is allowed.

So, user2 cannot modify the content of the table Account until the lock is released.

Difference Between DBMS and RDBMS

DBMS RDBMS

There are no relationships among tables. The database which stores data in the tables

that have relationships with other tables in the database is called RDBMS.

Data stored in flat file means no relationship with other data.

Data stored in tabular form with relationship exist among the tables.

Normalization process will not be present. Normalization process will not be present to

check database table consistency.

It does not support client server architecture.

It supports client server architecture.

It does not enforce any integrity constraints or security with regards to data manipulation.

It enforces integrity constraints.

It is used for simpler business applications. It is used for more complex applications.

Unit 1 - Advance SQL

10 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Table is considered as one kind of database object in Oracle.

Like as tables, Oracle comes with few other database objects such as views, indexes, sequences, synonyms

etc.

These objects are not used to stored data like as tables.

They are used to enhance performance and provide extra functionalities to manage data in better and

flexible way.

Views are basically used to enforce security in flexible way and to make complex queries easier.

Indexes are used to retrieve data in faster way from the tables.

Views

A view is a virtual or logical table that allows to view or manipulate the parts of the tables.

A view is derived from one or more tables known as base tables.

A view looks like and works similarly to normal tables. But, unlike tables, a view does not have storage

space to store data.

A view is created by a query, i.e. a SELECT statement which uses base tables.

Data for views are extracted from these base tables based on specified query.

A view is dynamic and always reflects the current data of the base tables.

Only definition of view is stored in the database.

When a view is referenced in SQL statement following steps will be followed:

o Its definition is retrieved from database.

o The base tables are opened.

o A query, specified in definition is executed.

o A view is created on top of the base tables.

When any operation is performed on view, it is actually performed on the base table.

For example, any SELECT operation on view displays data from the base table. In a similar way, INSERT,

UPDATE, DELETE operations modify the contents of the base table.

Types of Views

View can be classified into two categories based on which type of operations they allow:

1) Read-only View:

o Allows only SELECT operation, this means user can only view data.

o No INSERT, UPDATE or DELETE operations are allowed. This means contents of base table cannot be

modified.

2) Updateable View:

o Allows SELECT as well as INSERT, UPDATE and DELETE operations. This means contents of the base

tables can be displayed as well as modified.

Creating a View

A view can be created using syntax as given below:

Unit 1 - Advance SQL

11 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Syntax:

CREATE [ OR REPLACE ] VIEW viewName

As SELECT … …. [ WITH READ ONLY ];

This statement creates a view based on query specified in SELECT statement.

OR REPLACE option re-creates the view if it is already existing maintaining the privileges granted to view that is given by viewName.

WITH READ ONLY option creates read-only views. If this option is not provided then by default updatable views are created.

The SELECT statement can include WHERE, ORDER BY, GROUP BY clauses if required.

A view can be created using single base table as well as multiple base tables using joins.

The following examples explain how to create views and how to use them in SQL statements. Consider tables – Account and Branch as given in below figure:

Account Branch

Acc_No Balance B_Name

01 1000 Rjt

A02 4000 Ahmd

A03 3000 Srt

An Account and a Branch table

A view using Single Base Table: Example 1 : Create a view ‘Acc_Rjt’ based on Account table to have only accounts belonging to ‘Rjt’ branch. Input: CREATE VIEW Acc_Rjt AS SELECT * FROM Account WHERE B_Name = ‘Rjt’; Output: View created. o This view can be used like as it is a table having all accounts belonging to ‘Rjt’ branch in any SQL

statement. o It is required to use view name instead of table name. Example 2 : Display all the accounts from Acc_Rjt view. Input: SELECT * FROM Acc_Rjt; Output: Acc_No Balance B_Name -------------------------------------------------------- A01 1000 Rjt

B_Name B_Address

Rjt Kalawad Road, Rajkot

Ahmd Elisbridge,Ahmedabad

Srt Mota Bazaar, Surat

Unit 1 - Advance SQL

12 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

A view using Multiple Base Tables:

Example 3 : Create a view ‘Acc_Branch’ based on Account and Branch table having all the information about accounts and related branches. Input: CREATE VIEW Acc_Branch AS SELECT Acc_No, Balance, Account.B_Name Branch, B_Address FROM Account, Branch WHERE Account.B_Name = Branch.B_Name; Output: View Created. o A B_Name column will be referred as Branch in Acc_Branch view.

Example 4 : Display consistent information about accounts and its branches using Acc_Branch view. Input: SELECT * FROM Acc_Branch; Output: Acc_No Balance Branch B_Address ------------------------------------------------------------------------------------------------ A01 1000 Rjt Kalawad Road, Rajkot A02 4000 Ahmd Elisbridge,Ahmedabad A03 3000 Srt Mota Bazaar, Surat

Alter View

Use the ALTER VIEW statement to explicitly recompile a view that is invalid or to modify view constraints.

You can also use ALTER VIEW to define, modify, or drop view constraints. Example : To recompile the view Acc_Branch.

Syntax: ALTER VIEW Acc_Branch COMPILE;

If Oracle Database encounters no compilation errors while recompiling Acc_Branch, then Acc_Branch becomes valid. If recompiling results in compilation errors, then the database returns an error and Acc_Branch remains invalid.

We can also update view by re-create the view by CREATE / REPLACE VIEW as describe above.

Restriction on View

For updateable view, there are some restrictions.

Any view created to be updatable either using single base table or multiple base table, must not include –

o DISTINCT, GROUP BY and HAVING clause.

o Aggregate functions like MAX, MIN, SUM, AVG, COUNT(*) and COUNT.

o Set operations like UNION, UNION ALL, INTERSECT and MINUS.

o Sub-queries.

Advantages of View

View the data without storing the data into the object.

Unit 1 - Advance SQL

13 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Restricts the view of a table. i.e. can hide some of columns in the tables.

Join two or more tables and show it as one object to user.

Restricts the access of a table so that nobody can insert the rows into the table.

There are two major advantages of views:

1) Flexible enforcement of security

2) Simplification of complex query

1) Flexible enforcement of Security:

o Using views, only limited view of table data can be provided to different users. This helps in enforcing

security in flexible way.

Example:

To allow all branch managers to access only those accounts and customers belonging to their branch

only.

For that there must be separate tables for each branch. But this scenario results in data redundancy

and complex database design which is difficult to manage.

To solve this problem, instead of create separate tables for branch, separate view can be created on

the base table and privilege can be given on these views rather than entire base table.

A view ‘Acc_Rjt’ is created in example-1 having information regarding all the accounts belonging to

‘Rjt’ branch.

The following example grants all privileges to manage of ‘Rjt’ branch named ‘Rjt_Manager’ on this

view.

Example 5 : Grant all the data manipulation permission to ‘Rjt_Manager’ on the view ‘Acc_Rjt’ allowing

him to pass privilege to other users.

Input:

Grant ALL ON Acc_Rjt TO Rjt_Manager WITH GRANT OPTION; Output: Grant Succeeded.

2) Simplification of complex Query: o Complex queries can be simplified.

Example: To retrieve name of branch and address of account ‘A01’, a simple query can be fired on the view

‘Acc_Branch’ created in example - 3.

Example 6 : Display branch name and address for account ‘A01’. Input:

SELECT Branch, B_Address From Acc_Branch WHERE Acc_No = ‘A01’;

Unit 1 - Advance SQL

14 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Output: Branch B_Address ------------------------------------------------------

Rjt Kalawad Road, Rajkot Here, Acc_Branch is created by joining Account and Branch tables, but it is acts just like a separate

table having combined information from Account and Branch tables.

Disadvantages of Views

Cannot use DML operations on view.

When table is dropped view becomes inactive.

View is an object, so it occupies space.

Destroying a View

The DROP VIEW command drops the specified view.

The base table will not be affected if a view is destroyed.

If a base table is dropped or column included in view are altered then view will not be valid further.

Oracle issues an error message while using such in-valid views.

Syntax:

DROP VIEW viewName;

Example 7 : Drop the view Acc_Branch.

Input: DROP VIEW Acc_Branch;

Output: View Dropped.

Indexes

Search is always efficient when data to be searched is sorted in some specific order such as in ascending

order.

If records are not sorted then any query fired on a table to search sequentially testing values of all records

one by one.

The following figure describes this situation. Here a customer table is unsorted and searched for a record of

customer ‘Sophia’. So we need to search sequentially all records one by one.

Customer

Name CID Address City

Jack C01 Corporation Street Manchester

Emily C02 Council Area Perth

Sophia C05 Corporation Street Manchester

Scott C03 Sardar Colony Anand

So, to make search efficient on table, data in a table need to be kept sorted.

But, this requires sorting entire table on each insert, update and delete operation.

Unit 1 - Advance SQL

15 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Also sorting process consumes time, resulting in overall slower and inefficient system.

Also, there may be a need to search on different columns. It may be based on customer name, ID, Address

or City. So, it is not possible to sort table data for each column separately.

Oracle provides an object called INDEX to solve problem mention above and to make searching efficient.

An Index is an ordered list of contents of the column ( or a group of columns ) of a table.

An index is similar to a table. It contains at-least two columns:

1) A column having sorted data on which an index is created.

2) A column representing RowID for each row in a table.

A RowID is a unique identifier for each record inserted in a table.

The following figure illustrates the searching process while using index for a table. Here, Customer table is

searched for a record of customer ‘Sophia’.

In below figure, for simplicity RowID is considered as a three digit number.

Customer

RowID Name CID Address City

001 Jack C01 Kalawad Road

Rajkot

002 Emily C02 C.G. Road Ahmedabad

004 Scott C03 Kamati Baug Vadodara

Whenever a query is fired on table, involving a column on which an index is created and an index consulted

first instead of a table itself.

An index is searched sequentially and RowID of required record is found.

This RowID will give the address of the location where that record is stored in the database and record can

be accessed directly.

Advantage:

As content of the name column is sorted in index, searching process will be faster.

Also index contains only two columns. So, updating index on each insert, update, delete operation on

table will not consume much time.

Disadvantages:

Indexes slow down DML (i.e. inserts, updates and deletes).

Indexes may make your queries slower instead of faster.

A RowID – A Unique Identifier of a Record

A RowID is a unique identifier for each record inserted in a table.

A RowID is a hexadecimal string and contains logical address of the location in a database where a

particular record is stored.

Name RowID

Jack 001

Emily 002

Scott 004

Index

Unit 1 - Advance SQL

16 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Oracle assigns a unique id for each and every record inserted in a table and that is used to create indexes.

Oracle provides a pseudo column, named ROWID, to retrieve RowID associated with records in a table.

ROWID column can be used like any other column in SELECT statement.

The format for RowID can be any of the following:

1) Extended:

o This format is an 18 digit string of the form OOOOOOFFFBBBBBBRRR.

o This format is used by Oracle8i and higher versions.

2) Restricted:

o This format is a 15 digit string separated with dots of the form BBBBBBBB.RRRR.FFF.

o This format is used by Oracle7 and earlier releases.

Meaning of characters given in format strings are as below :

Character Specifies….

OOO… Data object number identifying the database segment.

FFF… Data file number identifying a file containing data.

BBB… Block number in a data file identifying block containing a record.

RRR… Record number in the block.

Records inserted in tables are grouped into blocks. These blocks are grouped and stored into data files.

There can be many data files in system.

Advantage: Once a RowID is found from the index, it indicates the data file, block and the record number.

So, record can be accessed directly.

Thus, the time required to locate the data on the hard disk is reduced.

So, data retrieval time is improved drastically.

Example 8 : Display RowID along with other columns for a Customer table.

Input:

SELECT RowID, Name, CID, Address, City From Customer;

Output:

RowID Name CID Address City

AAAFx7AABAAAKjCAAA Jack C01 Kalawad Road Rajkot

AAAFx7AABAAAKjCAAB Emily C02 C.G. Road Ahmedabad

AAAFx7AABAAAKjCAAC Sophia C05 RMC Chowk Rajkot

AAAFx7AABAAAKjCAAD Scott C03 Kamati Baug Vadodara

Unit 1 - Advance SQL

17 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Types of Indexes

There are four types of indexes:

1) Duplicate Indexes

2) Unique Indexes

3) Simple Indexes

4) Composite Indexes

Difference between Duplicate and Unique indexes as given below:

Duplicate Indexes Unique Indexes

o An index, that allows duplicate values for index column is called a Duplicate Index.

o An index, that does not allows duplicate values for index column is called a Unique Index.

o Duplicate indexes are not created on primary and unique key because it contains duplicate value.

o A unique index is created automatically for a table, if it contains a primary key or unique key.

Simple Indexes:

o An index created on a single column of a table is called a Simple Index.

Composite Indexes:

o An index created on more than one column is called a Composite Index.

Creating an Index

Creating Simple Index:

Syntax: CREATE [UNIQUE] INDEX indexName ON tableName (columnName);

o By default indexes are created as Duplicate Indexes. o If UNIQUE option is provided while creating an index, it will be considered as a unique Index.

Example 9 : Create simple index on ‘name’ column of a Customer table. Input:

CREATE INDEX indCustName ON Customer (Name);

Output: Index Created.

Creating Composite Index:

Syntax: CREATE [UNIQUE] INDEX indexName ON tableName (columnName1, columnName2 );

Unit 1 - Advance SQL

18 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

o If more than one column is provided while creating an index, it will be considered as as Composite Index. Otherwise, indexes are created as Simple Indexes.

o If index is created on more than two columns, other column will be considered only when the previous all columns contain duplicate data.

o In above syntax, second column will be considered only when the first column contains duplicate data. In such case, sorting is performed based on data of the second column.

Multiple Indexes on a Table

It is possible for a table to have more than one index on different columns. A table can have any number of

indexes.

If a table contains more than one index, a particular index is selected based on the column specified in

WHERE or ORDER BY clause. Otherwise, no any index is used for that query.

Multiple indexes on a tables must be created carefully.

Disadvantage:

o Too many indexes may degrade the overall performance. Because, whenever a record in table is

inserted, deleted or updated, indexes created on the table also need to be updated.

o Sorting of contents of index is required time to perform such operation.

o If there are too many indexes, this processing may take a longer time resulting in slower system.

o Thus, Indexes speed up the data retrieval, but they slow down the operations such as insert, update or

delete

o So, indexes should be created only on the columns which are used frequently in data retrieval

operations.

Destroying an Index

Syntax: DROP INDEX indexName;

o This command drops an index given by indexName. o Once an index is dropped, it can be recreated whenever required.

Example 10 : Drop the index ‘ind CustName’ created on Customer table. Input:

DROP INDEX indCustName;

Output: Index Dropped.

Sequences

To distinguish different records of a table from each other, it is required that each record must have distinct

values.

Unit 1 - Advance SQL

19 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

The primary key constrain ensures this by not allowing duplicate or NULL values in columns defined as a

primary key.

Such column generally contain sequential numbers such as 1, 2, 3,… or combination of sequential values

with some strings, such as ‘A01’, ‘A02’,….

While entering data manually in insert or update operations, it is difficult to track such type of sequence.

An Oracle object, a Sequence helps to ease the process of creating unique identifiers for a record in a

database.

A Sequence is simply an automatic counter, which generates sequential numbers whenever required.

A value generated can have maximum 38 digits.

A sequence can be defined for following purpose:

o To generate numbers in ascending or descending order.

o To provide intervals between numbers.

o To caching sequence numbers in memory to speed up their availability.

Creating a Sequence

Syntax: CREATE SEQUENCE sequenceName [ START WITH n

INCREMENT BY n MINVALUE n / NOMINVALUE

MAXVALUE n / NOMAXVALUE CYCLE / NOCYCLE

CACHE n / NOCHACHE ORDER / NOORDER ];

A default sequence created without any options, always start with 1, is in ascending order and values are incremented by 1.

To change default behavior of a sequence, various option are described in figure:

Option Specifies…

START WITH Specifies the first sequence number. Default for ascending sequence the minimum value: 1 Default for descending sequence the maximum value: -1

INCREMENT BY

Specifies the interval between sequence numbers. It can be any positive or negative number but not zero. Default value is 1.

MINVALUE Specifies the sequence minimum value.

NOMINVALUE Specifies a minimum value 1 for ascending sequence and 10-26 for descending sequence.

MAXVALUE Specifies the sequence maximum value.

NOMAXVALUE Specifies a maximum value 1027 for ascending sequence and -1 for descending sequence.

Unit 1 - Advance SQL

20 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

CYCLE Specifies to repeat cycle of generating values after reaching maximum value.

NOCYCLE Specifies that no more numbers can be generated after reaching maximum value.

CACHE Specifies how many values to generate in advance and to keep in memory for faster access. Minimum values is 2 for this option.

NOCHACHE Specifies that no any values will be generated in advance.

ORDER Guarantees that sequence numbers are generated in order. This is only used while using parallel servers in parallel mode.

NOORDER Does not Guarantee that sequence numbers are generated in order while using parallel servers in parallel mode.

NEXTVAL and CURRVAL

Oracle provides two pseudo column – NEXTVAL and CURRVAL.

Once a sequence is created, you can access its values in SQL statements with the CURRVAL pseudo column,

which returns the current value of the sequence.

The NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

These pseudo columns are used with a Sequence name as described below: Syntax:

squenceName.CURRVAL

o Returns the current value of the sequence.

squenceName.NEXTVAL

o Increases the value of the sequence and returns the next value.

Generally the values generated by the Sequence are numerical values.

These values can be combined with string to get required values. Ex. – A01

Example 11 : Create a Sequence which generated number from 1 to 99 in ascending order and stops after generating number 99. Input:

CREATE SEQUENCE mySequence START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 99 NOCYCLE ;

Output: Sequence Created.

Example 12 : Demonstrate how to generate sequential values like ‘A01’ , ‘A02’,… , ‘A99’ using mySequence generated in previous example. Input:

SELECT (‘A’ || LTRIM(To_CHAR(mySequence.NEXTVAL,’00’),’’)) ANO FROM DUAL;

Unit 1 - Advance SQL

21 Dept: CE ADBMS (3340701) Prof. Vishal K. Makwana

Output: ANO

----------------- A01

We can write same kind of query for INSER or UPDATE operation.

Destroying a Sequence

A sequence can be destroyed as described below.

Syntax:

DROP SEQUENCE sequenceName ;

Example 13 : Drop the sequence ‘mySequence’ created in example 11.

Input:

DROP SEQUENCE mySequence;

Output:

Sequence Dropped.

Synonyms

A synonym is an alternative name for database object such as tables, indexes, sequences.

A synonym can be used to hide the actual identity of the object being referenced.

For example, if there is a need to hide name of some particular table, then a synonym can be created to

refer that table, hiding the original name.

Another use of the synonym is to abbreviate (Shorten) the table names, particularly tables from other

users.

For example, user1 can create synonym for Customer table owned by user2. Appropriate privileges must

be granted to a user before the user can use the synonym.

Creating a Synonym:

Syntax:

CREATE SYNONYM synonymName

FOR objectName;

Example 14 : As a user2, create a Synonym for Customer table owned by user1. Input:

CREATE SYNONYM Cust

FOR user1.Customer;

Output:

Synonym Created.

Destroying a Synonym:

Syntax: DROP SYNONYM synonymName;