Re: Q about objects/nested tables

I can't give you a reference that says explicitly what you want to hear; but be assured that you have simply created a data type, not a physical object.

When you create a variable of that type in a PL/SQL procedure, it is just another
variable that goes on the local heap
(not even the SGA).

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

sam wrote in message <867hdh$cgk$1_at_nnrp1.deja.com>...
>The question follows the scenario, the motivation is performance.
>I create an object type and a table type of that object type in SQLPlus
>
>CREATE OR REPLACE TYPE data_obj
> AS OBJECT (idx INTEGER,
> some_num NUMBER,
> some_string VARCHAR2(162),
> some_str2 VARCHAR2(289));
>/
>CREATE OR REPLACE TYPE data_list AS TABLE OF data_obj;
>/
>
>The largest this will ever grow is 100 rows in the table, 300
>characters in the 2 combined varchar2 fields.
>
>then I use these both of these in PLSQL packages. I never assign a
>tablespace to the object / table types, and in the PLSQL I never do
>any dynamic SQL to assign tablepsaces for these objects.
>
>The question is, will any data I store in these objects in the PLSQL
>end up on disk or will it be held in the SGA? Could you please provide
>a reference to ORCL docs
>
>In the plsql ref, oracle 8.1 HTML docs section 4, subsection 'varrays
>versus nested tables' I find this:
>Oracle stores varray data in-line (in the same tablespace). But, Oracle
>stores nested table data out-of-line in a store table, which is a
>system-generated database table associated with the nested table
>
>While It's a small leap to add 'if you specify storage, these get
>stored in the database, if you specify no storage and only ever use
>these from pl/sql it stays in SGA.'
>
>I don't want to make that assumption without definitive source
>
>--
>thanks for reading
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Jan 20 2000 - 14:17:54 CST