Re: DATA BLOCK

From: Andrew Mobbs <andrewm_at_chiark.greenend.org.uk>
Date: 22 Feb 2002 11:41:59 +0000 (GMT)
Message-ID: <-Qg*HUyhp@news.chiark.greenend.org.uk>

Nuno Souto <nsouto_at_optushome.com.au.nospam> wrote:
>
>VARRAYs! That's the ones I couldn't remember the name of last week.
>Thanks, you just solved a minor problem I had for a report!

No problem, I could post a "Oracle datatype of the week" if it helps. :-P

>Coming back to this. IIRC, VARRAYS are in-lined if <4K and fitting
>the block size. If not, then ORACLE does a "LOB" on them and puts
>them on their own segment, internally-generated names/index names and
>all. So truly, they aren't multiple tables in the same block.
>Clustering would still be the most obvious example?

Just noticed that in 9i nested tables can themselves contain nested tables. Cool, I think.

Yes, my docs say 4k is the limit for in-lining VARRAYs, they're stored as a RAW. Out-of-line VARRAYs are stored as a BLOB, you can ask for VARRAYs to be stored out-of-line regardless.  

Anyway, yes, nested tables are off in their own segment so don't share blocks with the parent table.

SQL> CREATE TYPE item_t AS OBJECT
  2 ( id INTEGER,
  3 name VARCHAR2(30))
  4 /

Type created.

SQL> CREATE TYPE item_tab_type AS TABLE OF item_t;   2 /

Type created.

SQL> CREATE TABLE testnest (

  2  item_id     NUMBER,
  3  item        ITEM_TAB_TYPE)

  4 NESTED TABLE item STORE AS testnest_sub   5 /

Table created.

SQL> SELECT segment_name,file_id,block_id,blocks   2 FROM dba_extents
  3 WHERE segment_name LIKE 'TEST%';

SEGMENT_NAME

---

   FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
TESTNEST_SUB
         3 2446779 1280

TESTNEST
         3 2266299 1280

(er... excuse the 10 MB extent size - disk space is cheap, right? :)

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/

Received on Fri Feb 22 2002 - 05:41:59 CST