Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Partitioning problems - Oracle 8.1.7.4

Re: Partitioning problems - Oracle 8.1.7.4

From: <Tony.Adolph_at_o2.com>
Date: Mon, 7 Feb 2005 11:14:47 +0100
Message-ID: <OF7E0AEE4A.6B0B00EE-ONC1256FA1.0036EFA8-C1256FA1.00382AAC@viaginterkom.de>


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

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



--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 07 2005 - 05:14:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US