Updating multiple tables in pl sql
WAprrs = 'start,stop' FROM Table1 T1, Table2 T2 WHERE T1= T2and T1= '011008'; COMMIT; @unknown: Based on your comment, you would need to join across both Table1 and Table2 when you update Table2 if your update query needs the keys from a third table. XXXXXX' FROM Table1 T1, Table2 T2 WHERE T1= T2and T1= '011008'; UPDATE Table2 SET Table2.There were workarounds available to overcome this limitation, but they often performed less well.Oracle 9i Release Two introduces BULK COLLECT support for collections of records.In December 2001, I wrote a paper entitled Introduction to Bulk PL/SQL Processing in 8i.This demonstrated the (then new) bulk PL/SQL processing enhancements introduced in Oracle 8i.Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables, but it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.If the amount of data to be processed or utilized from your PL/SQL procedure is too large to fit comfortably in a PL/SQL table, use a GLOBAL TEMPORARY table rather than a normal table.
Now, PL/SQL uses an optimizer that can rearrange code for better performance. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter .In the query that follows, we want to identify all users who exist within Oracle who have not been granted a role.We could formulate the query as an anti-join with a noncorrelated subquery (against a complex view) as shown here: This query runs in 18 seconds.Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).
To allow subprogram inlining, either accept the default value of the pragma (with the preceding syntax) to give a subprogram a high priority for inlining, and then the compiler inlines it unless other considerations or limits make the inlining undesirable.
A well-documented limitation of BULK COLLECT in 8i was that each column fetched from a SQL statement or cursor required its own index-by table or collection.