Oracle Tips And Tricks Part#1

Tips 4 : Join small table first.

Suppose we have three tables A,B,C which can be join each other by any combinations (i,e a join b join c, c join a join b, ... etc).

A has 10 columns and 100 rows, that means 10*100=1000 cells
B has 5 columns and 2000 rows, that means 5*2000=10000 cells
and C has 20 columns had 30 rows, that means 20*30=600 cells

Now When you apply "Left Join"/ "Right Join" on those tables, for achieving optimum performance please join small table fist (if possible for certain case. In our example a,b,c can be join by any combinations) that means

SELECT ..... FROM C Left Join A ON (...) Left Join B ON (...);

Tips 3: Do not use LONG datatype.

LONG datatype is used to store variable length character strings. Oracle has advised, since 8i, that the LONG datatype no longer be used, it has placed in dtatatype set only for backward compatibility. The limitation of LONG datatype are:

1. There is a limit of one column of type LONG per table.
2. Tables containing a LONG cannot be partitioned.
3. LONG datatypes cannot be used in subqueries.
4. Few functions will work with LONG data.

Instead of LONG please use CLOB. The CLOB datatype is the preferred datatype for character data larger than VARCHAR2.

Tips 2 : Place column/columns in last, if there is a possibility to contain null.

Suppose we have a table like this student (id,name not null,address1,address2,address3,age not null). Here you can see, there is a possibility to remain NULL in 3 column (address1,address2,address3). so for a row of this table that have null on those three columns, 3 byte space consume to store the NULL. But if you rearrange the columns like this student (id,name not null,,age not null,address1,address2,address3) then a row of this table that have null on those three columns, 1 byte space consume to store the NULL. Because it took 1 byte to store NULL on column 'address1' and no space needed for columns 'address2' and 'address2'. And again if you have data on 'address3' then again 2 byte space needed to store NULL info :( .

So you need to place the column such a way that all null columns stay in a row to get this space benefit . 1 byte space too small to consider but a large table with 30 columns (10 of then can be null) and have 4 lacks rows, this tricks can be hand full ! :)


Tips 1 : Create index on FOREIGN KEY columns

Suppose we have two Tables:
1. branch(id,name,Address,total_employee).
2. employee(id,name,branch_id,sal).

Here employee.branch_id is a foreign key (referenced by branch.id). Now suppose 50 branches has 20 employees and another 10 employee add in each branch (already inserted into employee table). so we need to update branch.total_employee =20 to 30. If we have no index on employee.branch_id column, update on branch will lock all 50*30=1500 rows of employee table and all DML operations on employee table wait until DML operation completed on branch table. But if we have an index on employee.branch_id column, update on branch will lock only 1*30=30 rows of employee table at a time.

For small database the wait may not bother you but in large database it will bother you significantly.