Technorama

This post is about the SQL best practices that I have come across:

Make a note of the indexes or primary keys declared for a table. Using these in a WHERE clause will speed up the operation.

Avoid nested queries as far as possible.

In case of updatable tables, aim for a fair amount of normalization. This will depend on how much data is to be inserted. If there is much data to be inserted, then aim for less normalizing as it slows down the insert.

This has a collection of PL SQL best practices that I have learned .. some times the hard way

When writing stored procedures, always write packages. This helps in better maintainence of the code.

Always write EXCEPTION blocks in any stored procedure. Either throw an error using raise or log the error.

Use the OO concept when deciding on functions and procedures. This will help in debugging and also building the functionality

Use meaningful procedure and function names.

Code SQL key words in UPPERCASE. Rest as lowercase.

Keep in mind looping through complex statements. Very easy to fall in the trap. Try to avoid rerunning of sqls in loop. A query may cause a delay of a fraction of second, but when run a million times, can cause considerable delay !!

Use bulk INSERT/UPDATE statements wherever possible.

If a lot of data has been inserted, follow it up with a ANALYZE table command. This will speed lookup on the table

Use proper naming conventions like l_ for local, i_ for input etc. A duplicate variable can cause unforeseen issues that are hard to debug!!

To connect as sysdba on oracle, do the following:

1. Login to the oracle machine.
2. export (or set) ORACLE_SID = {SID of the database}
3. sqlplus / as sysdba.

Thats all

Dropping a table in Oracle also drops all the constraints, indexes etc associated with the table. So when recreating the table, the other objects must also be created.

An oracle error when Oracle is unable to extend the table in a tablespace.

Solution:
Extend the tablespace size

alter database datafile ‘xyz.dbf’ resize 1000M (The new total size)

Getting the datafile: Go to DBA->Segment management and Tablespaces in TOAD.

Sometimes the system may not have enough space for increasing tablespace file size.

Create a new file and attach it to the tablespace

ALTER TABLESPACE
ADD DATAFILE ‘abc.dbf’ SIZE 100M
AUTOEXTEND ON
NEXT 1024K
MAXSIZE 250M

To get only the execution plan for a query and check for its performance even before executing the query, use the foll command on sqlplus:

set autotrace traceonly explain

This will show the execution plan for the query.

This lists some of the tables that are of good use when looking at data at the session level.
You may have to login as the system user to access some of the tables

1. v$session_longops: Has data regarding operations that are running on the database.
2. v$session: Has data about the session.
3. dba_jobs: Has information about the jobs in the database.
4. dict: Has information about the system tables.
5. dba_source : Has the source for all objects. Useful for finding out which line of your procedure/function is causing errors. The line # in the error message is not the same as in your source code.
6. dba_tablespaces : Has information about the tablespaces in the Database.
7. DBA_TAB_PRIVS: Has information about the grants given to all the objects in the database. Can use this to find out grants given to a user for any object in the database.