Oracle generally advises using SQL Profiles/Patches/Baselines rather than introducing hints into application code. Using one of these forms of plan stability saves you from having to alter the code, and then having to test and verify that the change is functionally neutral and release it to production. It also saves repeating that whole process if you ever choose to remove or change the hint.
I generally use SQL profiles with PeopleSoft, because they are the only plan stability mechanism that can force match a statement. That is to say, a force-matching SQL profile will match other SQL statements that differ only in embedded literal values. PeopleSoft code makes extensive use of literal values. SQL patches and baselines only exactly match statements (so they match the SQL ID and not the force matching signature). Note that SQL Profiles require that the Tuning Pack is licenced, and that is only available on Enterprise Edition or Oracle.
I have written previously about using SQL Profiles to inject hints into dynamically generated code. If any part of the SQL (other than literal values) changes, then a different SQL profile is needed for each variation.
When generating SQL profiles for dynamically generated code, you have to be able to predict every possible variation in the code and generate a SQL profile for every combination of every variation. You end up writing code that mimics the dynamic code generation in the application
The same is true of Application Engine steps that reference temporary records via the %Table() meta-SQL but are otherwise static. This variation is a special case because it is a part of PeopleTools' behaviour. It is easy to determine which tables could be referenced by querying some of the PeopleTools tables. Although, the table name itself can be set dynamically!
Let's take an example statement. I identified it as performing poorly during an analysis of ASH data. I have a SQL_ID, a plan hash value, and ACTION indicates the name of the Application Engine step that generated it (because Application Engine instrumentation is enabled by setting EnableAEMonitoring - see https://blog.go-faster.co.uk/2015/03/undocumented-application-engine.html).
- SQL ID = bk98x60cspttj
- SQL Plan Hash Value = 113493817
- Action = XX_TREESEL.iDetVal.iDetVal.S
This is the statement and execution plan report generated by dbms_xplan.display_workload_repository (formatted and edited slightly). I have the statement, the execution plan and the outline of hints that describes the plan.
SELECT * FROM table(dbms_xplan.display_workload_repository('bk98x60cspttj',113493817,'ADVANCED +ADAPTIVE'));
SQL_ID
-------------
INSERT INTO PS_TSEL_P_TAO12 (CHARTFIELD, CHARTFIELD_VALUE,
PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO12 A, PS_GL_ACCOUNT_TBL DV
WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678
AND DV.SETID = '12345'
AND DV.EFFDT = (SELECT MAX(EFFDT)
FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT
AND EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD'))
AND DV.ACCOUNT >= A.RANGE_FROM_30
AND DV.ACCOUNT <= A.RANGE_TO_30
Plan hash value: 113493817
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 38754 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | PS_TSEL_P_TAO17 | | | | |
| 2 | SORT UNIQUE | | 1 | 161 | 38754 (1)| 00:00:02 |
|* 3 | FILTER | | | | | |
| 4 | MERGE JOIN | | 24219 | 3807K| 8 (50)| 00:00:01 |
| 5 | SORT JOIN | | 1138 | 151K| 3 (34)| 00:00:01 |
|* 6 | INDEX STORAGE FAST FULL SCAN | PSATSEL_R30_TAO17 | 1138 | 151K| 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | SORT JOIN | | 8513 | 207K| 3 (34)| 00:00:01 |
|* 9 | INDEX STORAGE FAST FULL SCAN| PS_GL_ACCOUNT_TBL | 8513 | 207K| 2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 25 | | |
| 11 | FIRST ROW | | 1 | 25 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN (MIN/MAX) | PS_GL_ACCOUNT_TBL | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / A@SEL$1
9 - SEL$1 / DV@SEL$1
10 - SEL$2
12 - SEL$2 / B@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
FULL(@"INS$1" "PS_TSEL_P_TAO17"@"INS$1")
INDEX_FFS(@"SEL$1" "A"@"SEL$1" ("PS_TSEL_R30_TAO17"."PROCESS_INSTANCE"
"PS_TSEL_R30_TAO17"."CHARTFIELD" "PS_TSEL_R30_TAO17"."RANGE_FROM_30"
"PS_TSEL_R30_TAO17"."RANGE_TO_30"))
INDEX_FFS(@"SEL$1" "DV"@"SEL$1" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT"
"PS_GL_ACCOUNT_TBL"."EFFDT"))
LEADING(@"SEL$1" "A"@"SEL$1" "DV"@"SEL$1")
USE_MERGE(@"SEL$1" "DV"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
INDEX(@"SEL$2" "B"@"SEL$2" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT"
"PS_GL_ACCOUNT_TBL"."EFFDT"))
END_OUTLINE_DATA
*/
…Below is the underlying Application Engine step that generated the SQL. There is other dynamic code in this step that is driven by configuration data as well as the %Table() meta-SQLs. Other reasons not to introduce hints into the statement include:
- different dynamic variations of the code might require different hints, and then I would have to write more code to generate the hint dynamically. However, in this case, I am only going to deal with a single variation, and I am only going to produce one set of SQL Profiles,
- this step and the code generation are delivered by PeopleSoft, any change in these areas would be considered as a customisation.
%InsertSelect(DISTINCT
, %Bind(FT_TSEL_AET.RECNAME_SEL_TBL, NoQuotes)
, %Bind(FT_TSEL_STR_AET.DTL_RECNAME, NoQuotes
) DV
, %Bind(FT_TSEL_AET.FIELDNAME_CF, NoQuotes) = %Bind(FT_TSEL_STR_AET.DTL_FIELDNAME)
, %Bind(FT_TSEL_AET.FIELDNAME_VALUE, NoQuotes) = DV.%Bind(FT_TSEL_STR_AET.DTL_FIELDNAME, NoQuotes)
, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
, TREE_NAME = %Bind(FT_TSEL_AET.TREE_NAME)
, TREE_NODE = A.TREE_NODE
, TREE_NODE_NUM = A.TREE_NODE_NUM
, TREE_LEVEL_NUM = A.TREE_LEVEL_NUM, SETCNTRLVALUE =%Bind(FT_TSEL_AET.SETCNTRLVALUE)
, %Bind(FT_TSEL_GEN_AET.FIELDNAME_FROM, NoQuotes) = A.%Bind(FT_TSEL_GEN_AET.FIELDNAME_FROM, NoQuotes)
, %Bind(FT_TSEL_GEN_AET.FIELDNAME_TO, NoQuotes) = A.%Bind(FT_TSEL_GEN_AET.FIELDNAME_TO, NoQuotes)
, SETID_TREE = %Bind(FT_TSEL_GEN_AET.SETID_TREE)
, EFFDT = %Bind(FT_TSEL_STR_AET.EFFDT)
, CFV_SET = %Bind(FT_TSEL_AET.CFV_SET))
FROM %Table(%Bind(FT_TSEL_GEN_AET.RECNAME_SEL_TBL, NoQuotes)) A
, %Table(%Bind(FT_TSEL_STR_AET.DTL_RECNAME, NoQuotes)) DV
WHERE A.CHARTFIELD = %Bind(FT_TSEL_AET.FIELDNAME)
AND A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
%Bind(FT_TSEL_WRK_AET.WHERE_TXT_LONG, NoQuotes)This step references two temporary records. It generates data in one temporary table TSEL_P_TAO based on data in another, TSEL_R30_TAO. It happens to use instance 12 of both records. This is really just a coincidence. Temporary table instances are allocated and deallocated in a group when an application engine starts and ends, so it is common for one step to use the same instance of different tables, but there is nothing to stop different table instances from being used. That can occur when concurrently executing multiple instances of a program or different programs that allocate just some of the same tables.
I now need to produce SQL profiles for each permutation. I will start by generating a script to create a single profile for a single SQL statement using Oracle's coe_xfr_sql_profile.sql script that is delivered as a part of the SQLT utility. It is available from the Oracle support website (All About the SQLT Diagnostic Tool (Doc ID 215187.1)).
It produces a SQL script such as the one below (I have removed the comments). The SQL text is in a CLOB variable, and all the hints required to reproduce the same execution plan are in an array. The script generates a signature for the statement and then creates a SQL profile from that information.
I have already customised my version of coe_xfr_sql_profile.sql so that FORCE_MATCH => TRUE in the generated scripts.
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$683B0107")]',
q'[OUTLINE_LEAF(@"SEL$C772B8D1")]',
q'[UNNEST(@"SEL$2")]',
q'[OUTLINE_LEAF(@"INS$1")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$7511BFD2")]',
q'[OUTLINE(@"SEL$1")]',
q'[FULL(@"INS$1" "PS_TSEL_P_TAO17"@"INS$1")]',
q'[NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")]',
q'[INDEX(@"SEL$C772B8D1" "DV"@"SEL$1" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT" "PS_GL_ACCOUNT_TBL"."EFFDT"))]',
q'[FULL(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "DV"@"SEL$1" "A"@"SEL$1")]',
q'[USE_NL(@"SEL$C772B8D1" "DV"@"SEL$1")]',
q'[USE_MERGE(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[PARTIAL_JOIN(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[INDEX_FFS(@"SEL$683B0107" "B"@"SEL$2" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT" "PS_GL_ACCOUNT_TBL"."EFFDT"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'coe_g7wz6ctquwjcy_2476903986',
description => 'coe g7wz6ctquwjcy 2476903986 '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_g7wz6ctquwjcy_2476903986 completedIn Application Engine, different tables will be used if multiple programs referencing the same table run concurrently, or if a process crashes and holds its allocation to a particular table instance. Therefore, I need to create a set of similar SQL profiles, one for each statement that could be generated by this Application Designer step. I need to iterate through all possibilities, so I have added some additional pieces to the script. They are explained in the footnotes below.
REM coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal.sql
SPO coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal.log;
WHENEVER SQLERROR CONTINUE
SET serveroutput on ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
clear screen
ALTER SESSION SET CURRENT_SCHEMA=SYSADM;1
…
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
l_recname1 VARCHAR2(15) := 'TSEL_R30_TAO';2
l_table1 VARCHAR2(18) := 'PS_TSEL_R30_TAO17';
l_recname2 VARCHAR2(15) := 'TSEL_P_TAO';
l_table2 VARCHAR2(18) := 'PS_TSEL_P_TAO17';
l_name VARCHAR2(30);
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
e_no_sql_profile EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
BEGIN
FOR i IN (3
WITH v4 AS (SELECT rownum-1 row_number FROM dual CONNECT BY LEVEL <= 100)
SELECT DISTINCT
v1.row_number id1, r1.recname recname1, t1.table_name table_name1
, v2.row_number id2, r2.recname recname2, t2.table_name table_name2
, o.TEMPTBLINSTANCES
FROM psrecdefn r1
, psrecdefn r2
, pstemptblcntvw5 i1
, pstemptblcntvw i2
, all_tables t1
, all_tables t2
, psoptions o
, ps.psdbowner p
, v v1
, v v2
WHERE r1.rectype = 7 AND r1.recname = i1.recname AND r1.recname = l_recname1
AND r2.rectype = 7 AND r2.recname = i2.recname AND r2.recname = l_recname2
AND v1.row_number <= i1.temptblinstances + o.temptblinstances6 --up to total number of instances
AND v2.row_number <= i2.temptblinstances + o.temptblinstances --up to total number of instances
AND (v1.row_number = 0 OR v1.row_number > o.temptblinstances)7 --omit online temp tables
AND (v2.row_number = 0 OR v2.row_number > o.temptblinstances) --omit online temp tables
and t1.owner = p.ownerid AND t1.table_name
= DECODE(r1.sqltablename,' ','PS_'||r1.recname,r1.sqltablename)
||DECODE(v1.row_number,0,'',LTRIM(TO_NUMBER(v1.row_number)))8 --derive table table
and t2.owner = p.ownerid AND t2.table_name
= DECODE(r2.sqltablename,' ','PS_'||r2.recname,r2.sqltablename)
||DECODE(v2.row_number,0,'',LTRIM(TO_NUMBER(v2.row_number))) --derive table table
AND ABS(v1.row_number-v2.row_number)<=19 --max variation in instance numbers
ORDER BY id1, id2
) LOOP
-----------123456789012345678901234567890
l_name := 'XX_TREESEL.iDetVal.iDetVal'||i.id1||i.id2;10
sql_txt := q'[
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[ALL_ROWS]',
q'[LEADING(@"SEL$1" "A"@"SEL$1")]',11
q'[END_OUTLINE_DATA]');
sql_txt := REPLACE(sql_txt, l_table1,i.table_name1);12
sql_txt := REPLACE(sql_txt, l_table2,i.table_name2);
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
dbms_output.put_line(l_name||':'||:signature||':'||sql_txt);
for n in 1 .. h.count LOOP13
if h(n) LIKE '%'||l_table1||'%' THEN
h(n) := REPLACE(h(n), l_table1,i.table_name1);
dbms_output.put_line(n||':'||h(n));
end if;
if h(n) LIKE '%'||l_table2||'%' THEN
h(n) := REPLACE(h(n), l_table2,i.table_name2);
dbms_output.put_line(n||':'||h(n));
end if;
end loop;
BEGIN --drop profile if already exists
DBMS_SQLTUNE.drop_SQL_PROFILE(name => l_name);
EXCEPTION WHEN e_no_sql_profile THEN NULL;
END;
DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( --create new profile
sql_text => sql_txt,
profile => h,
name => l_name,
description => 'coe XX_TREESEL.iDetVal.iDetVal '||l_name||' '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END LOOP;
END;
/
WHENEVER SQLERROR CONTINUE
…
SPO OFF;
PRO
PRO coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal completed- Set CURRENT_SCHEMA to specify PeopleSoft owning schema, SYSADM.
- For each temporary record in the original SQL statement, add pairs of variables to specify the name of PeopleSoft record and Oracle temporary table instance referenced in the statement. I could derive the record name from the table name, but it is easier just to hard-code it.
- The SQL statement produces all combinations of temporary records that could appear in the SQL statement. I will put it in an implicit cursor loop, and then for each row returned, the script will create a SQL profile.
- Common table expression V returns 100 rows, numbered 0 to 99. Irrespective of the number of temporary table instances specified in each Application Engine program, there can only be non-shared 99 table instances for each PeopleTools record, plus the shared instance (that doesn't have a suffix number).
- PSTEMPTBLCNTVW returns the number of non-shared batch (i.e. not online) instances of each temporary record that needs to be built. This is in addition to the number of online temporary table instances.
- The query will return a row for each instance of each temporary table up to the number of instances required by the application engines plus the number of online table instances, but not exceeding the 99 rows returned by CTE V.
- Most Application Engines do not run online in the component processor, therefore there is no need to build SQL profiles on these instances. There are exceptions, such as some as the journal and voucher edit and post processes in Financials, in which case these criteria should be removed.
- This expression joins the record and instance number to the table in the database. Instance 0 will be used to refer to the shared instance.
- The instance numbers of the temporary records is permitted to vary by up to one in either direction. Thus the script generates profiles with instance 12 of one table, and instances 11 to 13 of the other. It is rare, but possible, for there to be any difference in instance numbers between tables. It is possible, but rarer for the difference to be greater than one.
- SQL Profile names are limited to 30 characters. I have specified a meaningful name based on the Application Engine step, up to 26 characters, and then the two IDs (which can be up to 2 digits each).
- In this example, I am not going to use the full set of hints in the captured profile. I just want to introduce a single leading hint.
- I will substitute each table name in the SQL text with the specific table instance name.
- Tables are generally referenced in hints via the row source alias. However, sometimes the table name appears in the hints, and must also be replaced with the specific table name. So I also work through all the hints in array h and substitute any table names that may be there. Indexes are not referenced by their names but by a list of indexed columns.
XX_TREESEL.iDetVal.iDetVal00:11943215885789639839:
INSERT INTO PS_TSEL_P_TAO (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
XX_TREESEL.iDetVal.iDetVal1717:15747497907378648788:
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
XX_TREESEL.iDetVal.iDetVal1718:12015611546030583918:
INSERT INTO PS_TSEL_P_TAO18 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
XX_TREESEL.iDetVal.iDetVal1817:14883898515022367531:
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO18 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
…
Note- If the number of non-shared instances of a table in an Application Engine is increased, you need to build add table instances with Application Designer.
- If the number of online temporary instances is increased, you need to build additional instances for every temporary record in the database. If the number is reduced some tables will cease to be used, and they ought to be dropped.
- If either the number of application engine table instances or online temporary table instances are changed, then you will need to rerun the script to create additional SQL profiles.
@go-faster.co.uk on BlueSky
Posts
