One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement. However, this can produce challenges should decide that you also need to add an optimiser hint to a SQL statement. In this post I want to demonstrate how you can also 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.
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|
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 was being absolutely rigorous, I would have added some procedural code in 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 results in appropriate behaviour in all scenarios. Although this is something that you need to consider on a case by case basis