Set processing does not behave the same on every database platform. On some platforms, set processing can encounter performance breakdowns. Some platforms do not optimize update statements that include subqueries.
集處理在每個數據庫平臺上的行為并不相同。在某些平臺上，set處理可能會遇到性能故障。有些平臺不優化包含子查詢的更新語句。
For example, environments that are accustomed to updates with subqueries get all the qualifying department IDs from the Department table and then, using an index designed by an application developer, update the Personnel table. Other platforms read through every employee row in the Personnel table and query the Department table for each row.
例如，習慣于使用子查詢進行更新的環境從Department表中獲取所有符合條件的部門ID，然后使用應用程序開發人員設計的索引更新Personnel表。其他平臺讀取Personnel表中的每一個employee行，并查詢Department表中的每一行。
On platforms where these types of updates are a problem, try adding some selectivity to the outer query.
In the following example, examine the SQL in the Before section and then notice how it is modified in the After section to run smoothly on all platforms. You can use this approach to work around platforms that have difficulty with updates that include subqueries.
在這些類型的更新是一個問題的平臺上，請嘗試為外部查詢添加一些選擇性。在下面的示例中，檢查“之前”部分中的SQL，然后注意如何在“之后”部分修改它以在所有平臺上順利運行。您可以使用這種方法來解決那些在更新包含子查詢時遇到困難的平臺。
Note: In general, set processing capabilities vary by database platform. The performance characteristics of each database platform differ with more complex SQL and set processing constructs. Some database platforms allow additional set processing constructs that enable you to process even more data in a setbased manner. If performance needs improvement, you must tailor or tune the SQL for your environment. You should be familiar with the capabilities and limitations of your database platform and be able to recognize, through tracing and performance results, the types of modifications you need to incorporate with the basic set processing constructs described.
附注：一般來說，集合處理能力因數據庫平臺而異。每個數據庫平臺的性能特征因更復雜的SQL和集合處理構造而不同。一些數據庫平臺允許附加的集合處理構造，使您能夠以基于集合的方式處理更多的數據。如果需要改進性能，則必須為環境定制或調優SQL。您應該熟悉您的數據庫平臺的功能和限制，并且能夠通過跟蹤和性能結果來識別您需要與所描述的基本集合處理構造合并的修改類型。
Basic version:
基本版本：
UPDATE PS_REQ_LINE
SET SOURCE_STATUS = 'I'
WHERE
EXISTS
(SELECT 'X' FROM PS_PO_ITM_STG STG
WHERE
STG.PROCESS_INSTANCE =%BIND(PROCESS_INSTANCE)? AND
STG.PROCESS_INSTANCE =PS_REQ_LINE.PROCESS_INSTANCE AND
STG.STAGE_STATUS = 'I'? AND
STG.BUSINESS_UNIT = PS_REQ_LINE.BUSINESS_UNIT AND
STG.REQ_ID = PS_REQ_LINE.REQ_ID AND
STG.REQ_LINE_NBR = PS_REQ_LINE.LINE_NBR)
?????????????? ????? Optimized for platform compatibility:
針對平臺兼容性進行了優化：
UPDATE PS_REQ_LINE
SET SOURCE_STATUS = 'I'
WHERE
PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE) AND
?EXISTS
(SELECT 'X' FROM PS_PO_ITM_STG STG
WHERE
STG.PROCESS_INSTANCE =%BIND(PROCESS_INSTANCE)? AND
STG.PROCESS_INSTANCE =PS_REQ_LINE.PROCESS_INSTANCE AND
STG.STAGE_STATUS = 'I'? AND
STG.BUSINESS_UNIT = PS_REQ_LINE.BUSINESS_UNIT AND
STG.REQ_ID = PS_REQ_LINE.REQ_ID AND
STG.REQ_LINE_NBR = PS_REQ_LINE.LINE_NBR)
Note: This example assumes that the transaction table (PS_REQ_LINE) has a PROCESS_INSTANCE column to lock rows that are in process. This is another example of designing your database with batch performance and set processing in mind.
附注：本例假設事務表（PS_REQ_LINE）有一個PROCESS_INSTANCE列來鎖定正在處理的行。這是在設計數據庫時考慮到批處理性能和設置處理的另一個示例。
This modification enables the system to limit its scan through PS_REQ_LINE to only those rows that the program is currently processing. At the same time, it enables a more set-friendly environment to first scan the smaller staging table and then update the larger outer table.
這個修改使系統能夠通過PS_REQ_LINE將其掃描限制為僅掃描程序當前正在處理的那些行。同時，它支持一個設置更友好的環境，首先掃描較小的分段表，然后更新較大的外部表。
