Nested Table / index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Nested Table / index

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Posts
    134
    Could I create on nested tables column ?
    If yes then please write the syntex.
    Thanx

  2. #2
    Join Date
    Nov 2001
    Location
    Turkey
    Posts
    7
    Hi,
    I send to you this document.
    Oracle 8 or higer support Nested Tables.

    Regerds.

    Examples for Nested Tables
    --------------------------

    Example 1:
    ----------

    The following example illustrates how a simple nested table is created.

    a) First, define a Object type as follows:

    SQL> CREATE TYPE ELEMENTS AS OBJECT (
    2> ELEM_ID NUMBER(6),
    3> PRICE NUMBER(7,2));
    4> /

    b) Next, create a table type ELEMENTS_TAB which stores ELEMENTS objects:

    SQL> CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;
    2> /

    c) Finally, create a database table STORAGE having type ELEMENTS_TAB as
    one of its columns:

    SQL> CREATE TABLE STORAGE (
    2> SALESMAN NUMBER(4),
    3) ELEM_ID NUMBER(6),
    4) ORDERED DATE,
    5) ITEMS ELEMENTS_TAB)
    6) NESTED TABLE ITEMS STORE AS ITEMS_TAB;


    Example 2:
    ----------

    This example demonstrates how to populate the STORAGE table with a single
    row:

    SQL> INSERT INTO STORAGE
    2> VALUES (100,123456,SYSDATE,
    3> ELEMENTS_TAB(ELEMENTS(175692,120.12),
    4> ELEMENTS(167295,130.45),
    5> ELEMENTS(127569,99.99)));


    Example 3:
    ----------

    The following example demonstrates how to use the operator THE which is
    used in a SELECT statement to identify a nested table:

    SQL> INSERT INTO
    2> THE
    3> (SELECT ITEMS FROM STORAGE WHERE ELEM_ID = 123456)
    4> VALUES (125762, 101.99);


    Example 4:
    ----------

    The following example shows how to update the STORAGE table row where
    salesman column has value 100:

    SQL> UPDATE STORAGE
    2> SET ITEMS = ELEMENTS_TAB(ELEMENTS(192512, 199.99))
    3> WHERE SALESMAN = 100;

  3. #3
    Join Date
    Aug 2001
    Posts
    134

    Could I create on nested tables column ?


    Sorry ,
    My question is

    Could I create INDEX on nested tables column ?
    If yes then please write the syntex.
    Thanx


  4. #4
    Join Date
    Feb 2001
    Posts
    389
    why do u need a index on nested columns.

  5. #5
    Join Date
    Nov 2001
    Location
    Turkey
    Posts
    7
    Yes,

    In Example 1:

    CREATE TYPE ELEMENTS AS OBJECT (
    ELEM_ID NUMBER(6),
    PRICE NUMBER(7,2));

    CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;

    CREATE TABLE STORAGE (
    SALESMAN NUMBER(4),
    ELEM_ID NUMBER(6),
    ORDERED DATE,
    ITEMS ELEMENTS_TAB)
    NESTED TABLE ITEMS STORE AS ITEMS_TAB;


    INSERT INTO STORAGE
    VALUES (100,123456,SYSDATE,
    ELEMENTS_TAB(ELEMENTS(175692,120.12),
    ELEMENTS(167295,130.45),
    ELEMENTS(127569,99.99)));

    *************
    CREATE INDEX AA1 ON STORAGE (ELEM_ID);
    ****************

    You can create index on ELEM_ID column.






  6. #6
    Join Date
    Nov 2001
    Location
    Turkey
    Posts
    7
    In Example 1:

    CREATE TYPE ELEMENTS AS OBJECT (
    ELEM_ID NUMBER(6),
    PRICE NUMBER(7,2));

    CREATE TYPE ELEMENTS_TAB AS TABLE OF ELEMENTS;

    CREATE TABLE STORAGE (
    SALESMAN NUMBER(4),
    ELEM_ID NUMBER(6),
    ORDERED DATE,
    ITEMS ELEMENTS_TAB)
    NESTED TABLE ITEMS STORE AS ITEMS_TAB;


    INSERT INTO STORAGE
    VALUES (100,123456,SYSDATE,
    ELEMENTS_TAB(ELEMENTS(175692,120.12),
    ELEMENTS(167295,130.45),
    ELEMENTS(127569,99.99)));

    *************
    CREATE INDEX AA1 ON STORAGE (ELEM_ID);
    ****************

    You can create index on ELEM_ID column.

    Sorry,

    You can create index ;

    CREATE INDEX AA3 ON ITEMS_TAB (ELEM_ID);

    or

    CREATE INDEX AA4 ON ITEMS_TAB (PRICE);

    or

    CREATE INDEX AA5 ON ITEMS_TAB (ELEM_ID,PRICE);






Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width