How effectively we can use temporary tables in Oracle?

Hello guys,

This is quite long back I am writing this post!

In between there were many changes in everyone’s life. Perhaps we are standing rigid with the help of technology stuff. As working with one of the most leading investment banking in Africa continent, I am dealing with daily reports that Investment banks share to their reserve/central banks.

In my previous blogs I have written some techniques of how to find long running queries with statpack or awr reports, bulk collects and forall dml’s and so many things.

But this is when you are analyzing with procedures and functions when you are doing production support work. What if you guys are dealing with large query reports(VIEWS) and you have have multiple reports depending on previous reports? Many of oracle developers in different investment banks came across such performance issues.I am pretty sure that you guys will get frustrated if your report structure is too large and you tried to find any of indexes are getting called or not.

Here are some solutions that you can implement.

Here onwards I will refer a VIEW as report in my blog.

1.If your report is having multiple SELECT statements, you need to find what are common tables referred in each SELECT statement.

2.After Identifying this make a temporary table with the help of WITH clause with parent tables as common tables.

3.Use that temporary tables in each SELECT statement instead of repeating same tables again and again in each SELECT query.This approach you can follow if report is having frequent change.

4. If your report is not changing frequently you can create MATERIALIZED VIEW or refer GLOBAL temporary tables instead of WITH clauses.

5. Query rewrite is only option if you guys don’t want to create a mess to big mess! As we all know prevention is better than cure!!

Taking care of temporary tablespace storage all you can take help of DBA’s to increase size of temporary tablespaces.

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j