Re: Index-Organized Tables

Murray Sobol wrote:
> 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.

There are two problems here.

1. The solution to your slow performance is to find out why it is slow. It seems you are just jabbing about in the dark hoping to connect with a solution: It will undoubtedly not work.

Where is the explain plan, AUTOTRACE, 10053 Trace, etc.?

2. The problem with your IOT is simply syntax: Change tablespace smartsoft_data
ORGANIZATION INDEX
to
ORGANIZATION INDEX
tablespace smartsoft_data

HTH

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)