SQL tips for Developer

Tips 1:

Try to avoid the * (asterisk) from select query.

When you fire the following commands:

Select * from employee

Database server needs that much space in its buffer cache (in RAM) for holding that amount of data. It will affect the performance of database if table is large. More work need to be done by Database Background process for make space in buffer cache. So try to use column name instead of *.

Tips 2:

Always uses bind variable in SQL queries instead of literals value.

Select * from employee where emp_no =: Bind Variable;

Database Server need to parse queries every time for the same syntax queries due to literal value are different. To avoid parsing again for same SQL Statement better to use bind variable.

Tip 3:

How to check Execution plan in Oracle:

Display Execution Plan in Oracle

EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.

DBMS_XPLAN package: is a function which can display as little (high-level) or as much (low-level) details.

It’s having following parameter for high level or low level details

Explain plan for select empno, ename, dname from emp a, dept b where a.deptno = b.deptno;

Select plan_table_output from table(dbms_xplan.display('plan_table'));

TIP 4:

Below is the Snapshot of Execution Plan of Queries:

Select * from K where owner = 'SCOTT';

IF your table is using full access scan then its means the index is missing on the join condition column or where clause column, for make it better you can create index on column used in where clause.

Create index K_OWNER_IDX on K(owner);

And Execute the Same Query, It show the cost is very low with index.

Tip 5:

Avoid commit Operation in loop statement for loading bulk data modules

On commit operation, database background process writes everything on data files and redo file which causes waiting event for a sync write to the file system every time which cause the performance issue. Example: if you want to make a procedure for inserting bulk data into database. So, does not use commit operation on loop statement below the insert/update operation for each row use it after 10000 or 5000 rows inserted/updated.

Tip 6:

Do not use database triggers as code.

Do not write trigger on the database, it causes problem in replication techniques and make the coding complex.  Oracle also recommends for not to use triggers as code in Database.

Tip 7:

Create index in sequence of where clause column used.

Always try to create index on column as same sequence used in where clause and vice versa.

Example: I Created Index on for following SQL Queries:

First Query: Select * from k where object_type=’data’ and owner=’IC’ and object_name=’TRAN’>

Then create following index according to column sequence wise:

Create index k_object_idx on k(object_type,owner,object_name);

It will use the index when we execute the select command.

If you have another query which uses the one column in where clause

Select * from k where owner='IC'

If you modified the first query according to use owner as first column and create index according to that sequence then only one index will work for both upper SQL Queries.

Select * from k where owner ='IC' and object_type ='TABLE' and object_name = 'K';

Create index k_owner on k(owner,object_type,object_name)

Select * from k where owner = 'IC';

Note:  Now days optimizer are too smart that it will automatically modified the statement internal and save execution plan for lower cost for SQL Statements

 Tip 8:

Primary key and Unique Key automatically create index on columns included. They will help your database to perform better when querying. Try to create Primary key and unique key on that column in application which has maximum utilization in where clause.

 Tip 9:

Use User Defined types in Programming.

Suppose if you use a Variable with system datatype in every part of coding like Pl/SQL packages, Procedure and Function. So if you want to increase its length then you need to modify all packages, Procedure and function coding. So always try to use user defined data types in coding for better flexibility.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s