1. I want to know the detailed infor about explain plans, with the terminologies explanation, how to decode it, and other info in detail. Can any one send me the link or attach documents if any in this regard ?
2. Under what conditions temporary tables are used to get the max benefit?
1. Let me google that for you...
2. Temporary tables are used to hold data temporarily. Most importantly, one session cannot see another sessions data, even after committing. There are many different options, but in all cases the data will not be there after the session disconnects.
I would use Temporary Tables when I need to do many calculations over a subset of data. I would also use them extensively during data transformation in something like an ETL process.
3. Partitioning is of used in large tables, usually 1.5 million rows+. It helps optimise data access by splitting your table into logical segments.
It can also be effective for implementing a kind of "archiving" functionality. In this scenario the data is still in the same table and you still have access to it from the same table, but your query's performance is not impacted by old data because it "knows" which data is current and which is old (depending on how you set it up of course).
For these types of questions, I seriously recommend reading through the Oracle Database Concepts. This guide is the doorway to all Oracle functionality and is an excellent resource that I use regularly.