| EXCHANGE PARTITION [message #252945] |
Fri, 20 July 2007 17:56  |
serafo
Messages: 24
Registered: May 2006
|
Junior Member |
|
|
Good Afternoon.
I am trying to use Exchange Partition. Schema STG has the base table (tbl_base) with the new data. Schema DWH has the partitioned table ( tbl_base_particionada).
I am executing a PL/SQL from schema STG. Schema STG has the DROP ANY TABLE provilege.
The Procedure works and does exchange partition, but returns the folowing exception :
ORA-01418: specified index does not exist
I notice the error takes place when I try to rebuild local unsuable indexes. If I comment the line , no exception is generated, but just a select count over the DWH schema table just populated takes long time.
If a rebuild the indexes direct from schema DWH, then the select count really improves so I think the indexes does need to be rebuild.
The following is the query I execute with the real objects :
EXECUTE IMMEDIATE 'TRUNCATE TABLE COMISIONES';
INSERT INTO /*+ APPEND PARALLEL(COMISIONES, 3) NOLOGGING */ COMISIONES
(
COM_VALOR,
COM_NEGOCIO,
COM_CODIGORESPONSABLE,
COM_CODIGOOFICINA,
COM_CODIGOSEGMENTO,
COM_FECHA,
COM_CODIGOPLAN,
COM_CODIGOCLIENTE,
COM_CODIGOPUC,
COM_CODIGOAPLICATIVO,
COM_TIPO
)
SELECT
"COM_VALOR",
"COM_NEGOCIO",
"COM_CODIGORESPONSABLE",
"COM_CODIGOOFICINA",
"COM_CODIGOSEGMENTO",
TO_DATE(TO_CHAR(COM_FECHA, 'DD/MM/YYYY'), 'DD/MM/YYYY'),
"COM_CODIGOPLAN",
"COM_CODIGOCLIENTE",
"COM_CODIGOPUC",
"COM_CODIGOAPLICATIVO",
"COM_TIPO"
FROM EXT_COMISIONES_TMP;
COMMIT;
/* TRUNCAR LA PARTICION */
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES TRUNCATE PARTITION FEB2007 UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES MODIFY PARTITION FEB2007 REBUILD UNUSABLE LOCAL INDEXES';
/* HACER EXCHANGE PARTITION CON LA TABLA DE GRANBANCOSTG */
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES EXCHANGE PARTITION FEB2007 WITH TABLE COMISIONES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES MODIFY PARTITION FEB2007 REBUILD UNUSABLE LOCAL INDEXES';
Best Regards and thanks for your help.!
|
|
|
|
| Re: EXCHANGE PARTITION [message #252980 is a reply to message #252945] |
Sat, 21 July 2007 02:39  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try replacing these 4 lines:
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES TRUNCATE PARTITION FEB2007 UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES MODIFY PARTITION FEB2007 REBUILD UNUSABLE LOCAL INDEXES';
/* HACER EXCHANGE PARTITION CON LA TABLA DE GRANBANCOSTG */
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES EXCHANGE PARTITION FEB2007 WITH TABLE COMISIONES WITHOUT VALIDATION UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE DWH.COMISIONES MODIFY PARTITION FEB2007 REBUILD UNUSABLE LOCAL INDEXES';
with a simple:
EXECUTE IMMEDIATE '
ALTER TABLE DWH.COMISIONES
EXCHANGE PARTITION FEB2007 WITH TABLE COMISIONES
INCLUDING INDEXES
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES
';
Make sure that the non-partitioned table has indexes that exactly match the LOCAL indexes of the partitioned table, but NONE of the global indexes.
By having the local indexes all ready on the non-partitioned table, Oracle can swap them over along with the data.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
