Re: Partitioning problems - Oracle 8.1.7.4

Hi Jaromir & Tim,
Thanks for your feedback.

I've taken on board what you've pointed out...

When I drop the global index on my temp table, I get the same error, so I dropped the local index and
recreated the global.
So on my temp table I now only have the global index:

SQL> create index I_TONYTAB_TEMP_1 on
  2 TONYTAB_TEMP (CUSTOMER_ID,SERVICE_ID)   3 tablespace PAGENTX00 pctfree 10 initrans 2 maxtrans 255   4 storage (initial 1k next 1k minextents 1 maxextents 121 pctincrease 0);

Index created

SQL> alter table TONYTAB exchange partition P01 with table TONYTAB_TEMP including indexes without validation;

Table altered

Which seems to be opposite to what you are suggesting :-(

Also the exchange causes the PK index on the main table to go UNUSABLE which I guess makes sense, but
is bad news. I suppose the 9i option UPDATE GLOBAL INDEXES fixes this?

Regarding the archive table: Because I have a global PK index I thought that letting the table grow would
have an adverse affect on performance for this index, so archiving a partition off would make sense.

Cheers
Tony

"jaromir nemec" <jaromir_at_db-nemec.com>
Sent by: oracle-l-bounce_at_freelists.org
02/06/2005 09:29 PM
Please respond to
jaromir_at_db-nemec.com

To
<oracle-l_at_freelists.org>
cc

Subject
Re: Partitioning problems - Oracle 8.1.7.4

> Why do you have a separate "archive" table at all?

exactly; it's possible good enough to have only an archive (e.g. read only)
tablespace.

Archive *table* could be appropriate if you keep only selected columns of the original table or only some aggregated data.

Jaromir

http://www.db-nemec.com

• Original Message ----- From: "Tim Gorman" <tim_at_evdbt.com> To: <Tony.Adolph_at_o2.com>; <oracle-l_at_freelists.org> Cc: <Maxim.Demenko_at_o2.com>; <Peter.Trischberger_at_o2.com>; "Tony Adolph" <t_adolph_at_hotmail.com>; <tony.adolph_at_tiscali.de> Sent: Saturday, February 05, 2005 6:36 PM Subject: Re: Partitioning problems - Oracle 8.1.7.4

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l