Re: Index organized table

On 15.06.2008 14:08, buu wrote:
> "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.

>>> 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.

>>> 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.

> 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

> CREATE UNIQUE INDEX PK_WEBPAGES ON WEBPAGES
> (WEBPAGEID)
> NOLOGGING TABLESPACE MYINDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

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

> ALTER TABLE WEBPAGES ADD (
> CONSTRAINT PK_WEBPAGES
> PRIMARY KEY
> (WEBPAGEID)
> USING INDEX TABLESPACE MYINDEX PCTFREE 10 INITRANS 2
Index and data separation. Richard wrote some excellent articles about this myth - err - concept:

http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-some-thoughts-part-i-everything-in-its-right-place/

> 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

 From a glance at this I'd say that you might need less space with an IOT. But space is not everything. Btw, are you sure that you'll never do lookups based on PAGEURL?

I tend to be conservative about these things that's why I would leave it at the default unless there are reasons to change it. I'd probably create both versions and fire a defined load (sequence of insert, select etc. statements) against both and measure performance as well as final size of both tables and indexes. If you are just concerned with the size you could just copy your current contents into a second table and look at the sizes - although that will yield slightly skewed results (because of the missing deletions and updates which might leave some holes).

If your volume is going to be large then partitioning would also be a technique to think about but in your case hash partitioning seems the only realistic option and then you have to carefully choose the partitioning key so you can benefit from pruning for PK and SITEID accesses. Ideally you would want to have partition local indexes only.

Kind regards

        robert Received on Tue Jun 17 2008 - 01:32:58 CDT