1 2 Previous Next 27 Replies Latest reply: Feb 26, 2012 9:11 PM by rp0428 RSS

    very long time to process big table

    mhernancl
      Hi everyone,
      I have a problem with a process that computes 6 columns of a table with 260 millions of rows. At the begining the compute time was about 1.2 mins per row. wich obviously takes more than a life to finish (about 500 years).
      So to improve this process naturally my first step was to partition the table. This make 64 partitions with 5 millions rows in the biggest one. My second step was to use a bulk collect with a limit of 500.000 per block, this way i have my array and next step is to compute my columns. So sadly, my first 2 columns just take 2 hours to complete for the 260 millions (Note: I need to compute all rows in the table), but the rest of columns, take 90% of total time or even more. Finally i make a bulk update with forall using the data computed on my arrays.
      After all this, the compute time was reduced of 1.2 mins per row to 6 or 7 seconds per row. time wich is great, but just reduced from 500 years to 49 years.
      With this scenary, my next logical step is to parallelize my query's, but if a have a degree of 8, my best shot is to take 6 years.
      Now, the problem with four of six columns, is that i need to compute some values that requires make some avg and sum over data from last six months previous to the record.
      So, here is my real question. ¿how i make this works?... surely i can not wait a lot of years. perhaps the company is broke before this finish.
      Which is the professional way to do this real? i think out there companies have tables with lots more of rows, even billions, i have saw in others forums.
      I need this, someway, somehow, this takes no more than one or two months... even less is better yet. PLEASE HELP

      My DB is a 11G, running on 64bits. 32GB RAM, 6 CPU Quadcore.
        • 1. Re: very long time to process big table
          JustinCave
          It would help if you could describe the data you have (post a sample) and describe the calculations you are trying to perform. Otherwise, we're pretty much blind to what problems you have.

          Justin
          • 2. Re: very long time to process big table
            rp0428
            Welcome to the forum
            >
            I have a problem with a process that computes 6 columns
            >
            First you should explain what it is that you are doing with the data. What does 'computes 6 columns' mean?

            Then provide the DDL for the table (shows the columns and data types), tell us what indexes are available on the table, some sample data and the query you are using to SELECT the data.

            Finally tell us what results you expect and what you are doing with the query results.

            Are you selecting from one table and updating another? Are you updating the same table you are selecting from.

            We need more information about the process you are using.
            • 3. Re: very long time to process big table
              sb92075
              i make a bulk update with forall using the data computed on my arrays.
              PL/SQL will NEVER be faster than raw SQL
              • 4. Re: very long time to process big table
                mhernancl
                ok, the objective of the process is to take all the data of six years, wich is in one table only. The table is with nologging, parallel degree 8 and partitioning. and we have to update same table (six columns) and it's values are calculated with data of the same table.
                Here is the DDL of the table:

                create table MV_DATA_SEC
                (
                COD_EMPRESA NUMBER(3),
                PERIODO VARCHAR2(6),
                ID_CLIENTE VARCHAR2(14),
                TIPO_CLIENTE VARCHAR2(1),
                NOMBRE VARCHAR2(80),
                DIRECCION VARCHAR2(100),
                NCONTRATO VARCHAR2(14),
                ID_TIPO_NUMERO_CONTRATO NUMBER(1),
                FCONTRATO DATE,
                FTCONTRATO DATE,
                ATIPICA VARCHAR2(2),
                SECTOR NUMBER(1),
                ID_TIPO_TARIFA NUMBER(2),
                ID_TIPO_CONSUMO NUMBER(2),
                ID_MEDIDOR VARCHAR2(30),
                VOLTAJE NUMBER(6),
                KWH NUMBER(10),
                LANTERIOR NUMBER(8),
                LACTUAL NUMBER(8),
                CONSTANTE FLOAT,
                ENERGIA NUMBER(8),
                LINVIERNO NUMBER(8),
                EINVIERNO NUMBER(8),
                PINVIERNO NUMBER(8),
                PPUNTA NUMBER(8),
                DPMAXIMA NUMBER(8),
                PBASE NUMBER(8),
                PVERANO NUMBER(8),
                DPMPUNTA NUMBER(8),
                DMLPPUNTA NUMBER(8),
                EICINTERNAS FLOAT,
                EICEXTERNAS FLOAT,
                NDOCUMENTO NUMBER(12),
                FDOCUMENTO DATE,
                FVENCIMIENTO DATE,
                CFIJO NUMBER(10),
                CENERGIA NUMBER(10),
                CEINVIERNO NUMBER(10),
                CPINVIERNO NUMBER(10),
                CPBASE NUMBER(10),
                CPVERANO NUMBER(10),
                CPDMAXIMA NUMBER(10),
                CDCMAXIMA NUMBER(10),
                CDMPUNTA NUMBER(10),
                CRFPOTENCIA NUMBER(10),
                CRPTRANS NUMBER(10),
                CARRIENDO NUMBER(10),
                CSERVICIOS NUMBER(10),
                SANTERIOR NUMBER(10),
                INTERESES NUMBER(10),
                DESCUENTO NUMBER(10),
                IVA NUMBER(10),
                TOTAL NUMBER(10),
                FLANTERIOR DATE,
                FLACTUAL DATE,
                ID_TIPO_DOCUMENTO VARCHAR2(1),
                ID_TIPO_COBRO VARCHAR2(1),
                SUBPRIMARIA VARCHAR2(40),
                FACTURABLE NUMBER,
                FACTURABLE2 NUMBER,
                COD_MEDIDOR CHAR(30),
                T_LECTURA NUMBER(10),
                T_ESTACION CHAR(3),
                LIM_INVIERNO NUMBER(10),
                E_ADIC_INVIERNO NUMBER(10),
                VAL_PROV_CALCULADO NUMBER(10),
                PROR_CONSUMO NUMBER(10),
                POT_BASE NUMBER(10)
                )
                partition by list (PERIODO)
                (
                partition PERIODO1 values ('072006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO2 values ('082006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO3 values ('092006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO4 values ('102006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO5 values ('112006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO6 values ('122006')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO7 values ('012007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO8 values ('022007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO9 values ('032007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO10 values ('042007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO11 values ('052007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO12 values ('062007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO13 values ('072007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO14 values ('082007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO15 values ('092007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO16 values ('102007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO17 values ('112007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO18 values ('122007')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO19 values ('012008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO20 values ('022008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO21 values ('032008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO22 values ('042008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO23 values ('052008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO24 values ('062008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO25 values ('072008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO26 values ('082008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO27 values ('092008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO28 values ('102008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO29 values ('112008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO30 values ('122008')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO31 values ('012009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO32 values ('022009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO33 values ('032009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO34 values ('042009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO35 values ('052009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO36 values ('062009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO37 values ('072009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO38 values ('082009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO39 values ('092009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO40 values ('102009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO41 values ('112009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO42 values ('122009')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO43 values ('012010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO44 values ('022010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO45 values ('032010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO46 values ('042010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO47 values ('052010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO48 values ('062010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO49 values ('072010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO50 values ('082010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO51 values ('092010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO52 values ('102010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO53 values ('112010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO54 values ('122010')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO55 values ('012011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO56 values ('022011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO57 values ('032011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO58 values ('042011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO59 values ('052011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO60 values ('062011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO61 values ('072011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO62 values ('082011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO63 values ('092011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO64 values ('102011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO65 values ('112011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                ),
                partition PERIODO66 values ('122011')
                tablespace SEC_SFACTURACION_DAT
                pctfree 10
                initrans 1
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                )
                );
                -- Create/Recreate indexes
                create index IDX_DATA_SEC_EMP2 on MV_DATA_SEC (COD_EMPRESA)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_FACT12 on MV_DATA_SEC (FACTURABLE)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_FACT22 on MV_DATA_SEC (FACTURABLE2)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_FDOC2 on MV_DATA_SEC (FDOCUMENTO)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_MEDI on MV_DATA_SEC (ID_MEDIDOR)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 4096M
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_PER2 on MV_DATA_SEC (PERIODO)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_SUBP2 on MV_DATA_SEC (SUBPRIMARIA)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_TCOB2 on MV_DATA_SEC (ID_TIPO_COBRO)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_TDOC2 on MV_DATA_SEC (ID_TIPO_DOCUMENTO)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );
                create index IDX_DATA_SEC_TTAR2 on MV_DATA_SEC (ID_TIPO_TARIFA)
                tablespace SEC_SFACTURACION_IDX
                pctfree 10
                initrans 2
                maxtrans 255
                storage
                (
                initial 64K
                next 1M
                minextents 1
                maxextents unlimited
                );

                So, first i make a bulk collect.


                cursor c_baseData is
                select t.lactual, t.lanterior, t.energia, t.rowid, t.periodo, t.t_lectura, t.t_estacion,
                t.lim_invierno, t.e_adic_invierno, t.val_prov_calculado, t.pror_consumo, t.pot_base,
                t.cod_empresa, t.id_cliente, t.flactual, t.id_medidor, t.facturable, t.id_tipo_cobro,
                t.flanterior, t.id_tipo_tarifa
                from mv_data_sec t
                where id_tipo_documento in ('B','F');

                fetch c_baseData bulk collect
                into v_lactual, v_lanterior, v_energia, v_rowid, v_periodo, v_lectura, v_estacion,
                v_avg_verano_ins, v_ea_invierno_ins, v_val_provisorio, v_Ecalendario, v_potencia_base,
                v_cod_empresa, v_id_cliente, v_flactual, v_id_medidor, v_facturable, v_id_tipo_cobro,
                v_flanterior, v_id_tipo_tarifa limit 500000;
                exit when c_baseData%NOTFOUND;

                And i start a LOOP, because i have to process each row. Inside the loop i have to calculate the values for the six columns. We have 2 escenarios.

                First one (This one, have no problem, because is calculated with data of the same row and therefore the execution time is lower than a fraction of second);
                v_lectura(idx) := case when ((v_lactual(idx)<>v_lanterior(idx)) and v_energia(idx)>0) then 1 else 0 end;
                v_estacion(idx) := case when (substr( v_periodo(idx), 1,2) in ('04', '05','06','07','08','09')) then 'I' else 'V' end;

                Second One (This take about 1.5 second and the other 3 values takes about same time);
                select nvl(avg(facturable),0), max(flactual), max(flanterior), max(lactual), max(lanterior)
                into v_facturable_ant, v_flactual_ant, v_flanterior_ant, v_lactual_ant, v_lanterior_ant
                from mv_data_sec
                where periodo <> v_periodo(idx)
                and cod_empresa = v_cod_empresa(idx)
                and id_cliente = v_id_cliente(idx)
                and id_medidor = v_id_medidor_consulta
                and flactual = (select max(flactual)
                from mv_data_sec
                where cod_empresa = v_cod_empresa(idx)
                and id_cliente = id_cliente
                and id_medidor = v_id_medidor_consulta
                and periodo <> v_periodo(idx)
                and flactual < v_flactual(idx));

                if ((v_flactual(idx) = v_flanterior(idx)) and (v_flactual_ant <> v_flanterior_ant)) then
                v_Ecalendario(idx) := ((v_facturable_ant / (v_flactual_ant - v_flanterior_ant)) * (extract (day from last_day (v_flactual_ant)) - extract (day from (v_flactual_ant))));
                end if;
                if ((v_flactual_ant = v_flanterior_ant) and (v_flactual(idx) <> v_flanterior(idx))) then
                v_Ecalendario(idx) := ((v_facturable(idx)/ (v_flactual(idx) - v_flanterior(idx))) * (extract (day from (v_flactual(idx)))));
                end if;
                if ((v_flactual(idx) <> v_flanterior(idx)) and (v_flactual_ant <> v_flanterior_ant)) then
                v_Ecalendario(idx) := ((v_facturable(idx)/ (v_flactual(idx) - v_flanterior(idx))) * (extract (day from (v_flactual(idx) )))) + ((v_facturable_ant / (v_flactual_ant - v_flanterior_ant)) * (extract (day from last_day (v_flactual_ant)) - extract (day from (v_flactual_ant))));
                end if;

                And finally the bulk update (time is depreciable), for example the first 2 columns.

                forall r in v_rowid.FIRST .. v_rowid.LAST
                update mv_data_sec c
                set c.t_lectura = v_lectura(r),
                c.t_estacion = v_estacion(r)
                where c.rowid = v_rowid(r);
                • 5. Re: very long time to process big table
                  mhernancl
                  My only mission is to calculate the data and update the table, later a BI software query this table to show some graphics and reports.
                  • 6. Re: very long time to process big table
                    sb92075
                    Second One (This take about 1.5 second and the other 3 values takes about same time);
                    select nvl(avg(facturable),0), max(flactual), max(flanterior), max(lactual), max(lanterior)
                    into v_facturable_ant, v_flactual_ant, v_flanterior_ant, v_lactual_ant, v_lanterior_ant
                    from mv_data_sec
                    where periodo v_periodo(idx)
                    and cod_empresa = v_cod_empresa(idx)
                    and id_cliente = v_id_cliente(idx)
                    and id_medidor = v_id_medidor_consulta
                    and flactual = (select max(flactual)
                    from mv_data_sec
                    where cod_empresa = v_cod_empresa(idx)
                    and id_cliente = id_cliente
                    and id_medidor = v_id_medidor_consulta
                    and periodo v_periodo(idx)
                    and flactual < v_flactual(idx));
                    what is "(idx)" in WHERE clause above?
                    Since INTO is used, I conclude a single row is returned. Correct?
                    Have you TRACED this to see why it takes so long?
                    Why 1.5 seconds per row?
                    The problem is NOT the UPDATE per se, but obtaining the values for the UPDATE
                    • 7. Re: very long time to process big table
                      rp0428
                      where periodo v_periodo(idx)
                      is this 'periodo != v_periodo(idx)'? A left angle bracket that is followed by a rigth angle bracket gets removed.

                      I assume idx is the FORALL idx FROM 1..t.COUNT?
                      • 8. Re: very long time to process big table
                        mhernancl
                        all the (idx), are the indexes of the arrays obtained from the bulk collect. it's take about 1.5sec, but the execution plan is fine... but query it self it's over the same table with the 260 millions rows.
                        And, YES!, the specific problem is to get the values for the update.
                        • 9. Re: very long time to process big table
                          sb92075
                          mhernancl wrote:
                          the execution plan is fine
                          If the SELECT can not be made to run faster,
                          then nothing more can be done & you live with this reality.
                          • 10. Re: very long time to process big table
                            mhernancl
                            Yes it's ! =
                            and the idx are the indexes for the array obtained from the bulk collect
                            • 11. Re: very long time to process big table
                              mhernancl
                              there has to be a solution. which obviously i don't know, maybe some kind of way to cluster the process or anything of higher level... high density of computing is not a new reality. I think this may be recurrent when you try to implement a BI software to analyze history.
                              The solution is more than just tune the query
                              • 12. Re: very long time to process big table
                                sb92075
                                mhernancl wrote:
                                there has to be a solution. which obviously i don't know, maybe some kind of way to cluster the process or anything of higher level... high density of computing is not a new reality. I think this may be recurrent when you try to implement a BI software to analyze history.
                                The solution is more than just tune the query
                                Nothing is impossible for the person who does not actually have to do it.

                                I need to teach my goat to fly.
                                there has to be a solution. which obviously i don't know

                                Edited by: sb92075 on Feb 25, 2012 7:24 PM
                                • 13. Re: very long time to process big table
                                  rp0428
                                  That's what I thought. That may be the nut of your problem.

                                  If I understand this right then each time thru the loop of the PL/SQL table you process one record
                                  and then for that one record you are doing a full table scan to get MAX values for records that do not have the same 'periodo' value as that one record.

                                  Then for the next record in the loop you do another full table scan.

                                  You are essentially doing a full table scan for each record in the table; 260 million full table scans. In addition to that you are doing another full table scan for the subselect 'and flactual = (select max(flactual) . . .'.

                                  Those main and nested full table scans for each record is worse than slow-by-slow. I'd be surprised if it ever finished.

                                  Still thinking about it but will summarize my first thought (may change after I think it through more).

                                  1. Make the process so it can be done in parallel.

                                  2. Create a separate query to roll up the data in the table to get the avg and max values you want to use for the computation and update. This query can roll up by 'cod_empresa', 'id_cliente', and 'id_medidor'. You can roll it up for each period or for each 'anti period' (with period1, period2 and period3 the anti-period of period2 includes all other periods - so period1 & 3).

                                  3. Create a global temporaty table to hold the results of step 2 above. Could be a real-table if you want. Add appropriate indexes.

                                  4. Modify your procedure to take a parameter 'p_PERIODO' that will specify the period (and thus partition) to use

                                  5. Use a new query with a WHERE clause that specifies 'PERIODO = p_PERIODO'. This will partition prune to select only that periods (partition) data for update.

                                  6. Have the new query use data in the global temp table. The query will select data based on 'PERIODO' if you rolled it up that way.

                                  7. The procedure will strictly work on one partitions data at a time.

                                  Because the procedure now only works for the one partition whose key you pass as a parameter you can run the procedure in parallel for multiple partitions at a time and they will not overlap each other.
                                  • 14. Re: very long time to process big table
                                    rp0428
                                    Hey - I'm still working here. Can you guys keep the noise down?

                                    So what I am saying is don't do this
                                    Second One (This take about 1.5 second and the other 3 values takes about same time);
                                    select nvl(avg(facturable),0), max(flactual), max(flanterior), max(lactual), max(lanterior)
                                    into v_facturable_ant, v_flactual_ant, v_flanterior_ant, v_lactual_ant, v_lanterior_ant
                                    from mv_data_sec
                                    where periodo v_periodo(idx)
                                    and cod_empresa = v_cod_empresa(idx)
                                    and id_cliente = v_id_cliente(idx)
                                    and id_medidor = v_id_medidor_consulta
                                    and flactual = (select max(flactual) 
                                    from mv_data_sec
                                    where cod_empresa = v_cod_empresa(idx)
                                    and id_cliente = id_cliente
                                    and id_medidor = v_id_medidor_consulta
                                    and periodo v_periodo(idx)
                                    and flactual < v_flactual(idx));
                                    one record at a time in a loop.
                                    Do a rollup query like that one time globally using the grouping columns you need at the parition level. And save that result set into a global temp or a real work table.

                                    Then modify your procedure to query and update the data for one partition by selecting the appropriate rolled-up data from the temp table.
                                    1 2 Previous Next