Performance in Oracle DB

It is implemented in real time project. One of my colleagues has followed these rules to improve the performance of the oracle query. Use it and give your comments on the same.

Do and Don’t to increase performance in Oracle Database

a)   Avoid using  IN and NOT IN

Instead using IN or NOT IN you can use EXISTS or NOT EXISTS in subquery.EXISTS or NOT EXISTS returns Boolean to the main query.

b)   Avoid using DISTINCT

Use distinct, if essentially required. Before using DISTINCT function check written sql’s joins and try to include all possible join conditions in where clause, this way you can remove duplication.

c)   Use proper brackets.

In join conditions if multiple join conditions are present on same column then put whole condition into the round brackets.

Eg. (tab1.a=tab2.a OR tab2.a IS NULL)

d)   Proper use of large table.

Think a twice before applying any logic on the large tables (ex : contains data more than 100 thousands) ,table like ETL_SUPPLY_CHAIN,ETL_PRODUCT_MASTER.

Write all possible join for these tables.

If you need single column from these large tables, try to update it at the end instead of using it in SELECT statement using joins, for example columns are like Product category, Business Segment etc. are candidate for these types of conditions.

  

E.g.

UPDATE tab1 a

SET a.cat = (SELECT product_cat FROM tab2 b    

                WHERE a.col1=b.col1

               AND a.col2 = b.col2)

WHERE

EXISTS (       SELECT 1 FROM tab2 b       

                WHERE a.col1=b.col1

               AND a.col2 = b.col2)      ;

Avoid writing SELECT to get category information (single field) like below.

SELECT col1,col2,col3,cat

FROM tab1 a, tab2 b

WHERE a.col1=b.col1

               AND a.col2 = b.col2;

e)   INDEX

Create index on column which are frequently use in sql’s WHERE clause.

f)    SQL HINTS

If you are creating temporary table then use NOLOGGING hint in create statement. This way you can avoid the database logging for the temporary tables, which is absolutely not required. Also use /*+ append */ hint when you are inserting data into the same temporary table.

g)   CORRELATED DML SQL:

When you are using correlated update statement, include exists clause for the filtering of the data.

Eg. Update tab1 a

Set a.col1 = (select b.col1 from tab2 b

                   Where a.col2=b.col2

                   And a.col3= b.col3)

Where exists (select 1 from tab2 b

                   Where a.col2=b.col2

                   And a.col3= b.col3);

Since we have used EXISTS in condition, the above statement first filter out set of data and then updates the records.

h)   Avoid use of UNION

Try to use UNION ALL instead of UNION in sql SELECT statement. Since UNION removes duplicates from the fetched records, it takes comparatively more time than UNION ALL.

i)     Avoid using SQL function directly on any column in WHERE clause

Avoid use of string conversion function or single row function on any column in WHERE clause. Since we are using function on column, the respective column index will not remain useful.

If we want to use it ,then we need to create new function based index, so again performance will get impact due to presence of more indexes on the same table.

Eg. Upper (currency_code) = Currency_code

    Substr(col1,1,5)=col2

j)    Use TRUNCATE

Use TRUNCATE instead of DELETE statement if deleting whole record from table. Truncate release memory space. Also removes high watermark and retain pointer to first row of the table for new or next insertion.

k)   Avoid Creation of temporary tables in big scripts.

When you need to create temporary table in your procedures/scripts for transformation for this use Oracle Global Temporary Table. Instead of creating new table each time and drop it in the end of the procedure. If we create new table every time before execution and drop it before end ,we cannot gather table statistics for that table.

l)     Create Synonym where ever required.

When we create model related objects like rowsource, dimension etc.. and if we are using its base tables(which are created by model while creating) frequently and for this if we need to write query to get its base name every time, instead of writing query every time create synonym in initial stage so that we can use it everywhere in coding.

m) Create Materialized view instead of view.

For large/complex sql queries or data retrieval create materialized view instead of creating view.

If we create view then again for retrieving the data/records optimizer goes to the table whereas for Materialized view optimizer selects data from materialized view itself, since it stores the data with certain interval or manual (dbms_view.refresh (mvname)) refreshes.