Home » SQL & PL/SQL » SQL & PL/SQL » EXCHANGE PARTITION
EXCHANGE PARTITION [message #252945] Fri, 20 July 2007 17:56 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: EXCHANGE PARTITION [message #254035 is a reply to message #252980] Wed, 25 July 2007 10:31 Go to previous message
serafo
Messages: 24
Registered: May 2006
Junior Member
Thanks rleishman! It works pretty good.
Previous Topic: ora-03114 error
Next Topic: Sql Help, please! - could I have come up with a less helpful title?
Goto Forum:
  


Current Time: Sat Dec 10 14:53:42 CST 2016

Total time taken to generate the page: 0.12063 seconds