8i Index-Organized Tables Question?

From: Erik <ecotsonas_at_saraswati.com>
Date: Thu, 08 Jul 1999 21:21:54 GMT
Message-ID: <7m34p5$nkk$1@nnrp1.deja.com>

Hello,
I have been working on creating an Index-Organized Table (8i feature). I have been able to create an index organized table on a 2GB existing table, but the primary key for the index-organized table has a large number of extents (over 14,000). I have not been able to find a way to specify the storage for the primary key on the index-organized table at creation time.

For Example:
CREATE TABLE CPEH_IOT (
  RRD_CLIENT_ID,
  CENTRE_ID,
  VISIT_NBR,
  INT_PROD_MENU_ID,

  PURCH_DT,
  PURCH_QUANTITY,
  PURCH_AMT,

  PROD_TYPE_CD,
  GROUP_TYPE,
  PRODCAT,
  constraint cpeh_iot_pk primary key
    ( RRD_CLIENT_ID,

      PURCH_DT,
      CENTRE_ID,
      VISIT_NBR,
      INT_PROD_MENU_ID
	)

  )
  organization index tablespace jci_data   PCTTHRESHOLD 20 OVERFLOW TABLESPACE jci_data   STORAGE (INITIAL 500M NEXT 500M MAXEXTENTS 100 PCTINCREASE 0)   PCTFREE 10
  PARALLEL (DEGREE 8)
  NOLOGGING
AS SELECT * FROM CPEH; Creates:

Object                  Size (MB)            Extents
-----------             ---------            -------
cpeh_iot_pk             2285                 14617
SYS_IOT_OVER_5030       500                  1

Where:
1. cpeh_iot_pk is the primary key for the table 2. sys_iot_over_5030 appears to be the overflow object

Note that the sys_iot_over_5030 is sized at 500M which is what I specified in my create statement.

I want to be able to specify the storage for the primary key to reduce the number of extents.

Does anyone have any ideas?
(I don't want to change the default next extent size for the tablespace either).

--
Erik
Consultant
Saraswati Systems Corporation - (SSC)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 16:21:54 CDT