Printable View
Yeah, but we're not changing the plan and as long as you run it a few time, you should be fine. This is the situation where the differences would be most pronounced - inside a single SQL statement that either does or does not have to context switch a million times. In this case, staying within a single SQL statement is the best way to observe the differences.
- Chris
Chris, Jeff et all
In my initial test I followed the same logic as Jeff has mentioned - to avoid I/O operations (both physical and logical) as much as possible. Thet is why I've done trivial selects from DUAL inside the loop. But I've forgotten one thing that you have pointed out - context switches. Context switches caused by repeatedlly calling SQL select from inside PL/SQL block. BTW, your qoutes from the documentation imply that DECODE is a PL/SQL function while CASE is not (ie it is "internal" function), so that calling a DECODE causes an extra context switch compared to CASE, which is simply not true. They are both implemented the same way, as any other internal function.
In any case, I conducted my previous tests again at home, adding one additional test, where I used simple "SELECT NULL FROM DUAL;" inside the same loop to get the baseline results. It turned out that all the selects and context switches take roughly about 90% of time, while using either DECODE or CASE functions takes other 10% of the owerall time. The timing was performed in SQL*Plus using SET TIMING ON (1/100 secs precision) and was again allmost identical for both CASE and DECODE.
Then I performed another set of tests, according Chris's single-select proposal. In your example I didn't like DROP TABLE and CREATE TABLE AS SELECT stetements because both DROP and CREATE are very expensive operations for such tests and would probably distort the results. So what I realy did was create a simple one-column table and fill it with 500,000 rows that fitted in about 700 db blocks. Those blocks were all read into the cache so that there were no physical reads during the subsequent tests.
Then I performed three full table scans on that table: one with NULL in the column list, another one with CASE and the third one with DECODE.
- SELECT null FROM test_table;
- SELECT CASE ... FROM test_table;
- SELECT DECODE(...) FROM test_table;
I also noticed that mesuring time with SQL*Plus's TIME ON is rather unreliable, so I performed timing by using V$TIMER before and after every select. I performed several iteration of this tests and here are the average results in seconds elapsed (absolute numbers are not so important, I have rather slow PC):
- without any function: 19.22
- with CASE: 21.80
- with DECODE: 23.25
So we can see again that pure select uses roughly 80-85% of the total time, while the remaining 15-20% is spent on CASE/DECODE execution. (So the context switches in my first testing were not so very expensive, after all). We can also see that CASE is indeed quite a bit faster than DECODE, however absolutely unmesurable in a single execution. Performing 500.000 executions of either of them took 2.58 secs for CASE and 4.03 secs for DECODE.
So I would say performance is not realy a serious reason for switching from DECODE to CASE. But the elegance and mantainability of the code - well, this is certanly absolutely a different story....
You are absolutely correct. I had completely mis-read what they were saying in that part of the docs. My bad! :)Quote:
Originally posted by jmodic
BTW, your qoutes from the documentation imply that DECODE is a PL/SQL function while CASE is not (ie it is "internal" function), so that calling a DECODE causes an extra context switch compared to CASE, which is simply not true.
Good tests, good results and good conclusions!!
Kudos
- Chris
(Told ya it was faster ;););))