1 Reply Latest reply: Nov 14, 2012 11:34 AM by Gary Graham-Oracle RSS

    count(*)...delay

    974021
      greetings,

      Hello
      First apologize for the bad English; I am a beginner and I'm having trouble making a COUNT (*) to a table X with 400 million approx. someone could guide me how to do this count with in less time, and do I need for a process within a stored procedure the problem that this count is delaying near 2HR and delay the overall process and 5HR, as this data could take the otherwise table, view materialized ?¿ ...

      Greetings and thanks from now...

      Structure:

      CREATE TABLE "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA"
      (     "MARCA_IDU" VARCHAR2(1 BYTE),
           "FHC_COD_PRO_CMR_CD" NUMBER(10,0),
           "FHC_CNC_FTB_PAR_CD" NUMBER(10,0),
           "FHC_HST_CNC_FTB_FF" VARCHAR2(10 BYTE),
           "FHC_COD_CIC_CD" NUMBER(5,0),
           "FHC_MES_FTD_FF" NUMBER(5,0),
           "FHC_IND_PRT_IN" VARCHAR2(1 BYTE),
           "FHC_FAC_AVI_COB_IN" VARCHAR2(1 BYTE),
           "FHC_IND_LIQ_IN" VARCHAR2(1 BYTE),
           "FHC_IND_VAL_IN" VARCHAR2(1 BYTE),
           "FHC_IND_PRC_NDO_IN" VARCHAR2(1 BYTE),
           "FHC_IND_FTB_IN" VARCHAR2(1 BYTE),
           "FHC_IND_FTC_ADE_CD" VARCHAR2(1 BYTE),
           "FHC_IND_RNT_PAA_IN" VARCHAR2(1 BYTE),
           "FHC_IND_ANM_IN" VARCHAR2(1 BYTE),
           "FHC_IND_RPC_IN" VARCHAR2(1 BYTE),
           "FHC_COD_CLI_CD" NUMBER(10,0),
           "FHC_COD_CTA_CD" NUMBER(10,0),
           "FHC_PRO_SER_CTO_CD" NUMBER(13,0),
           "FHC_COD_CNC_FTB_CD" NUMBER(10,0),
           "FHC_TIP_CNC_FTB_CD" VARCHAR2(2 BYTE),
           "FHC_COD_EST_CD" VARCHAR2(2 BYTE),
           "FHC_COD_SEU_CIC_CD" NUMBER(5,0),
           "FHC_TIP_PRO_CMR_CD" NUMBER(10,0),
           "FHC_SBT_PRO_CMR_CD" NUMBER(10,0),
           "FHC_COD_SCC_CGO_CD" VARCHAR2(20 BYTE),
           "FHC_COD_ANM_CD" NUMBER(5,0),
           "FHC_COD_CNF_CD" NUMBER(10,0),
           "FHC_COD_FAC_CD" NUMBER(10,0),
           "FHC_UNI_MED_MON_CD" NUMBER(10,0),
           "FHC_UNI_PRC_NDO_SN" NUMBER(10,0),
           "FHC_PRO_CMR_DER_CD" NUMBER(10,0),
           "FHC_COD_EMP_CD" NUMBER(10,0),
           "FHC_COD_ACU_SUM_CD" NUMBER(10,0),
           "FHC_AGR_COD_FTC_CD" NUMBER(10,0),
           "FHC_COD_CAS_CD" NUMBER(10,0),
           "FHC_COD_IDE_PRI_CD" NUMBER(10,0),
           "FHC_COD_RPC_CD" NUMBER(10,0),
           "FHC_DSD_CNC_FTB_FF" VARCHAR2(10 BYTE),
           "FHC_ULT_FTC_ANR_FF" VARCHAR2(10 BYTE),
           "FHC_FEC_EXO_ACR_FE" VARCHAR2(10 BYTE),
           "FHC_FEC_CIE_FF" VARCHAR2(10 BYTE),
           "FHC_IMP_VAL_IM" NUMBER(20,4),
           "FHC_IMP_CNV_IM" NUMBER(20,4),
           "FHC_IMP_PRC_NDO_IM" NUMBER(20,4),
           "FHC_IMP_TOT_IMT_IM" NUMBER(20,4),
           "FHC_TIP_PRE_CD" VARCHAR2(2 BYTE),
           "FHC_OPD_LOG_IN" NUMBER(5,0),
           "FHC_DSD_RPN_FAC_FF" VARCHAR2(10 BYTE),
           "FHC_POR_RDC_PRC_NU" NUMBER(9,4),
           "FHC_TIM_ULT_MOD_TS" VARCHAR2(26 BYTE),
           "FHC_PCS_ULT_MOD_NO" VARCHAR2(8 BYTE),
           "FHC_COD_NUM_FAC_CD" VARCHAR2(20 BYTE),
           "FHC_TIP_DOC_IPR_CD" NUMBER(5,0),
           "FHC_COD_RPN_PAQ_CD" NUMBER(10,0),
           "FHC_COD_PAQ_CD" NUMBER(10,0),
           "FHC_COD_PRY_CD" NUMBER(10,0),
           "FHC_SCC_PYO_NU" NUMBER(5,0),
           "FHC_LIT_GLO_DS" VARCHAR2(40 BYTE),
           "FHC_COD_OPR_CMR_CD" NUMBER(10,0),
           "FHC_COD_PRO_SER_CD" NUMBER(10,0),
           "TAG" VARCHAR2(100 BYTE)
      ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;


      CREATE INDEX "CHL_TDC_REP_PROD"."IDX1_HISTORICO_RENTA" ON "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA" ("FHC_COD_CIC_CD")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;

      CREATE INDEX "CHL_TDC_REP_PROD"."IDX2_HISTORICO_RENTA" ON "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA" ("FHC_DSD_CNC_FTB_FF")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;

      CREATE INDEX "CHL_TDC_REP_PROD"."IDX3_HISTORICO_RENTA" ON "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA" ("FHC_COD_PRO_CMR_CD")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;

      CREATE INDEX "CHL_TDC_REP_PROD"."IDX4_HISTORICO_RENTA" ON "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA" ("TAG")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;

      CREATE INDEX "CHL_TDC_REP_PROD"."IDX5_HISTORICO_RENTA" ON "CHL_TDC_REP_PROD"."TDC_HISTORICO_RENTA" ("FHC_HST_CNC_FTB_FF")
      PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "CHL_TDC_REP_PROD" ;

      Edited by: user8678662 on Nov 13, 2012 9:58 AM
        • 1. Re: count(*)...delay
          Gary Graham-Oracle
          Hi,

          Welcome to our forum, but note it is intended only for questions regarding the SQL Developer tool. A more appropriate forum may be:
          PL/SQL

          The trick to navigating to the correct forum is realizing SQL and PL/SQL are database languages rather than application development tools. Look at the Forum Home link at the top of each forum page, click through Forum Home > Database > SQL and PL/SQL to get to the correct forum.

          -Gary