Precautions while using Clustered and Non-Clustered Indexes on a Table

Precautions while using Clustered and Non-Clustered Indexes on a Table

 

Without an index SQL Server / Oracle has to scan entire tables to return requested data. It is like the index page in a book. You check for the keyword you want to read about in the index and you jump directly to the page where the content belongs, instead of scanning page by page for the material you want to read.

 

Similarly a table index allows you to locate data without the need to scan the entire table. You create indexes on one or more columns in a table to help SQL Server / Oracle find the data quickly in a query.

 

Types of Index

 

1. Clustered Index

 

A clustered index alters the way that the rows are stored. When you create a clustered index on a column (or a number of columns), SQL Server / Oracle sorts the table’s rows by that column(s). It is like a dictionary, where all words are sorted in alphabetical order in the entire book. Since it alters the physical storage of the table, only one clustered index can be created per table.

 

Oracle automatically creates clustered index on primary key column.

 

A clustered index should be used on a column that will be used for sorting. A clustered index is used to sort the rows on disk, so you can only have one per table.

 

2. Non Clustered Index

 

Non-clustered index, on the other hand, does not alter the way the rows are stored in the table. It creates a completely different object within the table that contains the column(s) selected for indexing and a pointer back to the table’s rows containing the data. It is like an index in the last pages of a book, where keywords are sorted and contain the page number to the material of the book for faster reference.

 

Indexes help in Performance Optimization

 

SQL server sorts the indexes efficiently by using a B-tree, which is a tree data structure that allows SQL Server to keep data sorted, to allow searches, sequential access, insertions and deletions in logarithmic amortized time. This methodology minimizes the number of pages accessed to locate the desired index key, therefore resulting an improved performance.

 

Precaution while using Indexes on Table

 

You will not notice performance issues until you have quite a bit of data in your tables. But as your data increases, you must take care of indexes.

 

1. Index should be used on a column that's going to be used (a lot) to search the table. Most important consideration is how much time your queries are taking. If a query doesn't take much time or isn't used very often, it may not be worth adding indexes. The best option is to set your clustered index on the most used unique column, usually the primary key.

 

You should always have a well selected clustered index in your tables, unless a very compelling reason.

 

2. Indexes should not be used for columns or tables that are often updated. Clustered indexes makes SQL Server / Oracle order the rows on disk according to the index order. This implies that if you access data in the order of a clustered index, then the data will be present on disk in the correct order. However if the column(s) that have a clustered index is frequently changed, then the row(s) will move around on disk, causing overhead - which generally is not a good idea.

 

This side effect of indexes is related to the cost of INSERT, UPDATE, MERGE and DELETE statements. Such statements can take longer to execute in the presence of indexes since they alter the data on the table resulting the update of the indexes too. Imagine the situation of an INSERT statement that has to add rows to a table with a clustered index. Table rows may need to be repositioned since clustered index needs to order the data pages themselves thus creating more overhead. So, it is crucial to take into account the overhead of INSERT, UPDATE and DELETE statements before designing your indexing strategy. Although there is an overhead in the above statements, you have to take into account that many times an UPDATE or DELETE statement will have to execute in a subset of data, defined by a WHERE clause, where indexing may outweigh the additional cost of index updates since SQL server has to find the data before updating them.

 

3. Having many indexes is not good either. They cost to maintain. So start out with the obvious ones, and then profile to see which ones you miss and would benefit from. You do not need them from start, they can be added later on.

 

4. Big column datatypes can be used when indexing, but it is better to have small columns indexed than large. Also it is common to create indexes on groups of columns.

 

5. There are also storage considerations. When inserting rows into a table with no clustered index, the rows are stored back to back on the page and updating a row may result in the row being moved to the end of table, leaving empty space and fragmenting the table and indexes.

 

Scan and Seek

 

A table without a clustered-index is called a “heap table”. A heap table has no sorted data thus SQL server has to scan the entire table in order to locate the data in a process called a “scan”.

 

In the case of a clustered index the data are sorted on the key values (columns) of the index. SQL server is now able to locate the data by navigating down from the root node, to the branch and finally to the leaf nodes of the B-tree structure of the index, in a process called a “seek”. The later approach is much faster when you want to filter or sort the data you want to retrieve.

 

Difference between Clustered and Non Clustered Index

 

1. A clustered index actually describes the order in which records are physically stored on the disk, hence the reason you can only have one. A Non-Clustered Index defines a logical order that does not match the physical order on disk.

 

2. There can be only one clustered index on a table while there can be up to 249 non clustered indexes on a table.

 

3. Faster to read than non clustered as data is physically stored in clustered index.

3 Simple and Interesting PLSQL Programs to Explain Triggers

Triggers in PL/SQL are the blocks which are automatically fired before or after any alteration (insert, update, delete) is done to the table. Here are 3 simple programs which will illustrate the concept of triggers very efficiently. Have a look...

Consider the following table named student. It contains roll no, name and marks of each student.

roll          name     marks
20034   SID        69
20035   HARRY 88
20036   TANK    34

1. Program to illustrate the use of BEFORE TRIGGER. This trigger will always enter the name of student in capital letters.

CREATE OR REPLACE TRIGGER capital_name BEFORE INSERT OR UPDATE ON student FOR EACH ROW
BEGIN
:NEW.name := UPPER(:NEW.name);
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

Then instead of ‘Steven’, ‘STEVEN’ is inserted into the table.

2. Program to illustrate the use of BEFORE TRIGGER. This trigger will not allow to do any action with student table on a specified day, here saturday.

CREATE OR REPLACE TRIGGER no_action BEFORE INSERT OR UPDATE OR DELETE ON student FOR EACH ROW
BEGIN
IF(LTRIM(RTRIM(TO_CHAR(SYSDATE,’DAY’)))=’SATURDAY’)
THEN
RAISE_APPLICATION_ERROR(-20998,’Action Denied’);
END IF;
END;

Now if you make the following query to student table:

UPDATE student SET name = ‘Steven’ WHERE roll = 20035;

on saturday. You will get the message ‘Action Denied’. This is very useful feature used to avoid any alteration in sensitive data on weekends when you are not around and anybody else tries to alter it.

3. Program to illustrate the use of AFTER TRIGGER. This trigger will put the altered enteries in a new table named track.

Suppose student table is very critical and crucial. Only you are allowed to alter the enteries. So you can make trigger to track other persons who login with their username and try to alter the table. For this you have to create a table track as follows:

CREATE TABLE track (roll number, name varchar2(40), oldmarks number, newmarks number, uname varchar2(40));

Now create a Trigger as

CREATE OR REPLACE TRIGGER track_action AFTER UPDATE ON student FOR EACH ROW
BEGIN
INSERT INTO track VALUES(:OLD.roll, :OLD.name, :OLD.marks, :NEW.marks, USER);
END;

Now suppose a relative of TANK works in your department and he comes to increase his marks for 34 to 94. He will login from his username and will fire the following query.

UPDATE student SET marks = 94 WHERE roll = 20036;

He will now get delighted that he has altered the table. But he doesn’t know that a secret table named track has strored all the alterations done by him.

Now when you come in morning and fire the following query:

SELECT * FROM track;

Now, there will be one record in this table containing roll as 20036, name as TANK, old marks as 34 and new marks as 94 and most importantly the username of sneaker. So he has been trapped.

3 Very Simple PLSQL Programs to Explain Procedures, Functions and Packages

Here in this tutorial, Procdures, Functions and Packages are fully explored through simple programs. Procedure is a subprogram that performs a given task while Function is same as Procedure but it returns the value. Packages group up Procedures, Functions, Variable, Constants, Cursors and Exceptions.

1. Program to illustrate the use of Procedure. The program is to multiply two numbers. This program is stored in a file name myprocedure.sql

CREATE OR REPLACE PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

Now use SQL>@ myprocedure to create this procedure.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> EXEC product(3,4); It will give output 12.

You can also call the above procedure through a program below:

SQL> ED CALLPRO
DECLARE
a number;
b number;
BEGIN
a:= &a;
b:= &b;
product(a,b);
END

SQL> @ CALLPRO;

2. Program to illustrate the use of Functions. The program is to multiply two numbers. This program is stored in a file name myfunction.sql

CREATE OR REPLACE FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

Now use SQL>@ myfunction to create this function.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.
Now use SQL> SELECT product2(3,4) FROM DUAL; It will give output 12.

You can also call the above function through a program below:

SQL> ED CALLFUN
DECLARE
a number;
b number;
result number;
BEGIN
a:= &a;
b:= &b;
result:= product2(a,b);
DBMS_OUTPUT.PUT_LINE(result);
END

SQL> @ CALLFUN;

3. Program to illustrate the use of Packages. This package name is combine and package specification is stored in file pack and package body is stored in file pack_body.

SQL> ED pack
CREATE OR REPLACE PACKAGE combine AS
PROCEDURE product(a number, b number);
FUNCTION product2(a number, b number) RETURN number;
END combine;

Now use SQL>@ pack to create this package.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make package body in file pack_body.

SQL> ED pack_body
CREATE OR REPLACE PACKAGE BODY combine AS

PROCEDURE product(a number, b number) AS
c number;
BEGIN
c:=a*b;
DBMS_OUTPUT.PUT_LINE(c);
END product;

FUNCTION product2(a number, b number) RETURN number AS
c number;
BEGIN
c:=a*b;
RETURN c;
END product2;

END combine;

Now use SQL>@ pack_body to create this package body.
Now use SQL> SHOW ERRORS; to find out if there is any error. This is the optional step.

Now we will make call to this package as

SQL> EXEC combine.product(3,4);
SQL> SELECT combine.product(3,4) FROM DUAL;

9 Very Simple PLSQL Programs to Explain Control Structures

Here you will find the list of 9 simplest plsql programs fully exploring the control structures. This tutorial covers IF, ELSE, ELSIF, LOOP, EXIT, EXIT WHEN, WHILE, FOR, GOTO and NULL statements. So have a look...

1. Program to insert values in a table student which has two fields student_id and name.

DECLARE
max_id number;
BEGIN
SELECT MAX(student_id) INTO max_id FROM student;
INSERT INTO student (student_id, name) VALUES (max_id + 1, ‘Harry’);
DBMS_OUTPUT.PUT_LINE(’Record Inserted’);
END;

2. Program illustrating the use of IF, ELSE and ELSIF

If Percentage >=80 –> Grade A
If Percentage >=60 –> Grade B
If Percentage >=45 –> Grade C
If Percentage < 45 --> Fail

 

DECLARE
n number;
BEGIN
–Enter percentage between 1 and 100
n:=&n;
IF(n>=1 AND n< =100) THEN
IF(n>=80) THEN
DBMS_OUTPUT.PUT_LINE(’Grade A’);
ELSIF(n>=60 AND n<80)
DBMS_OUTPUT.PUT_LINE(’Grade B’);
ELSIF(n>=45 AND n<60)
DBMS_OUTPUT.PUT_LINE(’Grade C’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Fail’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’Percentage must be between 0 and 100′);
END IF;
END;

3. Program illustrating the use of LOOP with EXIT statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
IF(n>3) THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

4. Program illustrating the use of LOOP with EXIT WHEN statement

DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
EXIT WHEN n>3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

5. Program illustrating the use of WHILE LOOP statement

DECLARE
n number:=0;
BEGIN
WHILE n<3
LOOP
n:=n+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 3

6. Program illustrating the use of FOR LOOP statement

DECLARE

n number:=0;

BEGIN
FOR i IN 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

7. Program illustrating the use of FOR LOOP REVERSE statement

DECLARE
n number:=0;
BEGIN
FOR i IN REVERSE 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;

output: 6

8. Program illustrating the use of GOTO statement

BEGIN
DBMS_OUTPUT.PUT_LINE(’First Line’);
GOTO third;
DBMS_OUTPUT.PUT_LINE(’Second Line’);
< >
DBMS_OUTPUT.PUT_LINE(’Third Line’);
END;

output:
First Line
Third LIne

9. Program illustrating the use of NULL statement

DECLARE
n number:= &n;
BEGIN
IF n MOD 2 = 0 THEN
DBMS_OUTPUT.PUT_LINE(’Number is Even’);
ELSE
NULL;
END IF;
END;

output: If entered no. is even then it will display the message otherwise no message will be displayed.

6 Very Simple PLSQL Programs to Explain Cursors

Cursors in PL/SQL are used to retrieve more than one row at a time. The data that is stored in cursors is known as Active Data Set. These cursors are of two types:

1. Implicit Cursors : predefined cursors
2. Explicit Cursors : user defined cursors

Here are simple 6 programs illustrating the concept of cursors.

1. Program to illustrate the use of attribute SQL%FOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
END;

2. Program to illustrate the use of attribute SQL%NOTFOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
END;

3. Program to illustrate the use of attribute SQL%ROWCOUNT in Implicit Cursor. The Program is to update the salary of each employee by 1000.

BEGIN
UPDATE emp SET emp_sal = emp_sal +1000;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘Records Updated’);
END;

4. Program to illustrate the use of Explicit Cursors. The Program is to display the information of employess (Emp No, Name and Salary) of a given department.

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata;
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;

5. Program to illustrate the use of Explicit Cursors with FOR LOOP. The Program is to display the information of employess of a given department (same as program 4)

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
BEGIN
FOR rec IN empdata
LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_no || rec.emp_name || rec.emp_sal);
END LOOP;
END;

6. Program to illustrate the use of Explicit Cursors with Parameter Passing Concept. The Program is to display the information of employees of a given department (same as program 4)

DECLARE
CURSOR empdata(n number) IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = n;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata(n);
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;