Re: Fast refresh takes eternity

From: News <Contact_404_at_hotmail.com>
Date: 27 Apr 2006 06:57:57 -0700
Message-ID: <1146146277.467225.106200@j33g2000cwa.googlegroups.com>

stevedhoward_at_gmail.com a écrit :
> What does the explain plan for the MV query say?

This is the very elementary query and takes 2 seconds to run despite non optimal plan (because of nested loop).

SELECT STATEMENT,33697844,16823680,-351260288

	NESTED LOOPS,33697844,16823680,-351260288
		HASH JOIN,179628,16759108,1264420912
			TABLE ACCESS (FULL)--ACAL_DECL (TABLE),16,78084,6168636
			HASH JOIN,91224,16759108,-59548620
				TABLE ACCESS (FULL)--ACAL_DECL (TABLE),16,78084,6168636
				PARTITION RANGE (ALL),16211,16759108,-1383518152
					HASH JOIN,16211,16759108,-1383518152
						TABLE ACCESS (FULL)--A_TYP_PRSL (TABLE),5,10350,1542150
						HASH JOIN,12440,16759108,414342052
							TABLE ACCESS (FULL)--A_RISQ (TABLE),5,22976,1884032
							HASH JOIN,9047,16759108,-959904804
								TABLE ACCESS (FULL)--A_PERIODE_DECL (TABLE),3,29450,912950
								HASH JOIN,7294,16906497,-1454675800
									TABLE ACCESS (FULL)--A_COM_ASSUJ_TRANS (TABLE),4,30182,1026188
									TABLE ACCESS (FULL)--F_LGN_DECL_1_8
(TABLE),5386,16906497,-2029496698
		PARTITION RANGE (ITERATOR),2,1,157
			TABLE ACCESS (BY LOCAL INDEX ROWID)--A_PJ_DEBIT (TABLE),2,1,157
				INDEX (UNIQUE SCAN)--A_PJ_DEBIT1_PK (INDEX (UNIQUE)),1,1,0

But the refresh statement is different and looks like (from trace file):

INSERT INTO <MV>
SELECT /*+ NO_MERGE("JV$") */ /*+ */ ... FROM ( SELECT "MAS$"."ROWID" "RID$" , "MAS$".*

         FROM "IMMAT"."A_RISQ" "MAS$"
	WHERE ROWID IN (SELECT  /*+ CARDINALITY(MAS$ 5332)  NO_SEMIJOIN */
CHARTOROWID("MAS$"."M_ROW$$") RID$
	FROM "IMMAT"."MLOG$_A_RISQ" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :1 ))

AS OF SNAPSHOT (:2) "JV$",
"IMMAT"."ACAL_DECL" AS OF SNAPSHOT (:2) "MAS$1",
"IMMAT"."ACAL_DECL" AS OF SNAPSHOT (:2) "MAS$2",
"DECL"."A_TYP_PRSL" AS OF SNAPSHOT (:2) "MAS$3",
"DECL"."A_PJ_DEBIT" AS OF SNAPSHOT (:2) "MAS$4",
"DECL"."A_PERIODE_DECL" AS OF SNAPSHOT (:2) "MAS$5",
"DECL"."A_COM_ASSUJ_TRANS" AS OF SNAPSHOT (:2) "MAS$6",
"DECL"."F_LGN_DECL_1_8" AS OF SNAPSHOT (:2) "MAS$7"
WHERE "MAS$2"."A_CAL_DECL_DT_J"="MAS$7"."F_LGN_DECL_1_8_DT_DEB_LGN_PJ1"   AND "MAS$1"."A_CAL_DECL_DT_J"="MAS$7"."F_LGN_DECL_1_8_DT_FIN_LGN_PJ1"

  AND "MAS$6"."UR_TRAITEMENT"="MAS$7"."UR_TRAITEMENT"
  AND "MAS$6"."A_COM_ASSUJ_COM_INSEE"="MAS$7"."F_LGN_DECL_1_8_NO_CMN"
  AND "MAS$5"."UR_TRAITEMENT"="MAS$7"."UR_TRAITEMENT"
  AND "MAS$5"."A_PER_NUM"="MAS$7"."F_LGN_DECL_1_8_PERIODE"
  AND "MAS$4"."UR_TRAITEMENT"="MAS$7"."UR_TRAITEMENT"
  AND "MAS$4"."A_PJ_DEB_NUM_PJ"="MAS$7"."F_LGN_DECL_1_8_NUM_PJ"
  AND "JV$"."UR_TRAITEMENT"="MAS$7"."UR_TRAITEMENT"
  AND "JV$"."A_RISQ_CD_RISQ"="MAS$7"."F_LGN_DECL_1_8_CD_RSQ_AT"
  AND "MAS$3"."UR_TRAITEMENT"="MAS$7"."UR_TRAITEMENT"
  AND "MAS$3"."A_TYP_PRSL_CD"="MAS$7"."F_LGN_DECL_1_8_CD_TYP_PRSL"

Received on Thu Apr 27 2006 - 08:57:57 CDT