Notes on ‘Expert Oracle’ — No.10.4: Table — Nested table & Temp table

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.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.