Hinting Dynamically Generated SQL in Application Engine

One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement.  However, this can produce challenges that require you to add an optimiser hint to a gSQL statement.  In this post, I want to demonstrate how you can use the same techniques to dynamically generate valid Optimizer hints.

The following statement was generated by a step in a delivered Application Engine that had not been previously been changed.  The PS_XXX tables are custom tables, but the references are in custom meta-data that is used to dynamically generate the SQL.  I have added the hints and comments. 

The specific hints and comments are not important here, the point is how I managed to generate them.

Note that:

• I have added a comment that contains the name of the application engine step that generated this SQL.  This has can be added automatically with the aeid.sql script on my website.  It can be difficult to identify which step generated which static SQL statement.  It can be impossible to do that with dynamic SQL.  This identifying comment appears in the SQL.
• I have added a QB_NAME hint to the sub-query, and then the hint can refer to that sub-query (see related blog entry Hinting Sub-Queries on Oracle).
• Rather than specify the index name in the INDEX hint, I have used the table_name(column_list) construction to specify an index on a named table that starts with the named columns.  This is good general practice, the hint remains valid in the case that an index name changes.  However, it is also useful here because there is no meta-data that I can use to construct the name of the index.

UPDATE /*+INDEX(@SUB1 ln@SUB1 PS_XXX_SPLT_TA2(process_instance,resource_id))
          INDEX(PS_XXX_SPLT_TA1 PS_XXX_SPLT_TA1(process_instance,iu_line_type))*/
       /*ID-IU_PROCESSOR.P150.P150-010.S*/ PS_XXX_SPLT_TA1 
SET    iu_line_type='U'
WHERE  iu_line_type='2' 
AND    process_instance=12345678 
AND    setid_iu='XXXXX' 
AND    ledger_group = 'ACTUALS' 
AND    EXISTS (
  SELECT /*+QB_NAME(SUB1)*/ 'X'
  FROM   PS_XXX_SPLT_TA2 ln
  where  ln.EMPLID2 = PS_XXX_SPLT_TA1.EMPLID2
  and    ln.SEQ_NUM = PS_XXX_SPLT_TA1.SEQ_NUM
  and    ln.BUSINESS_UNIT = PS_XXX_SPLT_TA1.BUSINESS_UNIT
  and    ln.RESOURCE_ID = PS_XXX_SPLT_TA1.RESOURCE_ID
  AND    ln.setid_iu = 'XXXXX'
  AND    ln.ledger_group = 'ACTUALS'
  AND    ln.process_instance = 12345678
  GROUP BY ln.BUSINESS_UNIT_GL , ln.ledger, ln.OPERATING_UNIT, ln.BUSINESS_UNIT, ln.RESOURCE_ID, ln.EMPLID2, ln.SEQ_NUM     
  HAVING SUM(ln.RESOURCE_AMOUNT) <> 0)

So how did I get those hints into the SQL?

First of all remember that Application Engine doesn't know anything about SQL.  An Application Engine step is just a string of characters that will be submitted to the database.  Any PeopleCode macros are executed as the step is prepared and the resultant string is then set to the database.

The name of the PeopleSoft record being updated is in a variable line_wrk2_rec.  I can introduce the contents of the variable line_wrk2_rec with the %BIND() macro.  Normally a string bind variable is delimited by single quotes because it is used in a function or predicate, but the quotes can be suppressed with the NOQUOTES option.  I can convert the PeopleSoft record name to the database table name with the %Table() PeopleCode macro. 

So, in this example

Code in Application Engine Step	Expansion
%BIND(line_wrk_rec)	'XXX_SPLT_TA1'
%BIND(line_wrk_rec,NOQUOTES))	XXX_SPLT_TA1
%Table(%BIND(line_wrk_rec,NOQUOTES))	PS_XXX_SPLT_TA1

Note that delivered PeopleSoft SQL never specifies a row source alias on the table being updated because this would be invalid SQL on SQL Server.  Thus one SQL statement can be used on multiple platforms.  Although it is possible to have platform-specific steps in Application Engine, PeopleSoft development avoid this wherever possible because it increases their development overheads.  So the row source alias is the table name. 

I have used the expression %Table(%BIND(line_wrk_rec,NOQUOTES)) twice; once for the table alias and then again when I specify the index.  I want to force the use of an index on PS_XXX_SPLT_TA1 that leads on columns PROCESS_INSTANCE and RESOURCE_ID.  This is the code in the Application Engine step that generates the SQL statement above.

UPDATE /*+INDEX(@SUB1 ln@SUB1 %Table(%BIND(line_wrk2_rec,NOQUOTES))(process_instance,resource_id))
          INDEX(%Table(%BIND(line_wrk_rec,NOQUOTES)) %Table(%BIND(line_wrk_rec,NOQUOTES))(process_instance,iu_line_type))*/
          /*ID-IU_PROCESSOR.P150.P150-010.S*/%Table(%BIND(line_wrk_rec,NOQUOTES))
SET iu_line_type='U'
WHERE iu_line_type='2'
AND process_instance=%BIND(process_instance)
AND setid_iu=%BIND(iu_proc_002_aet.setid_ledger)
AND ledger_group = %BIND(iu_proc_002_aet.ledger_group)
AND EXISTS (
  SELECT /*+QB_NAME(SUB1)*/ 'X'
  FROM %Table(%BIND(line_wrk2_rec,NOQUOTES)) ln
  %BIND(iu_where_aet.iu_where_sql,NOQUOTES)
  %BIND(iu_group_by_aet.iu_group_by_sql,NOQUOTES)
  %BIND(iu_proc_002_aet.where_bu,NOQUOTES)
  AND ln.setid_iu = %BIND(iu_proc_002_aet.setid_ledger)
  AND ln.ledger_group = %BIND(iu_proc_002_aet.ledger_group)
  AND ln.process_instance = %ProcessInstance
  GROUP BY ln.%BIND(iu_sys_tran_aet.fieldname_bu_gl,NOQUOTES), ln.ledger%BIND(iu_proc_001_aet.iu_group_by_sql,NOQUOTES)
  HAVING SUM(ln.%BIND(iu_sys_tran_aet.fieldname_base_amt,NOQUOTES)) <> 0)

There is a problem here (at least there is in theory). If this statement executes for a different table the index hint will instruct the SQL to look for an index on that different table on the same columns.  The predicates in the where clauses are also derived from dynamic code.  If I were being absolutely rigorous, I would have added some procedural code in the preceding steps to build this part of the hints dynamically too, however, I am also trying to keep the customisation to a minimum in an area of code that is otherwise vanilla.

I have accepted a compromise.  If the step executes for a different table, the hint will probably be invalid because there is probably no such index on these columns on that table.  There is a risk that such an index does exist and so the hint could be valid but totally inappropriate to the situation because the predicates are totally different.  The result could be very poor performance.  However, in this case, in practice, this risk is zero, and the hint correctly results in appropriate behaviour in all scenarios.  Although this is something that you need to consider on a case by case basis