MERGE/ USING - A beneficial SQL Statement

Often times in our JDA commands, we are faced with doing an INSERT, UPDATE or DELETE based on data in a table as it relates to another.  For example, I am loading Inventory Data into a transaction table to be posted for polling.  I possibly have the following scenarios:

• If my Inventory record exists in the destination table, then update it
• If the record does not exist, then insert it
• Possibly, if the record exists, but my inventory is in a different status (depends on specific use case), then delete it from the destination

To implement this logic, a MOCA Command would be something like this (pseudo code):

[select data from destination table where pk=@lodnum] catch(-1403)

|

if (@?=0)

  if (@data has wrong status)

    perform my delete of destination table

  else

    perform my update of destination table

else

  perform my insert of the destination table

This in itself is not difficult to follow from coding perspective.  However, we do have three different sets of code branches to manage and maintain.  The MERGE/USING statement (both SQL and Oracle support it with slight differences in usage and syntax) allows for all three possibilities to be encompassed in one statement.

The pseudo syntax on MERGE/USING is as follows.  The statement was first introduced in SQL 2008 and Oracle 9i. For specific syntax and usage as applicable to your needs and database, please consult the relevant Database Documentation.

MERGE <destination table> 

USING <source table> /* Can be a select */

ON ( key columns )

WHEN MATCHED <and extra clauses>

  THEN UPDATE....

WHEN NOT MATCHED <BY TARGET or SOURCE>

  THEN INSERT....

You only have options of MATCHED and NOT MATCHED with extra qualifiers to implement the possibility of a DELETE statement.  Also note that UPDATE and DELETE statements can be provided at the same time under the MATCHED clause.

The benefit is that using one SQL statement, all three DML operations can be performed.  From performance standpoint, we have one cursor open that is traversing the source table and doing operations in the destination table in one go.  Rather than doing multiple open/ close cursors inside a loop.

Please note that MERGE statement is optimized for merging a SET of data and not for Single Row operations.  In a multi-row SET, the performance gain of a MERGE/USING statement can be almost 15 times, that of doing the same operations as INSERT/UPDATE.  So it is best to use it when you have a need of a larger set of data to be merged with another table.  Also note that the entire MERGE operation is one DML statement and hence one COMMIT context.

Hope you can find a use case appropriate for this statement.

Utilizing MOCA Query Hooks

RedPrairie/ JDA introduced Hooks in MOCA Toolset.  The Hook being described here is referred as the “QueryHook” (See MOCA Developer Guide for details).  This feature allows to look for a given SQL before it is sent to the database engine and manipulate the SQL itself or possibly its result set.

Caution: As mentioned in the MOCA Developer’s guide also, “This hook should be very heavily evaluated before its usage is implemented”.  As certainly a wrong implementation can have unpredictable results.

Oracular has introduced a solution that simplifies this process and allows a client’s development team to change an SQL at run-time to their advantage.  This solution takes the complexity out of writing a new Java method for the QueryHook and instead provides a simple Text File where Query Patterns can be stored.

Why do we need to change SQL this way?

JDA MOCA platform allows great flexibility in providing mechanism for overriding standard functionality and still keeping the upgrade path open.  However, many times the SQL performance of a given query requires it to be changed.  Introducing an index may not yield the performance plan that one is looking for and the only viable option is to be able to rewrite the query.

If the Query happens to be in a Local Syntax command, the user can easily copy the command to an LES folder and change the SQL to their liking.  In doing so, they have closed the door to this command being fixed by JDA (as this is a complete override), but the solution and performance goals have been achieved.

Many times however, the SQL that needs to be tuned is inside some C/ Java program or part of RDT code.  These SQL commands can only be manipulated using the QueryHook option.

Oracular's Solution

MOCA QueryHook can be implemented by anyone with knowledge of Java and they can write their own Moca Hook to take advantage of this.

Oracular provided a simple usage for this for our developers as well as our clients where a file sitting in $LES\data folder contains the Query Pattern to look for and its replacement.  This allows for a simple process where a new SQL pattern can be introduced with much ease.

The following section will explain how to use the Oracular Solution.

Basic Components

Anyone needing to take advantage of this feature from Oracular will need the Mocahooks.jar file to exist in their $LES\lib folder and query_replacements.txt file to exist in their $LES\data folder.

Structure of Query Replacement File

The Query Replacement File has three components for each SQL that needs to be manipulated:

• Leading edge of the SQL to search for – This must be an exact match.  This string does not allow for any regular expressions for white space.  So, number of spaces, new line characters etc. are all important to be specified exactly
• Regular Expression String to match the rest of the SQL – This Regular expression allows to match and parse the rest of the SQL.  This expression serves two purposes:

o   Fully qualify the SQL

o   Find Groups of data inside the SQL to be used during the replacement (important concept that will be explained in the example illustration)

• SQL to replace with – This is the new SQL that is executed instead.  The Groups identified in the search can be used in the replacement SQL

The use and need of replacements is generally performance related.  However, it can be used to manipulate result set if needed.  However, if result set is being manipulated, be sure to verify that downstream processing can handle any changes to the result set.

Regular Expressions

The search string uses Regular Expressions (Regex) for searching through the SQL string.  Please note that Regular Expressions are used in many variations and have unique structures and implementations based on the usage.  For our purpose, only the Java Regular Expressions can be used (not Posix, PHP etc.)

The following are the commonly utilized expressions for our purpose:

• (?i)  - Starting the expression with this, specifies to ignore the case during the search
• (?s) – This modifier allows to ignore New-Line when searching for any character
• If both of the above modifiers are needed then only one ? in the beginning is needed (?is)
•  ^ - Asserts position as the start of the string
• \s+ - Look for white space.  But at least one.  Commonly this will be used before SQL key words as there needs to be at least one space before them (like from or where)
• \s* - Look for white space.  None is also OK
•  \( or \) – Parenthesis are part of Regex.  So if you need to specify a parenthesis in your expression, always precede that with a \
• (.*?) – Look for a group of characters.  Any character can match in this and whatever matches becomes a group (see below on using groups).  Groups are numbered from left to right.  So the first instance of (.*?) is group one ($1), next is Group two ($2) and so one
• .* - Look for any character to match
• Anything else mentioned in the expression is taken as is.  So, keywords like select, from, or operators like = etc. are to be specified as is

Please note that this is just a brief list of commonly needed search operators.  Based on your specific need, you may need to use more.

If you need to validate your expression, you can use the site below:

https://regex101.com/

In this site you can provide your regular expression string in the first field and the select statement you are searching for in the next field and it will verify for you if the match is going to occur or not.  Furthermore, it provides all the groups for you also.  See illustration below.

This screen also shows the explanation of your expressions:

Notice Group 1 is what the expressions was looking for first as (.*) and it is also color coded for your convenience.

Generally speaking the groups are used for all variable data that may possibly come in the SQL string.  As this data cannot be forecasted ahead of time, so the search string must be able to ignore it during the search, but still use it later when rebuilding the new string.

So, in this illustration the group data involves part of the SELECT statement and the variable data in the WHERE clause.

SQL Statement from the Trace File

It is very important to find the proper statement in the Trace file for utilization in the QueryHook.  JDA trace files show an SQL statement in three different forms:

• The original form based on the MOCA command sending it
•  The XLATE modification where Bind Variables are introduced
• The modified statement set to the SQL engine

For QueryHook purposes, we must utilize the XLATE version of the SQL.  Any other version may not properly yield a good search pattern and match.  The following is an illustration of how SQL takes different forms in the trace file:

Original SQL:

Executing SQL: select distinct          lodnum,          decode('lodnum', 'subnum', subnum, 'dtlnum',                 subnum, 'x') subnum,          decode('lodnum', 'dtlnum',                 dtlnum, 'x') dtlnum     from inventory_view     where (('lodnum' = 'lodnum' and lodnum = :var_lodnum)              or ('subnum' = 'lodnum' and subnum = :var_lodnum)              or dtlnum = :var_lodnum )

XLATE Version:

XLATE: select distinct          lodnum, (case when :q0 = :q1 then  subnum when :q0 = :q2 then                  subnum else  :q3 end) subnum, (case when :q4 = :q5 then                  dtlnum else  :q6 end) dtlnum     from inventory_view     where ((:q7 = :q8 and lodnum = :var_lodnum)              or (:q9 = :q10 and subnum = :var_lodnum)              or dtlnum = :var_lodnum )

UNBIND Version Sent to SQL Engine:

UNBIND: select distinct          lodnum, (case when 'lodnum' = 'subnum' then  subnum when 'lodnum' = 'dtlnum' then                  subnum else  'x' end) subnum, (case when 'lodnum' = 'dtlnum' then                  dtlnum else  'x' end) dtlnum     from inventory_view     where (('lodnum' = 'lodnum' and lodnum = '00100538930017771875')              or ('subnum' = 'lodnum' and subnum = '00100538930017771875')              or dtlnum = '00100538930017771875' )

Notice that the original version of the statement has a DECODE in it.  Then XLATE converted that to CASE as this was being passed to SQL Server, and then the UNBIND version added all the values for bind variables.

Illustration Example

For sake of illustration I will share two examples.  In Microsoft SQL, there is a common issue where any statement with OR does not yield a good query plan.   This statement if converted to a UNION provides a far better execution plan and performance.  Hence any such instances of SQL statements will be good candidates for the QueryHook.

Converting an "OR" to a "UNION"

The SQL mentioned above is the illustration for converting the OR statement to a UNION.  However, to keep the SQL conversion database independent, the search string did not want to assume that a DECODE will be converted to a CASE statement.  So, the search pattern is as follows:

select distinct          lodnum

$$$$$

(?is)select\s+distinct\s+lodnum\s*,(.*)from\s+inventory_view\s+where\s+\(\(\s*(.*?)\s*=\s*(.*?)\s+and\s+lodnum\s*=\s*(.*?)\s*\)\s+OR\s+\(\s*(.*?)\s*=\s*(.*?)\s+and\s+subnum\s+=\s+(.*?)\s*\)\s+OR\s+dtlnum\s+=\s+(.*?)\s*\)\s*$

$$$$$

select distinct lodnum,

$1

   from inventory_view

  where ($2 = $3

    and lodnum = $4)

  union

 select distinct lodnum,

 $1

   from inventory_view

  where ($5 = $6

    and subnum = $4)

  union

 select distinct lodnum,

 $1

   from inventory_view

  where dtlnum = $4

Explanation

• The first line is the pattern that needs to match exactly

o   Notice the number of spaces between distinct and lodnum

o   This needed to be specified as this because the XLATE version illustrated above shows in the same manner

o   Hint: Copy the text directly from the Trace file and place it here

• After the $$$$$ comes the second portion of the QueryHook where the SQL is parsed based on the Regular Expression provided

o   It starts with (?is) to specify to ignore case during the pattern matching and ignore new line

o   After looking for “select”, it says \s+ meaning there should be at least one white space but more is OK

o   After “distinct” it does the same to say \s+ until “lodnum”.  Now that we are using regular expressions, we don’t need to worry about all the spaces between “distinct” and “lodnum”

o   After that we have \s* till comma.  So, it can be any number of white space, including none

o   Then comes the first group (.*).  Note that any search criteria when enclosed in () becomes a group.  As mentioned before, this QueryHook did not need to worry about DECODE vs. CASE, so it is saying that after the “lodnum,” till “from”, any text is fine.  However, I want that text to be in my group ($1 in this case), as I want to use it in my Query Rewrite

o   After this, it looks for “From”, then “inventory_view”, then “where”

o   After that it starts to look for more Groups of data, as the XLATE was showing that these are possible bind variables.  We need to make sure that the query will match, regardless of how the parameters are passed to it.

o   The Search Pattern terminates with $

• After the next $$$$$ comes the new SQL

o   In this section, you are allowed to write any valid SQL statement

o   It may or may not have relevance to the original SQL (as illustrated in the next example)

o   However, in most cases you will be rewriting the query to yield similar result set, except in a faster manner

o   So, in this illustration the OR was turned into a three-way UNION

o   The first part of the UNION utilizes the first part of the OR, and so on for the next two portions

o   Important part is the utilization of the groups:

§  Note that $1 was all the text matched after lodnum, till the “from”.  So, in our rewrite we use it as is

§  The rest of the groups need to be utilized properly when the UNION is being expanded so the proper group is utilized where needed

§  Since the original statement only provides the value for the Load Number first in $4, it gets used throughout.  However, the same statement could have been written as:

·       Subnum = $7

·       Dtlnum = $8

After this QueryHook is introduced, the Trace Shows the Following:

Converting a statement to a Completely Different One:

Another possibility is that a problem statement due to performance, needs to be rewritten, but the functionality is not being used in your respective implementation.  So even though you could go through the exercise of a rewrite, it will still take precious seconds to execute, yet to produce no benefit.

So, in this case, simply returning a result set from DUAL yields a faster execution time.

Caution: This use should be utilized with extreme care, as turning the functionality on in the future will not work as we have changed the statement completely using the QueryHook

select distinct trnsp_mode.palletctl_flg

$$$$$

^select\s+distinct\s+trnsp_mode.palletctl_flg\s+from\s+pckwrk\s*,\s*shipment\s*,\s*stop\s*,\s*car_move\s*,\s*cardtl\s*,\s*trnsp_mode\s*,\s*wrkque\s+where\s+shipment.ship_id\s*=\s*pckwrk.ship_id\s+and\s+shipment.stop_id\s*=\s*stop.stop_id\s+and\s+stop.car_move_id\s*=\s*car_move.car_move_id\s+and\s+car_move.carcod\s*=\s*cardtl.carcod\s+and\s+\(wrkque.wrkref\s*=\s*pckwrk.wrkref\s+or\s+wrkque.list_id\s* =\s*pckwrk.list_id\)\s+and\s+wrkque.wh_id\s*=\s*pckwrk.wh_id\s+and\s+wrkque.wh_id\s*=\s*(.*?)\s+and\s+wrkque.reqnum\s*=\s*(.*?)\s*and\s*trnsp_mode.trnsp_mode\s*=\s*nvl\s*\(car_move.trans_mode\s*,\s*cardtl.cartyp\)$

$$$$$

select  0 as palletctl_flg from dual

Explanation

• The first line shows the predicate to match
• The line after the $$$$$ has the regular expression to match.  This is complex SQL so the regular expression is quite long.  However, with the knowledge of each search patter it can be understood
• After the next $$$$$ comes the replacement SQL.  Notice that this is simply being replaced with a select from DUAL.  We did make sure that the correct column is returned so the result set is not compromised for the downstream logic.  But how the 0 was returned did not matter and since Pallet Control logic is not being used, this rewrite yielded the best performance in this case.

Conclusion

The purpose of this blog entry was to illustrate to the reader the important and useful feature of MOCA and the available QueryHook.  Even though that caution must be exercised, this provides a valuable way to fine tune standard SQLs which JDA may not even be able to provide a fix for since the given SQL may only be working poorly in your specific case only.  This was the purpose of the two illustrations also, to emphasize that these SQLs would not be changed by JDA as they are optimized from their perspective.