This discussion is archived
1 Reply Latest reply: Nov 14, 2012 9:34 AM by Gary Graham RSS

count(*)...delay

974021 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    Hi,

    Welcome to our forum, but note it is intended only for questions regarding the SQL Developer tool. A more appropriate forum may be:
    SQL and 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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points