nested table access using index

From: Marcin Buchwald <Marcin.Buchwald_at_agora.pl>
Date: Wed, 30 Oct 2002 11:55:10 +0100
Message-ID: <3DBFBA8E.8AB5078A@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 Wed Oct 30 2002 - 04:55:10 CST