SOLVED: nested table access using index

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:

> Where did you get the code that shows
> a unique constraint on hidden columns ?
>
> I would not have expected this to (a) be
> legal, (b) be possible - I must try it.
>
> Is this an IOT with a nested table by the way -
> it doesn't look like from your definition, but
> that may be because you've just reduced
> the definition.
>
> Nested tables have an additional hidden
> column called nested_table_id, which holds
> a unique identifier that is stored in the parent
> row in the parent table.
>
> To get from the parent to the child rapidly,
> the normal practice is to create an index
> on the nested table which starts with the
> nested_table_id. Typically, this index is
> a good candidate for compression on the
> first column - and in many cases the
> nested table can be created very effectively
> as an IOT in its own right.
>
> --
> 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 <3DBFBA8E.8AB5078A_at_agora.pl>...
> >I've got the table
> >
> >CREATE TABLE "SPACE_RESERVATION"."GRZBIET"(
> > <not interesting columns>
> > "STRONY" "SPACE_RESERVATION"."GRZBIET_STRONY"
> > CONSTRAINT "UIND_GRB_STR" UNIQUE("SYS_NC0000600007$")
> >) LOGGING NESTED TABLE STRONY STORE AS GRZBIET_STR
> >
> >and simple queries:
> >
> >select * from table(select strony from grzbiet where xx=441);
> >select * from table(select /*+ INDEX grzbiet_str */ strony from
> grzbiet
> >where xx=441);
> >
> >they do not use UIND_GRB_STR
> >
> > 0 SELECT STATEMENT Optimizer=CHOOSE
> (Cost=8
> >Card=30 Bytes=780)
> > 1 0 FILTER
> > !!! 2 1 TABLE ACCESS (FULL) OF
> 'GRZBIET_STR'
> >(Cost=8 Card=30 Bytes=780)
> > 3 1 TABLE ACCESS (BY INDEX ROWID) OF
> >'GRZBIET' (Cost=1 Card=1 Bytes=20)
> > 4 3 INDEX (UNIQUE SCAN) OF
> 'PK_GRZBIET'
> >(UNIQUE)
> >
> >
> >The Question:
> >
> >As I know, nested table (GRZBIET_STR) rows are stored separetely from
> >main table GRZBIET.
> >Am I right?
> >If so, using of UIND_GRB_STR should be great for this queries.
> >I noticed the response time gets worse quick, as table GRZBIET grows
> :-(
> >
> >How to persuade Oracle into using the index in this example?
> >
> >Regards,
> >
> > Marcin
> >
> >
> >Oracle EENT 8.1.7
> >
Received on Thu Oct 31 2002 - 02:03:08 CST