One of my favorite PL/SQL techniques used in batch job development is to add an extra parameter, default it to NULL, so that I can test a single account at a time.
example
CREATE PROCEDURE bill_employers(in_employer_id DEFAULT NULL)
IS
BEGIN
FOR employer in
SELECT emp.employer_id
, emp.account_number
, emp.bill_amt
FROM employer emp
WHERE emp.employer_id = NVL(in_employer_id,emp.employer_id)
LOOP
--Do stuff here
END LOOP;
END;
So the real trick is the NVL in the where clause – If I supply a value for in_employer_id that’s the only one that’ll get processed.
exec bill_employers(in_employer_id => 12345);
If I don’t pass a value at all, we’ll process the whole set because of the NVL – if in_employer_id is NULL, then the rvalue becomes the same as the lvalue.
exec bill_employers;
5 Comments
having looked at nothing but NVLs lately, even when not used in any way, shape or form, I prefer explicitly stating it like this:
WHERE ( emp.employer_id = in_employer_id
OR in_employer_id IS NULL )
I’m glad you are blogging about your tips and tricks. These are great ideas and help me improve my own code. Keep it up!
I wonder if the optimizer is smart enough to see the WHERE clause as including all rows, and eliminate it? Have you looked at the execution plan that results from this approach (versus using an IF statement to test for null, then submitting two different SELECTS)?
@Bob_Watkins thoughtful question. I built an unscientific test case and it looks like the answer to your question is a no. I created two procedures, one that selects all rows, the other using the NVL() technique outlined above. This is an unscientific test against a non-idle system so I take the disk and elapsed numbers with a huge grain of salt. Interesting that LIO was the same across both queries.
SELECT PRTY_ID , DBA_NAME , CREATED_DT FROM PARTIES A WHERE PRTY_ID = NVL(:B1 ,PRTY_ID) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 9711 5.70 6.53 12396 22172 0 971084 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9713 5.70 6.53 12396 22172 0 971084 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 49 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 971084 CONCATENATION (cr=22172 pr=12396 pw=0 time=3899891 us) 971084 FILTER (cr=22172 pr=12396 pw=0 time=3899589 us) 971084 TABLE ACCESS FULL PARTIES (cr=22172 pr=12396 pw=0 time=2928149 us) 0 FILTER (cr=0 pr=0 pw=0 time=5 us) 0 TABLE ACCESS BY INDEX ROWID PARTIES (cr=0 pr=0 pw=0 time=0 us) 0 INDEX UNIQUE SCAN PARTIES_PK (cr=0 pr=0 pw=0 time=0 us)(object id 125098) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 879 0.08 0.70 db file sequential read 16 0.00 0.00 ******************************************************************************** SELECT PRTY_ID , DBA_NAME , CREATED_DT FROM PARTIES A call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 9711 4.69 4.81 12366 22171 0 971084 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 9713 4.69 4.81 12366 22171 0 971084 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: 49 (recursive depth: 1) Rows Row Source Operation ------- --------------------------------------------------- 971084 TABLE ACCESS FULL PARTIES (cr=22171 pr=12366 pw=0 time=2914439 us) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file scattered read 885 0.00 0.17 db file sequential read 15 0.00 0.00 ********************************************************************************@chet this is interesting – i tried the method you suggested and wound up with more LIO and PIO. I didn’t expect that-I did a double take and then an ALTER SYSTEM FLUSH SHARED_POOL and ran it again. Same result. Here are results for where a value is supplied for in_party_id:
SELECT PRTY_ID , DBA_NAME , CREATED_DT
FROM
PARTIES A WHERE PRTY_ID = NVL(:B1 ,PRTY_ID)
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.04 0 0 0 0
Fetch 1 0.01 0.00 0 4 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 3 0.02 0.04 0 4 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49 (recursive depth: 1)
Rows Row Source Operation
——- —————————————————
1 CONCATENATION (cr=4 pr=0 pw=0 time=84 us)
0 FILTER (cr=0 pr=0 pw=0 time=6 us)
0 TABLE ACCESS FULL PARTIES (cr=0 pr=0 pw=0 time=0 us)
1 FILTER (cr=4 pr=0 pw=0 time=70 us)
1 TABLE ACCESS BY INDEX ROWID PARTIES (cr=4 pr=0 pw=0 time=68 us)
1 INDEX UNIQUE SCAN PARTIES_PK (cr=3 pr=0 pw=0 time=39 us)(object id 125098)
********************************************************************************
SELECT PRTY_ID , DBA_NAME , CREATED_DT
FROM
PARTIES A WHERE (PRTY_ID = :B1 OR :B1 IS NULL)
call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.23 1.22 12582 12596 0 1
——- —— ——– ———- ———- ———- ———- ———-
total 3 1.23 1.23 12582 12596 0 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 49 (recursive depth: 1)
Rows Row Source Operation
——- —————————————————
1 TABLE ACCESS FULL PARTIES (cr=12596 pr=12582 pw=0 time=1227765 us)
Post a Comment