=> Second execution of DECODE : 6.39 seconds => First execution of DECODE : 7.05 secondsģ WHERE 'ALL' = DECODE(preg_underwrite_company,'ALL','ALL' ,'ALL')Ĥ AND 'ALL' = DECODE(preg_product_code,'ALL','ALL' ,'ALL')ĥ AND 'ALL' = DECODE(preg_state_code,'ALL','ALL' ,'ALL')Ħ AND 'ALL' = DECODE(preg_bill_type,'ALL','ALL' ,'ALL')ħ AND 'ALL' = DECODE(preg_broker,'ALL','ALL' ,'ALL')Ĩ AND 'ALL' = DECODE(DECODE(preg_inception_date, NULL, 'NEW', 'REN') ,'ALL','ALL' ,'ALL')ĩ AND 'ALL' = DECODE(preg_payment_plan,'ALL','ALL' ,'ALL') => Second execution of CASE : 0.82 seconds => First execution of CASE : 7.92 secondsģ WHERE 'ALL' = CASE preg_underwrite_company WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDĤ AND 'ALL' = CASE preg_product_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDĥ AND 'ALL' = CASE preg_state_code WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDĦ AND 'ALL' = CASE preg_bill_type WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDħ AND 'ALL' = CASE preg_broker WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDĨ AND 'ALL' = CASE DECODE(preg_inception_date, NULL, 'NEW', 'REN') WHEN 'ALL' THEN 'ALL' ELSE 'ALL' ENDĩ AND 'ALL' = CASE preg_payment_plan WHEN 'ALL' THEN 'ALL' ELSE 'ALL' END With the Partitioning, OLAP, Data Mining and Real Application Testing options Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production Is there a way to generate it through SQL prompt? I guess the trace table presented by you in your first follow-up in this thread is generated by tkprof. ![]() What i am not able to understand is : while autotrace ON, the statistics are almost similar in both - CASE as well as DECODE, still difference in performance.May be this behavior of poor performance by DECODE is due to 10g which is different than that of 11g (short circuit)? I have tested it on a table of 2 million rows on version 10.2.0.4.0 ![]() When id=6 then 'c' when id=7 then 'd' when id=8 then 'e' when id=9 then 'f' when id=0 then 'g' When id=1 then 'x' when id=2 then 'y' when id=3 then 'z' when id=4 then 'a' when id=5 then 'b' When 7 then 'd' when 8 then 'e' when 9 then 'f' when 0 then 'g' end from t Select case id when 1 then 'x' when 2 then 'y' when 3 then 'z' when 4 then 'a' when 5 then 'b' when 6 then 'c' Ops$tkyte%ORA11GR2> create table t as select mod(rownum,10) id, a.* from all_objects a Hmmm, how do you think I would answer this.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |