UNION and UNION ALL operations are used to combine two or more queries in a table.

UNION is best used for retrieving data in a sort-unique way. Oracle will process each data retrieved from the table and compare it if there is double values. UNION operation eliminates the duplicated data from the result set. It is usefull if you want distinct value to be retrieved from both collections. This extra process could takes time.

UNION ALL is best used when you want to join two or more tables without care about data duplication. UNION ALL operation is much faster than UNION because it does not require extra process for removing duplication.

So which one provides better performance? It depends on your need. But when you look at the auto trace result for both of them. On the execution plans, you can notice that UNION operation uses UNION ALL operation in execution. Shortly, it can be said that UNION = UNION ALL + sort operations.

So the best practice for this would be to use UNION ALL instead of UNION (whenever possible).

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