AMAZING!!! But this is serious! Below SQL statement takes more than 26 hours (26:35:12.03 to be exact) to execute. I doubt anyone can sort it out. I ain't going to sort out myself either. It's darn long, isn't it?
SELECT DISTINCT
CA.CODE_UC_PRESTATAIRE,
CA.CODE_CUC_PRESTATAIRE,
CA.COMPTE_ANALYTIQUE,
CA.CODE_PRESTATION,
M.NUM_ECRITURE,
M.NUM_MVT,
M.CODE_UC,
M.CODE_JOURNAL,
M.AGREGA,
M.REFERENCEE,
M.EXERCICE_COMPTABLE,
NVL(M.MONTANT_EURO,0),
TO_CHAR(E.DATE_COMPTABLE,'YYYY-MM-DD'),
M.NUM_RACINE_GENE,
M.SENS,
C.QUANTITE_MVT,
C.ELEMENT_VALORISATION,
E.MOIS_COMPTABLE,
TO_CHAR(CA.DATE_DERNIER_TRAITEMENT,'YYYY-MM-DD')
FROM ECRITURE_C E, /* 79900000 rows */
MOUVEMENT_C M, /* 226081169 rows */
COMPLEMENT_MOUVEMENT_C C, /* 189944438 rows */
TSF_CA_DEPENSES_REFACTURABLES CA, /* 52000 rows */
TSF_PRESTATION P /*12000 rows */
WHERE E.NUM_ECRITURE = M.NUM_ECRITURE
AND E.CODE_UC = M.CODE_UC
AND E.CODE_JOURNAL = M.CODE_JOURNAL
AND E.AGREGA = M.AGREGA
AND E.REFERENCEE = M.REFERENCEE
AND E.EXERCICE_COMPTABLE = M.EXERCICE_COMPTABLE
AND M.NUM_ECRITURE = C.NUM_ECRITURE (+)
AND M.NUM_MVT = C.NUM_MVT (+)
AND M.CODE_UC = C.CODE_UC (+)
AND M.CODE_JOURNAL = C.CODE_JOURNAL (+)
AND M.AGREGA = C.AGREGA (+)
AND M.REFERENCEE = C.REFERENCEE (+)
AND M.EXERCICE_COMPTABLE = C.EXERCICE_COMPTABLE (+)
AND M.CODE_UC_AFF = CA.CODE_UC_PRESTATAIRE
AND P.CODE_UC_PRESTATAIRE = CA.CODE_UC_PRESTATAIRE
AND P.CODE_CUC_PRESTATAIRE = CA.CODE_CUC_PRESTATAIRE
AND P.CODE_PRESTATION = CA.CODE_PRESTATION
AND P.STATUT_PRESTATION = 1
AND ((CA.CODE_CUC_PRESTATAIRE != ' ' AND
C.COMPLEMENT_UC = CA.CODE_CUC_PRESTATAIRE) OR
(CA.CODE_CUC_PRESTATAIRE = ' ' AND
(C.COMPLEMENT_UC = ' ' OR C.COMPLEMENT_UC is NULL)))
AND ((CA.COMPTE_ANALYTIQUE != ' ' AND
M.COMPTE_ANA_12 = CA.COMPTE_ANALYTIQUE) OR
(CA.COMPTE_ANALYTIQUE = ' ' AND
(M.COMPTE_ANA_12 = ' ' OR M.COMPTE_ANA_12 is NULL)))
AND (
(E.EXERCICE_COMPTABLE = TO_NUMBER(TO_CHAR(P.DATE_DEBUT_PRESTATION,'YYYY'))
AND E.MOIS_COMPTABLE >= TO_NUMBER(TO_CHAR(P.DATE_DEBUT_PRESTATION,'MM')))
OR
(E.EXERCICE_COMPTABLE > TO_NUMBER(TO_CHAR(P.DATE_DEBUT_PRESTATION,'YYYY')))
)
AND E.NATURE_ECRITURE = 'CPT'
AND (INSTR(',879020,8790214,87971,879740,',',' || RTRIM(M.NUM_RACINE_GENE) || ',') = 0)
AND (INSTR(',FO1,',',' || RTRIM(M.CODE_JOURNAL) || ',') = 0)
AND NOT EXISTS (SELECT 1
FROM TSF_DEPENSES_REFACTURABLES D
WHERE D.NUM_ECRITURE = M.NUM_ECRITURE
AND D.NUM_MVT = M.NUM_MVT
AND D.CODE_UC = M.CODE_UC
AND D.CODE_JOURNAL = M.CODE_JOURNAL
AND D.AGREGA = M.AGREGA
AND D.REFERENCEE = M.REFERENCEE
AND D.EXERCICE_COMPTABLE = M.EXERCICE_COMPTABLE);
~~~~~~~~~~~~~~~~~~~~~~~
Explain plan :
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 647
SORT UNIQUE 1 164 647
FILTER
FILTER
NESTED LOOPS OUTER
NESTED LOOPS 1 133 641
NESTED LOOPS 1 104 638
HASH JOIN 1 56 5
TABLE ACCESS FULL PROP_NSIC.TSF_PRESTATION 770 17 K 2
TABLE ACCESS FULL PROP_NSIC.TSF_CA_DEPENSES_REFACTURABLES 4 K 135 K 2
TABLE ACCESS BY INDEX ROWID PROP_NSIC.MOUVEMENT_C 1 K 52 K 633
INDEX RANGE SCAN PROP_NSIC.I_MVT_UCAFF_RGENE 1 K 422
TABLE ACCESS BY INDEX ROWID PROP_NSIC.ECRITURE_C 15 M 438 M 3
INDEX UNIQUE SCAN PROP_NSIC.PK_ECRITURE_C 15 M 2
TABLE ACCESS BY INDEX ROWID PROP_NSIC.COMPLEMENT_MOUVEMENT_C 187 M 5G 3
INDEX UNIQUE SCAN PROP_NSIC.PK_COMPLEMENT_MOUVEMENT_C 187 M 2
INDEX UNIQUE SCAN PROP_NSIC.PK_TSF_DEPENSES_REFACTURABLES 1 19 1
~~~~~~~~~~~~~~~~~~~~~~~~~~
timing :
26:35:12.03
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DDLS:
~~~~~~~~~~~~~~~~~~~~~~~~
--
-- ECRITURE_C (Table)
--
-- Count: 79211958 rows
CREATE TABLE PROP_NSIC.ECRITURE_C ( NUM_ECRITURE NUMBER(9) NOT NULL, CODE_UC NUMBER(5) NOT NULL, CODE_JOURNAL CHAR(3) NOT NULL, AGREGA NUMBER(1) NOT NULL, REFERENCEE NUMBER(1) NOT NULL, EXERCICE_COMPTABLE NUMBER(4) NOT NULL, NUM_CP CHAR(8) NOT NULL, REF_PAIEMENT VARCHAR2(15) NULL, EXERCICE_COMPTABLE_AGR NUMBER(4) NULL, CODE_UC_AGR NUMBER(5) NULL, CODE_JOURNAL_AGR CHAR(3) NULL, AGREGA_AGR NUMBER(1) NULL, REFERENCEE_AGR NUMBER(1) NULL, NUM_ECRITURE_AGR NUMBER(9) NULL, MOIS_COMPTABLE NUMBER(2) NOT NULL, CODE_PERIODE CHAR(1) NOT NULL, DATE_ENREGISTREMENT DATE NOT NULL, DATE_EVENEMENT DATE NOT NULL, DATE_COMPTABLE DATE NULL, MOIS_REDRESSEMENT NUMBER(2) NULL, REF_EXTERNE VARCHAR2(15) NULL, IDENTIFIANT_EXTERNE CHAR(12) NULL, ZONE_TECHNIQUE VARCHAR2(30) NULL, ANNULATION_SAISIE NUMBER(1) NOT NULL, LIBELLE_ECR VARCHAR2(30) NULL, MODIFIABLE NUMBER(1) NOT NULL, DATE_DERNIERE_MAJ DATE NULL, NATURE_ECRITURE CHAR(3) NOT NULL, CODE_DEVISE CHAR(3) NOT NULL, DATE_VALEUR DATE NULL, TX_CONV_CPTE NUMBER(9,7) NULL, ID_SNCF_RFF CHAR(2) NULL ) TABLESPACE TBS_T250M_2 PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING NOCACHE NOPARALLEL;
--
-- I_ECR_C_CODE_JOURNAL (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.I_ECR_C_CODE_JOURNAL ON PROP_NSIC.ECRITURE_C (CODE_JOURNAL) LOGGING TABLESPACE TBS_X250M_1 PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- I_ECRITURE_C_DATECPT (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.I_ECRITURE_C_DATECPT ON PROP_NSIC.ECRITURE_C (DATE_COMPTABLE) LOGGING TABLESPACE TBS_X250M_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- I_ECRITURE_C_UC (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.I_ECRITURE_C_UC ON PROP_NSIC.ECRITURE_C (CODE_UC) LOGGING TABLESPACE TBS_X250M_3 PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_2_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.IT_2_ECRITURE_C ON PROP_NSIC.ECRITURE_C (NUM_ECRITURE_AGR, CODE_UC_AGR, CODE_JOURNAL_AGR, AGREGA_AGR, REFERENCEE_AGR, EXERCICE_COMPTABLE_AGR) LOGGING TABLESPACE TBS_X5M PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 400K NEXT 400K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_4_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.IT_4_ECRITURE_C ON PROP_NSIC.ECRITURE_C (DATE_EVENEMENT) LOGGING TABLESPACE TBS_X250M_3 PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_7_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.IT_7_ECRITURE_C ON PROP_NSIC.ECRITURE_C (REF_EXTERNE) LOGGING TABLESPACE TBS_X250M_2 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_8_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.IT_8_ECRITURE_C ON PROP_NSIC.ECRITURE_C (IDENTIFIANT_EXTERNE) LOGGING TABLESPACE TBS_X50M PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 50M NEXT 50M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_9_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE INDEX PROP_NSIC.IT_9_ECRITURE_C ON PROP_NSIC.ECRITURE_C (EXERCICE_COMPTABLE, MOIS_COMPTABLE, CODE_PERIODE, NUM_ECRITURE, CODE_UC, CODE_JOURNAL, AGREGA, REFERENCEE, NATURE_ECRITURE, ANNULATION_SAISIE) LOGGING TABLESPACE TBS_X250M_5 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) PARALLEL ( DEGREE 4 INSTANCES 1 );
--
-- PK_ECRITURE_C (Index)
--
-- Dependencies:
-- ECRITURE_C (Table)
--
CREATE UNIQUE INDEX PROP_NSIC.PK_ECRITURE_C ON PROP_NSIC.ECRITURE_C (NUM_ECRITURE, CODE_UC, CODE_JOURNAL, AGREGA, REFERENCEE, EXERCICE_COMPTABLE) LOGGING TABLESPACE TBS_X250M_5 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
~~~~~~~~~~~~~~~~~~~~~~~
--
-- COMPLEMENT_MOUVEMENT_C (Table)
--
-- Count: 187722829 rows
CREATE TABLE PROP_NSIC.COMPLEMENT_MOUVEMENT_C ( NUM_ECRITURE NUMBER(9) NOT NULL,
NUM_MVT NUMBER(4) NOT NULL,
CODE_UC NUMBER(5) NOT NULL,
CODE_JOURNAL CHAR(3) NOT NULL,
AGREGA NUMBER(1) NOT NULL,
REFERENCEE NUMBER(1) NOT NULL,
EXERCICE_COMPTABLE NUMBER(4) NOT NULL,
COMPLEMENT_UC CHAR(5) NULL,
ELEMENT_VALORISATION NUMBER(10,2) NULL,
QUANTITE_MVT NUMBER(15,2) NULL,
INDIC_TVA CHAR(2) NULL,
MOIS_COMPTABLE NUMBER(2) NULL )
TABLESPACE TBS_T250M_1 PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 250M NEXT 892328K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
LOGGING NOCACHE NOPARALLEL;
--
-- IT_1_COMPLEMENT_MOUVEMENT_C (Index)
--
-- Dependencies:
-- COMPLEMENT_MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.IT_1_COMPLEMENT_MOUVEMENT_C ON PROP_NSIC.COMPLEMENT_MOUVEMENT_C (COMPLEMENT_UC) LOGGING TABLESPACE TBS_X250M_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--
-- MOUVEMENT_C (Table)
--
-- Count: 223721272 rows
CREATE TABLE PROP_NSIC.MOUVEMENT_C ( NUM_ECRITURE NUMBER(9) NOT NULL, NUM_MVT NUMBER(4) NOT NULL, CODE_UC NUMBER(5) NOT NULL, CODE_JOURNAL CHAR(3) NOT NULL, AGREGA NUMBER(1) NOT NULL, REFERENCEE NUMBER(1) NOT NULL, EXERCICE_COMPTABLE NUMBER(4) NOT NULL, CODE_UC_AFF NUMBER(5) NOT NULL, NUM_RACINE_GENE CHAR(8) NOT NULL, MONTANT NUMBER(15,2) NOT NULL, SENS CHAR(1) NOT NULL, COMPTE_ANA_12 CHAR(12) NULL, COMPLEMENT_COMPTE_ANA CHAR(4) NULL, NUM_RACINE_ANA CHAR(12) NOT NULL, LONGUEUR_ACTIVE NUMBER(2) NOT NULL, NUM_MVT_FONC NUMBER(4) NULL, MONTANT_DEVISE NUMBER(15,2) NULL, FILIERE_PRODUCTION CHAR(12) NULL, MONTANT_EURO NUMBER(15,2) NULL, MOIS_COMPTABLE NUMBER(2) NULL ) TABLESPACE TBS_T250M_1 PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING NOCACHE NOPARALLEL;
--
-- I_MOUVEMENT_C_11 (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.I_MOUVEMENT_C_11 ON PROP_NSIC.MOUVEMENT_C (NUM_ECRITURE, CODE_UC, CODE_JOURNAL, AGREGA, REFERENCEE, EXERCICE_COMPTABLE) LOGGING TABLESPACE TBS_X250M_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) PARALLEL ( DEGREE 4 INSTANCES 1 );
--
-- I_MOUVEMENT_C_12 (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.I_MOUVEMENT_C_12 ON PROP_NSIC.MOUVEMENT_C (COMPTE_ANA_12) LOGGING TABLESPACE TBS_X250M_2 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- I_MOUVEMENT_C_13 (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.I_MOUVEMENT_C_13 ON PROP_NSIC.MOUVEMENT_C (COMPTE_ANA_12, NUM_RACINE_GENE) LOGGING TABLESPACE TBS_X250M_2 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 500 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- I_MVT_NUM_RAC_GENE (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE UNIQUE INDEX PROP_NSIC.I_MVT_NUM_RAC_GENE ON PROP_NSIC.MOUVEMENT_C (NUM_RACINE_GENE, NUM_ECRITURE, CODE_UC, CODE_JOURNAL, EXERCICE_COMPTABLE, REFERENCEE, AGREGA, NUM_MVT) LOGGING TABLESPACE TBS_X250M_4 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- I_MVT_UCAFF_RGENE (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.I_MVT_UCAFF_RGENE ON PROP_NSIC.MOUVEMENT_C (CODE_UC_AFF, NUM_RACINE_GENE) LOGGING TABLESPACE TBS_X250M_3 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 624176K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- IT_2_MOUVEMENT_C (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE INDEX PROP_NSIC.IT_2_MOUVEMENT_C ON PROP_NSIC.MOUVEMENT_C (COMPLEMENT_COMPTE_ANA) LOGGING TABLESPACE TBS_X250M_1 PCTFREE 0 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 100 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
--
-- PK_MOUVEMENT_C (Index)
--
-- Dependencies:
-- MOUVEMENT_C (Table)
--
CREATE UNIQUE INDEX PROP_NSIC.PK_MOUVEMENT_C ON PROP_NSIC.MOUVEMENT_C (NUM_ECRITURE, CODE_UC, CODE_JOURNAL, AGREGA, REFERENCEE, EXERCICE_COMPTABLE, NUM_MVT) LOGGING TABLESPACE TBS_X250M_5 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 250M NEXT 250M MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL;
No comments:
Post a Comment
Do provide your constructive comment. I appreciate that.