Re: nested table access using index

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

>