6 PL/SQL Static SQL. Static SQL is a PL/SQL feature that allows SQL syntax directly in a PL/SQL statement. This chapter describes static SQL and explains how to use it. · Data Manipulation. To manipulate Oracle data you can include DML operations, such as INSERT, UPDATE, and DELETE statements, directly in PL/SQL. Free Oracle Magazine Subscriptions and Oracle White Papers: Oracle Insert Statements: Version 11.1: Basic Inserts: Single Column Table. You can find tutorials on SQL, SQL Server, Oracle, PL/SQL, Interview Questions, Joins, Multiple Choice Questions, Select, Insert, Update, Delete and other latest. · The Oracle PL/SQL language was designed to be a portable, high-performance transaction processing language that is tightly integrated with the SQL. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural extension for SQL and the Oracle relational database. PL/SQL is available in. Bulk Update Methods Compared. What I love about writing SQL Tuning articles is that I very rarely end up publishing the findings I set out to achieve. With this one, I set out to demonstrate the advantages of PARALLEL DML, didn't find what I thought I would, and ended up testing 8 different techniques to find out how they differed. And guess what? I still didn't get the results I expected. Hey, at least I learned something. As an ETL designer, I hate updates. They are just plain nasty. I spend an inordinate proportion of design time of an ETL system worrying about the relative proportion of rows inserted vs updated. I worry about how ETL tools apply updates (did you know Data. Stage applys updates singly, but batches inserts in arrays?), how I might cluster rows together that are subject to updates, and what I might do if I just get too many updates to handle. It would be fair to say I obsess about them. A little bit. The two most common forms of Bulk Updates are: Update (almost) every row in the table. This is common when applying data patches and adding new columns. Updating a small proportion of rows in a very large table. Case 1 is uninteresting. The fastest way to update every row in the table is to rebuild the table from scratch. All of these methods below will perform worse. Case 2 is common in Data Warehouses and overnight batch jobs. We have a table containing years worth of data, most of which is static; we are updating selected rows that were recently inserted and are still volatile. This case is the subject of our test. For the purposes of the test, we will assume that the target table of the update is arbitrarily large, and we want to avoid things like full- scans and index rebuilds. The methods covered include both PL/SQL and SQL approaches. I want to test on a level playing field and remove special factors that unfairly favour one method, so there are some rules: Accumulating data for the update can be arbitrarily complex. SQL updates can have joins with grouping and sub- queries and what- not; PL/SQL can have cursor loops with nested calls to other procedures. I'm not testing the relative merits of how to accumulate the data, so each test will use pre- preared update data residing in a Global Temporary Table. Some methods - such as MERGE - allow the data source to be joined to the update target using SQL. Other methods don't have this capability and must use Primary Key lookups on the update target. To make these methods comparable, the "joinable" techniques will use a Nested Loops join to most closely mimic the Primary Key lookup of the other methods. Even though a Hash join may be faster than Nested Loops for some distributions of data, that is not always the case and - once again - we're assuming an arbitraily large target table, so a full scan is not necessarily feasible. Having said that we're not comparing factors outside of the UPDATE itself, some of the methods do have differences unrelated to the UPDATE. I have included these deliberately because they are reasonably common and have different performance profiles; I wouldn't want anyone to think that because their statements were *similar* to those shown here that they have the same performance profile. The 8 methods I am benchmarking here are as follows (in rough order of complexity): Explicit Cursor Loop. Implicit Cursor Loop. UPDATE with nested SET subquery. BULK COLLECT / FORALL UPDATEUpdateable Join View. MERGEParallel DML MERGEParallel PL/SQLFor all of the tests, the following table structures will be used: TEST{n} (Update Source) - 1. K rows TEST (Update target) - 1. M rows. Name Type Name Type. PK NUMBER PK NUMBER. FK NUMBER FK NUMBER. FILL VARCHAR2(4. FILL VARCHAR2(4. The data has the following characteristics: TEST. PK will contain values 0 . TEST. PK is poorly clustered. It is generated by reversing the digits in LPAD(ROWNUM, '0', 7). PK values of 1,2, and 3 are adjacent in the primary key index but one- million rows apart in the table. TEST. FK will contain values 0 . For the first round of testing, the column FK will be indexed with a simple b- tree index. Not many people code this way, but there are some Pro*C programmers out there who are used to Explicit Cursor Loops (OPEN, FETCH and CLOSE commands) and translate these techniques directly to PL/SQL. The UPDATE portion of the code works in an identical fashion to the Implicit Cursor Loop, so this is not really a separate "UPDATE" method as such. The interesting thing about this method is that it performs a context- switch between PL/SQL and SQL for every FETCH; this is less efficient. I include it here because it allows us to compare the cost of context- switches to the cost of updates. DECLARE. CURSOR c. IS. SELECT *. FROM test. FETCH c. 1 INTO rec_cur. EXIT WHEN c. 1%notfound. UPDATE test. SET fk = rec_cur. WHERE pk = rec_cur. This is the simplest PL/SQL method and very common in hand- coded PL/SQL applications. Update- wise, it looks as though it should perform the same as the Explicit Cursor Loop. The difference is that the Implicit Cursor internally performs bulk fetches, which should be faster than the Explicit Cursor because of the reduced context switches. BEGIN. FOR rec_cur IN (. SELECT *. FROM test. UPDATE test. SET fk = rec_cur. WHERE pk = rec_cur. This method is pretty common. I generally recommend against it for high- volume updates because the SET sub- query is nested, meaning it is performed once for each row updated. To support this method, I needed to create an index on TEST8. PK. UPDATE test. SET (fk, fill) = (. SELECT test. 8. fk, test. FROM test. 8. WHERE pk = test. SELECT pk. FROM test. This one is gaining in popularity. Using BULK COLLECT and FORALL statements is the new de- facto standard for PL/SQL programmers concerned about performance because it reduces context switching overheads between the PL/SQL and SQL engines. The biggest drawback to this method is readability. Since Oracle does not yet provide support for record collections in FORALL, we need to use scalar collections, making for long declarations, INTO clauses, and SET clauses. DECLARE. CURSOR rec_cur IS. FROM test. 4. TYPE num_tab_t IS TABLE OF NUMBER(3. TYPE vc. 2_tab_t IS TABLE OF VARCHAR2(4. NUM_TAB_T. fk_tab NUM_TAB_T. VC2_TAB_T. OPEN rec_cur. FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab, fill_tab LIMIT 1. EXIT WHEN pk_tab. COUNT() = 0. FORALL i IN pk_tab. FIRST . pk_tab. LAST. UPDATE test. SET fk = fk_tab(i). WHERE pk = pk_tab(i). CLOSE rec_cur. /This is really a deprecated pre- 9i method; the modern equivalent is the MERGE statement. This needs a unique index on TEST1. PK in order to enforce key preservation. UPDATE (. SELECT /*+ ordered use_nl(old)*/ new. FROM test. 1 new. JOIN test old ON (old. SET fk = new_fk. , fill = new_fill. The modern equivalent of the Updateable Join View. Gaining in popularity due to its combination of brevity and performance, it is primarily used to INSERT and UPDATE in a single statement. We are using the update- only version here. Note that I have included a FIRST_ROWS hint to force an indexed nested loops plan. This is to keep the playing field level when comparing to the other methods, which also perform primary key lookups on the target table. A Hash join may or may not be faster, that's not the point - I could increase the size of the target TEST table to 5. M rows and Hash would be slower for sure. MERGE /*+ FIRST_ROWS*/ INTO test. USING test. 2 new ON (test. WHEN MATCHED THEN UPDATE SET. Here is the Explain Plan- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- . Id | Operation | Name | Rows | Bytes | Cost (%CPU)|. MERGE STATEMENT | | 1. K| 9. 92. 1K| 2. K (1)|. | 1 | MERGE | TEST | | | |. VIEW | | | | |. NESTED LOOPS | | 1. K| 1. 1M| 2. 58. K (1)|. | 4 | TABLE ACCESS FULL | TEST2 | 1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |