index by table in pl/sql

vipin1982 · #1 (**permalink**) August 28th, 2008, 05:55 AM

Hi,

i want to know about INDEX BY TABLE in pl/sql
and the advatages of INDEX BY TABLE.
will you plz explain this topice in bref.

vipin sudhakran

djwesto · #2 (**permalink**) October 1st, 2008, 12:19 PM

The authors of Beginning Oracle Programming (Dillon, Beck, & Kyte; 2002) stated "PL/SQL tables, or index-by tables ... are non-persistent tables that can be used in PL/SQL routines to emulate an array (p. 361).

Please be aware that these tables are actually one-dimensional arrays and are not regular Oracle tables.

One immediate use that comes to mind is the creation of OLAP (On-line analytic processing) stored procedures executed from the results of previous OLTP (On-line transaction processing) queries. Having access to a non-persistent table allows one to quickly manipulate data as an intermediate step. Some database tables may be immense and one may need to optimize total processing speed.

Another use may be far simpler, one has a need to work with an array structure or have the need to perform a cursive-like approach to a problem. Having access to an array data structure may make developing a solution easier than manipulating queries or creating views.