Re: SOLVED: nested table access using index

There is some discussion of how Oracle handles nested tables in my book. But the answer to the main question "why doesn't it create the index on nested_table_id" is that you might want to have your own index, e.g.

    (nested_table_id, extra_cols)
You might want to create the nested table as an IOT with a primary key that includes the nested table id, and has a degree of compression. So the choice is left up to you.

The index generated by DBA studio is a good example of how software takes time to catch up with the kernel. the SYS_NC00060007$ column holds the nested_table_id on the parent table, so it has to have a unique constraint (though in principle not a unique index). If I recall correctly the 6/7 are a clue that there are two attributes to your nested object type which notionally display as the 6th and 7th columns of your parent table. (Obviously, with a bigger list of attributes in the type and a different location in the parent table, the values 6/7 would be adjusted accordingly).

One

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Marcin Buchwald wrote in message <3DC0E3BC.E73B5894_at_agora.pl>...


>Thank You Jonathan.

>You are absolutly great!

>

>I typed in:

>

>create index IND_GRB_STR on grzbiet_str (nested_table_id) tablespace

indx;


>

>The plan for now is:

>

> 0                    SELECT STATEMENT Optimizer=CHOOSE (Cost=3

Card=29


>Bytes=754)

> 1                  0   TABLE ACCESS (BY INDEX ROWID) OF

'GRZBIET_STR'


>(Cost=3 Card=29 Bytes=754)

> 2                  1     INDEX (RANGE SCAN) OF 'IND_GRB_STR'

(NON-UNIQUE)


>(Cost=1 Card=29)

> 3                  2       TABLE ACCESS (BY INDEX ROWID) OF

'GRZBIET'


>(Cost=1 Card=1 Bytes=20)

> 4                  3         INDEX (UNIQUE SCAN) OF 'PK_GRZBIET'

(UNIQUE)


>

>The response time is O.K.

>

>What's interesting it is

>CONSTRAINT "UIND_GRB_STR" UNIQUE("SYS_NC0000600007$")

>in table definition.

>

>I used the option "Generate DDL" in DBA Studio.

>In my opinion the index is something like

>    NC  - Nested column,

>    00006 - 6th column in the table

>    00007$ - have no idea

>

>The index is created automaticly (different constraint name and

tablespace


>- I did the corrections myself)

>It seems not to be used. Without my corrections its going the same.

>What is this index for???

>

>The idea of Oracle nested tables without nested_table_id index is

weak.


>I can't imagine the real implementation of this construct without it.

>Could it be created by database on table with nested columns creation

>automaticly?

>

>

>

>Jonathan Lewis wrote:

>