Hm…sometimes tuning the query just did not work. It did not give a big impact on how long the result will be fetched by the database (Oracle). I noticed, finally, there is something wrong with tables that I use for my query..which eventually leads to the poor performance of my query…hohoho…

First is to make sure that the problem does not come from your query. To make it sure, check wether all join condition have been made using indexed fields. Also, avoid string comparison and ‘IN’ clause. It is faster for database to process numeric value rather than text/string.

The idea of index is that it creates a glossary for each table. It main purpose is to avoid Oracle from doing the full scan..So by using indexes, rather than scan the whole tables in every of its fields (which are very time consuming and block consuming of buffer cache) , it will scan only the index. Finally after it find the desired result, it will go to the table to fetch the query result. Basically, index is like a pointer or just like a glossary in books. Surely, it helps a lot to make the query much much much faster ^^

Secondly, check when is the last time this table has been analyzed..including all of the indexes. Reanalyzing both table and its indexes will enhance the query performance. How big is the impact relies on how fast the data growing in those tables. For tables that growth rapidly and used actively by the application, it is always a good practice to analyze that tables in defined time manner. Honestly, I dont know what analyze table it is..*hahaha (gotcha)..But, I think it is a way for Oracle to generate a statistic towards data that is mostly accessed by the program/query. So, by creating this statistic, it will help the database to find the desired data quickly… (just a tought)

I tried this one and I can save around 20 seconds for my queries…First time I ran it, it took around 18 secs, after analyzing the tables and its indexes..it took only 3-5 secs..yippie ^^

O..and if you already try to tune the query, indexing table, analyzing table, increase cache size, increase ora parameters..and still..it did not work..try to recreate the table..SOMETIMES it works..hahaha, what an extreme solution..oh, not good not good… @@’)

*I AM A NEWBIE, so dont trust every single word that I wrote here..kekeke