Nested Table / index

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

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;

Sorry ,
My question is

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

why do u need a index on nested columns.

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.

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);