Validating null value in pl sql Sexyvideo chat site

Rated 4.32/5 based on 658 customer reviews

When I run the following query twice, it gives different results.

SQLThat is sort of what I said -- it doesn't factor it out in these cases, you can HELP it to do that. COM exec dbms_output.put_line( state_); 1 PL/SQL procedure successfully completed. COM exec dbms_output.put_line( state_); 17138 PL/SQL procedure successfully completed. COM exec dbms_output.put_line( state_); 2 PL/SQL procedure successfully completed. ) where the inner query is simply the original query with the functions removed and rownum added (so no view merging takes place) select ((to_number(b.

I think most of us, at least on the OTN forums, regard you as the ultimate authority. COM select count(*) 2 from t 3 where tmptype = 'N' 4 and decode( replace(translate(tmpvalue,' -1234567890.','0000000000000'),'0',''), 5 NULL, non_deterministic(tmpvalue), 0 ) = 0 6 / COUNT(*) ---------- 17130 [email protected] Now, the cool thing is -- looking forward to 9i, the numbers on the same exact box were: ******************************************************************************** select count(*) from t where tmptype = 'N' and decode( replace(translate(tmpvalue,' -1234567890.','0000000000000'),'0',''), NULL, non_deterministic(tmpvalue), 0 ) = 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.08 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.71 0.72 0 96 3 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.71 0.80 0 96 3 1 ******************************************************************************** select count(*) from t where tmptype = 'N' and decode( replace(translate(tmpvalue,' -1234567890.','0000000000000'),'0',''), NULL, is_deterministic(tmpvalue), 0 ) = 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.73 0.73 0 96 3 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.74 0.73 0 96 3 1 ******************************************************************************** select count(*) from t where tmptype = 'N' and non_deterministic(tmpvalue) = 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.04 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.03 1.04 0 96 3 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.05 1.08 0 96 3 1 ******************************************************************************** select count(*) from t where tmptype = 'N' and is_deterministic(tmpvalue) = 0 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.01 1.00 0 96 3 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 1.02 1.01 0 96 3 1 so, there is still a marked improvement by short circuting the call to PLSQL but it is not nearly as much AND in fact, the query that doesn't short circut in 9i actually runs faster then the 8i query showing the speed up in 9i related to calling PLSQL from SQL (it is much reduced in 9i) Deterministic is mostly useful with function based indexes....

Also, I know you said that the combination of decode and replace and translate with an occasional function call is faster than multiple function calls, but will you please provide some more detail as to why or better yet some proof? COM create or replace function is_deterministic( p_num in varchar2 ) return number 2 DETERMINISTIC 3 as 4 x number; num_val_error exception; pragma exception_init( num_val_error, -6502 ); 5 begin 6 my_:= my_1; 7 x := to_number( p_num ); 8 return 1; 9 exception 10 when num_val_error then return 0; 11 end; 12 / Function created. Now, we'll test the decode vs no decode and deterministic vs non deterministic [email protected] Tom, It occurred to me that which method is preferred might depend on the scenario and how you want to handle it.

For example, if C is found in an age column that should be numeric, I don't plan to try to figure out what they meant to input when they typed C.

validating null value in pl sql-2

validating null value in pl sql-38

validating null value in pl sql-58

validating null value in pl sql-61

Tom, You say, "deterministic doesn't even come into play here, it is not relevant. My Oracle Advanced PL/SQL Student Guide page 2-28 (kit produced 2/23/01 BOM Updated 7/20/00) says, "The hint DETERMINISTIC helps the optimizer avoid redundant function calls." Further up on the page, it says, "Means the SQL may choose to use a cached copy of the return result rather than actually invoking the function." So,if there are many of the same value, wouldn't the function potentially only be invoked once per value, using cached results for repeat values, thereby running faster? COM exec dbms_output.put_line( my_); 34260 PL/SQL procedure successfully completed.My only goal is to identify which rows do not contain valid numbers, so I can flag them.Tom, Thank you very much for providing the explanation and proof in the form of a detailed example including TKPROF. Anytime this subject comes up, I can now just post a response using your method with decode, replace, translate, (including and - just in case) and your function (without deterministic) and provide a link to this for the details.Thanks, Barbara Tom, In your example, the function is referenced only once for each row, it makes sense the optimizer doesn't do anything to it, just like you said, "there are no redudant calls to be removed".Also the function modifies the value of cnt in my_state package, it probably should be called every time it's referenced.

Leave a Reply