HINTS and VIEWS question

[Former Member]

Hi

I trying to optimise the query on a 6 table view. From looking at the way the view is queried and the columns that some tables provide (several tables only provide a single column in the view definition) I have created indexes for 3 of the tables to optimise the query.

I've broken down the view into a series of SELECTs in ABAP and using hints I can get about a 30% improvement. If I can get MaxDB to use the same hints when using the view the query would even be faster as it wouldn't have the ABAP overhead.

I've tried /*+ JOIN( INDEXACCESS("TAB3_{Z00"), INDEXACCESS("TAB4}Z00"), INDEXACCESS("TAB6~Z00")) */.

Does the JOIN hint work with views? Would a hint be likely to work?

Thanks

Doug

[Former Member]

The syntax for the JOIN hint is:

JOIN([T1_IDX];[T2IDX];[T3~IDX]..)

Say you want to use an index on the 3rd table specified in the FROM clause but let the optimiser choose the index for the other tables in the join, the hint is:

JOIN(;;'TABLE3~Z1')

[lbreddemann]

HI Doug,

you can put hints into the view definition - but as this usually is not particularly a good idea I assume you meant whether one can provide hints in the sql statement that uses the view, didn't you?

That does only work for some hints (see [WIKI|http://wiki.sdn.sap.com/wiki/display/MaxDB/MaxDBOptimizerHints]) for more information.

So the best option may be to replace the view access with a new join statement.

Since your description sounds as if you want to employ index-only strategies in a join statement I've to warn you: index-only strategies in joins can only be used for the entry table but not for the next tables.

Maybe you show us your optimization problem and we'll check whether we can improve it nevertheless.

regards,

Lars

[Former Member]

Hi Lars

I have done what you have suggested and broken down the view into a sequence of joins and generated an explain plan.

SELECT                                                                                
/*+                                                                                
JOIN(INDEXACCESS ("ILOA~TEMP"))                                                                  
*/                                                                                
T_00 . "MANDT" , T_00 . "AUFNR" , T_04 . "AUFPL" , T_04 . "APLZL" , T_00 . "IPHAS" ,             
  T_00 . "PRIOK" , T_00 . "ARTPR" , T_00 . "INGPR" , T_00 . "IWERK" , T_03 . "BEBER" ,             
  T_01 . "AUART" , T_01 . "ERDAT" , T_01 . "OBJNR" , T_04 . "OBJNR" "OP_OBJNR" , T_04 . "VORNR" ,  
  T_04 . "STEUS" , T_04 . "ARBID" , T_04 . "RSANZ" , T_04 . "LARNT" , T_04 . "RUECK" ,             
  T_04 . "RMZHL" , T_05 . "ARBEI" , T_05 . "ARBEH" , T_00 . "WARPL" , T_00 . "ABNUM" ,             
  T_02 . "GSTRP"                                                                                
FROM                                                                                
"AFIH" T_00 INNER JOIN "AUFK" T_01 ON T_01 . "MANDT" = ? AND T_01 . "AUFNR" = T_00 . "AUFNR" INNER
  JOIN "AFKO" T_02 ON T_02 . "MANDT" = ? AND T_02 . "AUFNR" = T_01 . "AUFNR" INNER JOIN "ILOA" T_03
  ON T_03 . "MANDT" = ? AND T_03 . "ILOAN" = T_00 . "ILOAN" INNER JOIN "AFVC" T_04 ON T_04 . "MANDT
  " = ? AND T_04 . "AUFPL" = T_02 . "AUFPL" INNER JOIN "AFVV" T_05 ON T_05 . "MANDT" = ? AND T_05 .
  "AUFPL" = T_04 . "AUFPL" AND T_05 . "APLZL" = T_04 . "APLZL"                                     
WHERE                                                                                
T_00 . "MANDT" = ? AND T_00 . "IWERK" = ? AND T_01 . "ERDAT" BETWEEN ? AND ? AND T_04 . "LARNT"  
  IN ( ? , ? , ? , ? ) 

You are correct about wanting to use an index-only strategy for several tables (ILOA T_03, AFVV T_05,

and AFKO T_02) as they only contribute 1 column to the result columns.

The above execution plan looks great as it uses the primary key for a number of the joins but the values specified in the WHERE

clause are filtered after the row is retrieved.

See next post for further information (this editor sucks)

[Former Member]

Continued from previous post:

I have simulated the view using a number of select statements and loops of internal tables. Creating the indexes and using the

hint eg INDEXACCESS("ILOA~TEMP") in the relevant SELECT had a positive impact in the query performance:

JOIN           TIME			TIME(WITH INDEX AND HINT) 	ROWS
T_00 and T_01	180s			180s 				73K
*T_03*		122s			 71s				73K
*T_02*		169s			 47s				73K
T_04		292s			292s				141K
*T_05*		318s			134s				141K

The explain plan

OWNER	TABLENAME	COLUMN OR INDEX		STRATEGY				PAGECOUNT 
	T_00		AFIH~Z01		RANGE CONDITION FOR INDEX		13042
						MANDT (USED INDEX COLUMN)                           
						IWERK (USED INDEX COLUMN)                           
						MANDT (USED KEY COLUMN)                             
	T_01					JOIN VIA MULTIPLE KEY COLUMNS		33127
						MANDT (USED KEY COLUMN)                             
						AUFNR (USED KEY COLUMN)                             
	T_02					JOIN VIA MULTIPLE KEY COLUMNS		42874
						MANDT (USED KEY COLUMN)                             
						AUFNR (USED KEY COLUMN)                             
	T_03					JOIN VIA MULTIPLE KEY COLUMNS		20135
						MANDT (USED KEY COLUMN)                             
						ILOAN (USED KEY COLUMN)                             
	T_04					JOIN VIA RANGE OF MULTIPLE KEY COLUMNS	75552
						MANDT (USED KEY COLUMN)                             
						AUFPL (USED KEY COLUMN)                             
	T_05					JOIN VIA MULTIPLE KEY COLUMNS		85860
						MANDT (USED KEY COLUMN)                             
						AUFPL (USED KEY COLUMN)                             
						APLZL (USED KEY COLUMN)                             
					NO TEMPORARY RESULTS CREATED                  
		SHOW			RESULT IS COPIED   , COSTVALUE IS		35012

Thanks

Doug

[lbreddemann]

>

> Hi Lars 
> You are correct about wanting to use an index-only strategy for several tables (ILOA T_03, AFVV T_05, 
> and AFKO T_02) as they only contribute 1 column to the result columns.
> The above execution plan looks great as it uses the primary key for a number of the joins but the values specified in the WHERE 
> clause are filtered after the row is retrieved.
> 
> See next post for further information (this editor sucks)

@Editor: FULL ACK!

Ok, Doug, time to become serious about this.

Please open a support message for this.

We're going to need direct DB access to analyse this in detail and to figure out the best option.

regards,

Lars

[Former Member]

Hi Lars

I have created a support message, the id is (0000144996 2010).

Regards

Doug