Index-Organized Tables

From: Murray Sobol <murray_sobol_at_dbcsmartsoftware.com>
Date: Fri, 18 Nov 2005 12:22:50 -0500
Message-ID: <hi2sn1hlij3oogrndd9ujm4h225bkiuftf@4ax.com>

Environment: Windows XP SP2
Database: Oracle 9.2.0.6

I have a table defined as follows:

CREATE TABLE dpr_opening_balance
(

   plc_id                        varchar2(10)                not null,
   commodity_id            varchar2(10)                not null,
   opening_date            date                        not null,
   group_nbr                  number(5)                   not null,
   sort_order                  number(5)                   not null,
   quantity                      number                      not null,
   dpr_uom_code            varchar2(10)                null,

   CONSTRAINT pk_dpr_opening_balance PRIMARY KEY (plc_id, commodity_id, opening_date, group_nbr, sort_order)

       using index
       tablespace smartsoft_index

)
tablespace smartsoft_data
/

CREATE INDEX dpr_opening_balance_ndx1 ON dpr_opening_balance
(commodity_id ASC, opening_date ASC)

tablespace smartsoft_index
/

CREATE INDEX dpr_opening_balance_ndx2 ON dpr_opening_balance
(opening_date ASC)

tablespace smartsoft_index
/

This table is updated ONCE per day, several hundred (or thousand) new rows are inserted.

Query performance against this table is very slow (several minutes when I am expecting a few seconds).

I am considering changing this table to an Index-Organized table as follows:

CREATE TABLE dpr_opening_balance
(

   plc_id                        varchar2(10)                not null,
   commodity_id            varchar2(10)                not null,
   opening_date            date                        not null,
   group_nbr                  number(5)                   not null,
   sort_order                  number(5)                   not null,
   quantity                      number                      not null,
   dpr_uom_code            varchar2(10)                null,

   CONSTRAINT pk_dpr_opening_balance PRIMARY KEY (plc_id, commodity_id, opening_date, group_nbr, sort_order) )
tablespace smartsoft_data
ORGANIZATION INDEX
/

When I run the above SQL (in SQL*Plus Worksheet), I get the following error:
ORGANIZATION INDEX
*
ERROR at line 13:
ORA-00922: missing or invalid option

Elapsed: 00:00:00.00

I would like to ensure that this table is created within the correct tablespace (in my case: 'smartsoft_data'), but am failing in my attempts.

The Oracle online manuals provide this example:

    STORAGE

     ( INITIAL  4K 
      NEXT  2K 
      PCTINCREASE 0 
      MINEXTENTS 1 
      MAXEXTENTS 1 ) 

   OVERFLOW
    STORAGE

      ( INITIAL  4K 
        NEXT  2K 
        PCTINCREASE 0 
        MINEXTENTS 1 
        MAXEXTENTS 1 ); 

but I am hesitant to use this, since all of my tablespaces are LMT.

Also, to speed up the queries against this table, I would like to create a index on the column: 'opening date', but am unsure of the syntax required.

BTW, I am very new to INDEX-ORGANIZED tables

Any comments / assistance would be appreciated.

Murray Sobol
dbcSMARTsoftware inc. Received on Fri Nov 18 2005 - 11:22:50 CST