Wrong search strategy after kernel upgrade

[Former Member]

Hello,

we get sometimes a wrong search strategy after upgrading the version 7.7.07.16 to version 7.8.02.37.

search strategy before:

SCHEMANAME	TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
OMSDEV	PARTYPARTIES		DIFFERENT STRATEGIES FOR OR-TERMS	4834
		IDX_PARTYPARTIES_000	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		IKMAINPARTY	(USED INDEX COLUMN)
		VKSUBPARTYTYPE	(USED INDEX COLUMN)
		IKMAINPARTY	(USED KEY COLUMN)
			RANGE CONDITION FOR KEY
		IKMAINPARTY	(USED KEY COLUMN)
		IDX_PARTYPARTIES_000	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		IKMAINPARTY	(USED INDEX COLUMN)
		VKSUBPARTYTYPE	(USED INDEX COLUMN)
		IKMAINPARTY	(USED KEY COLUMN)
		IDX_PARTYPARTIES_000	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		IKMAINPARTY	(USED INDEX COLUMN)
		VKSUBPARTYTYPE	(USED INDEX COLUMN)
		IKMAINPARTY	(USED KEY COLUMN)
	PARTYTYPESUB	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			TABLE HASHED
	PARTYTYPEMAIN	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			TABLE HASHED
OMSDEV	PARTY	IKPARTY	JOIN VIA KEY COLUMN	13765
		NO TEMPORARY RESULTS CREATED
OMSDEV	PARTY		DIFFERENT STRATEGIES FOR OR-TERMS	13765
			EQUAL CONDITION FOR KEY
		IKPARTY	(USED KEY COLUMN)
		EQUAL CONDITION FOR KEY
		IKPARTY	(USED KEY COLUMN)
	PARTYPARTYTYPES	IKPARTY	JOIN VIA KEY RANGE	1178
	PARTYTYPE	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			NO TEMPORARY RESULTS CREATED
OMSDEV	PARTYPARTIES		DIFFERENT STRATEGIES FOR OR-TERMS	4834
		IDX_PARTYPARTIES_004	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		IKSUBPARTY	(USED INDEX COLUMN)
		IDX_PARTYPARTIES_004	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		IKSUBPARTY	(USED INDEX COLUMN)
		IDX_PARTYPARTIES_001	RANGE CONDITION FOR INDEX
			ADDNL. QUALIFICATION ON INDEX
		VKMAINPARTYTYPE	(USED INDEX COLUMN)
	PARTYTYPESUB	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
	PARTYTYPEMAIN	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
OMSDEV	PARTY	IKPARTY	JOIN VIA KEY COLUMN	13765
			NO TEMPORARY RESULTS CREATED
INTERNAL	TEMPORARY RESULT		TABLE SCAN	1
	JDBC_CURSOR_17		RESULT IS COPIED   , COSTVALUE IS	11046
	JDBC_CURSOR_17		QUERYREWRITE - APPLIED RULES:
	JDBC_CURSOR_17		SimplifyPredicates	2
	JDBC_CURSOR_17		DistinctPushDownTo	5
	JDBC_CURSOR_17		DistinctPushDownFrom	4
	JDBC_CURSOR_17		PushDownPredicates	1
	JDBC_CURSOR_17		ConvertOrToIn	3
	JDBC_CURSOR_17		AddLocalPredicates	3

now:

SCHEMANAME	TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
	PARTYTYPEMAIN		TABLE SCAN	1
OMSREAL	PARTYPARTIES	IDX_PARTYPARTIES_000	JOIN VIA RANGE OF MULTIPLE INDEXED COL.	6367
		VKMAINPARTYTYPE	(USED INDEX COLUMN)
	PARTYTYPESUB	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			TABLE HASHED
OMSREAL	PARTY	IKPARTY	JOIN VIA KEY COLUMN	14356
			NO TEMPORARY RESULTS CREATED
OMSREAL	PARTY	IDX_PARTY003	RANGE CONDITION FOR INDEX	14356
			ADDNL. QUALIFICATION ON INDEX
		BACTIV	(USED INDEX COLUMN)
		DVALIDTO	(USED INDEX COLUMN)
	PARTYPARTYTYPES	IKPARTY	JOIN VIA KEY RANGE	1173
	PARTYTYPE	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			TABLE HASHED
			NO TEMPORARY RESULTS CREATED
	PARTYTYPEMAIN		TABLE SCAN	1
OMSREAL	PARTYPARTIES	IDX_PARTYPARTIES_000	JOIN VIA RANGE OF MULTIPLE INDEXED COL.	6367
		VKMAINPARTYTYPE	(USED INDEX COLUMN)
	PARTYTYPESUB	VKPARTYTYPE	JOIN VIA KEY COLUMN	1
			TABLE HASHED
OMSREAL	PARTY	IKPARTY	JOIN VIA KEY COLUMN	14356
			NO TEMPORARY RESULTS CREATED
INTERNAL	TEMPORARY RESULT		TABLE SCAN	1
	JDBC_CURSOR_86		RESULT IS COPIED   , COSTVALUE IS	1488972
	JDBC_CURSOR_86		QUERYREWRITE : APPLIED RULES:
	JDBC_CURSOR_86		SubstituteViews	1
	JDBC_CURSOR_86		DistinctPushDown	1
	JDBC_CURSOR_86		PushDownPredicates	1
	JDBC_CURSOR_86		ConvertOrToIn	1

used statement:

SELECT  ikParty, vkPartyType, vPartyClass, ikDependParty, vkDependPartyType, vDependPartyClass, vDepend, vParty, iParent, vNumber, vName1,                  vName2, vStreet, vStreetNo, vPOB, vNation, vRegion, vPostcode, vPOBPostcode, vTown, vDistrict, fLatitude, fLongitude, vContactPerson,                    vVATNo, vTaxNo, vLogoPath, vLanguage, vLanguageComm, vTZ, vClassNamePerl, dValidFrom, dValidTo, bActiv

FROM ViewPartyDepend

WHERE ((( ( ViewPartyDepend.vNumber = 'XXX )  OR  ( ViewPartyDepend.vParty = 'XXX' ) ))

  AND (((( ( ViewPartyDepend.vDepend = 'MAIN' OR ViewPartyDepend.vDepend = 'CURR' OR ViewPartyDepend.vDepend = 'SUB' ) 

      AND  ( ViewPartyDepend.vkPartyType = 'SP' ) 

      AND  ( ViewPartyDepend.vkDependPartyType = 'GOP' OR ViewPartyDepend.vkDependPartyType = 'HWS' ) 

      AND  ( ViewPartyDepend.vPartyClass = 'SP' ) 

      AND  ( ViewPartyDepend.ikParty = 20001 ) ))

    OR (( ( ViewPartyDepend.vDepend = 'CURR' OR ViewPartyDepend.vDepend = 'SUB' ) 

      AND  ( ViewPartyDepend.vkPartyType = 'CO' ) 

      AND  ( ViewPartyDepend.vkDependPartyType = 'GOP' OR ViewPartyDepend.vkDependPartyType = 'HWS' ) 

      AND  ( ViewPartyDepend.vPartyClass = 'CO' ) 

      AND  ( ViewPartyDepend.ikParty = 30101 ) ))))

  AND (( ( ViewPartyDepend.bActiv = TRUE ) 

    AND  ( ViewPartyDepend.dValidFrom <= '2013-10-24' ) 

    AND  ( ViewPartyDepend.dValidTo >= '2013-10-24' ) ))) 

ORDER BY ViewPartyDepend.ikDependParty ASC

The "ViewPartyDepend" is a view with an UNION JOIN  on the tables Party and PartyParties.

Any ideas? How can we solve the problem?

Kind Regards

Alexander

[hannes_degenhart · Accepted solution]

Hi Alexander,

please check, i the deactivation of the Query Rewrite Rule "ConvertOrToIn" using the statement

   update queryrewriterules set active = 'NO' where rulename = 'ConvertOrToIn'

helps for your problem