Nested Table
1.Can be used for persistence or PL/SQL programming, but mainly used for programming.
2.Can be used as the type of another table’s column
3.Can’t referece any table for constraints, including itself.
Temp Table:
1.There is no concurrency problem for temp tables. Because two sessions will never share one copy.
2.Oracle’s temp table is staticly-defined structure. You can’t expect to create/drop freely it in your programs.
3.The data can retain inside a transaction, or inside a session(Spanning-transaction).
4.Shortcomings
a.Can’t be used as the target of any foreign key
b.Can’t be analyzed => It will do harm to performace if CBO(cost-based optimizer) is used.
5.Try to avoid use temp-tables.
a. You can use INLIN-VIEWS for temp data. Select * from (select * from XXX), e.g.
b.If you have to use temp-table, try to give hints to CBO by "select /*first_low*/ * from temp_table", or use "DBMS_STATS" to help CBO.