Oracle-L: RE: Partitioning an IOT

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Fri, 19 Jul 2002 10:53:21 -0800
Message-ID: <F001.0049CE02.20020719105321@fatcity.com>

I did and it works fine. I'm on 8.1.7.2.

IOT used to have some problems in previous releases that you may need to check with Oracle.

Regards,

Waleed

-----Original Message-----
Sent: Friday, July 19, 2002 12:33 PM
To: Multiple recipients of list ORACLE-L

Hello,

Oracle 8.1.6 on HP-UX 11.0

Have the following table:

activity_log(

accountability_type_id       number          not null ,
accountability_id            number          not null ,
act_code                     number          not null ,
entry_time                   date            not null ,
employee_table_id            number          not null ,
addnl_info                   varchar2(255)   not null ,
activity_code_id             number                   ,
event_parent_id              number          not null ,
work_group                   varchar2(80)             ,
date_added                   date            not null)

Table stats: contains 20 million rows; average row length 136 bytes; no updates & deletes; 70000 rows gets inserted every night

The first 6 columns make up the primary key. Apart from this there are 2 more indexes (one with 4 columns and another with 6 columns).

80% of the queries pull the previous day's data.

The current table + index structure made me think I can have a better performance by having this table as IOT and also partition it on the date_added column (had to add this column as part of the primary key).

Initial tests look positive. Has anyone tried this approach? Any problems?

TIA
Prakash
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: BALA,PRAKASH (Non-HP-USA,ex1)
INET: prakash_bala_at_non.hp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------