Re: Index organized table

From: buu <aha_at_a.com>
Date: Sun, 15 Jun 2008 14:08:07 +0200
Message-ID: <1213531695.704888@news.vmware.amis.hr>

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:6bk6d5F3cinplU1_at_mid.individual.net...
> On 15.06.2008 09:49, buu wrote:
>> I'm thinking about some changes in my DB app. to switch one table to an
>> IOT.
>> That table has pri. key wich is always stored in sequence order (in
>> inserting new row).
>> Currently, that table is 240Mb big (with more than 2 mil. rows) with an
>> average row size of 100 bytes.
>
> Unless you are using some ancient hardware I would not bother about data
> that size at all unless you are expecting this to grow to gigabytes
> shortly.

I'm expecting DB to grow rapidly.

>> There is an pri. key index and a single B-tree index.
>> Size of an pri. key index at the moment is 110Mb.
>
> Can you be more precise, i.e. post DDL of table and index along with
> Oracle version? With the information you have presented so far it is
> impossible to come up with a definitive answer.

Below, I put DDL and types of transactions that will be made on that table. Currently, It's Oracle XE 10.2, but it will move to an Standard or Standard Edition One.

>> Is it reccomended to use IOT in my case?
>> How would it affect insert time for a single row?
>
> If you have an IOT with an additional index it may be that you actually
> spent more space because the secondary index needs to store the PK to
> identify rows. Depending on the size (# of columns as well as types) the
> IOT solution might actually take more space especially if you add more
> indexes.
>
> Note also that IOT's make some administrative tasks harder. For example,
> you cannot just drop the PK constraint as easily as you can do with
> regular tables.
>
> Kind regards
>
> robert

DDL for table is:

CREATE TABLE WEBPAGES
(

  WEBPAGEID      NUMBER(20),
  WEBFOLDERID    INTEGER                        DEFAULT 0,
  SITEID         INTEGER,
  STATUS         CHAR(1 CHAR)                   DEFAULT 'N',
  PAGEURL        VARCHAR2(400 CHAR),
  WEBPAGEFROM    NUMBER(20)                     DEFAULT 0,
  DWTIME_MS      INTEGER                        DEFAULT 0,
  HTML_B_SIZE    INTEGER                        DEFAULT 0,
  PAGETITLE      VARCHAR2(400 CHAR),
  HTML_HASH      INTEGER                        DEFAULT 0,
  PAGEURL_HASH2  NUMBER(20)                     DEFAULT 0

)
TABLESPACE MYDATA
PCTUSED 10 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE (

            INITIAL          45M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE UNIQUE INDEX PK_WEBPAGES ON WEBPAGES (WEBPAGEID)
NOLOGGING TABLESPACE MYINDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (

            INITIAL          7M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      KEEP
           )

NOPARALLEL; CREATE INDEX NDX_WEBPAGES_SITEID ON WEBPAGES (SITEID)
NOLOGGING TABLESPACE MYINDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (

            INITIAL          7M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )

NOPARALLEL; ALTER TABLE WEBPAGES ADD (
  CONSTRAINT PK_WEBPAGES
 PRIMARY KEY
 (WEBPAGEID)
    USING INDEX TABLESPACE MYINDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
    STORAGE (

                INITIAL          7M
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
               ));

procs that manipulate table are:
- insert (pri. key is always in asc. order) - update status of table (update fields STATUS, DWTIME_MS, HTML_B_SIZE, PAGETITLE, HTML_HASH by primary key (WebPageId).

- select by primary key
- select by siteId
- delete by siteId
- once in a month update by siteId and Status 

Received on Sun Jun 15 2008 - 07:08:07 CDT