Oracle: 'DECODE' performance impact?

Post #150,352 by Meerkat 4/6/04 9:47:12 PM Reply	Oracle: 'DECODE' performance impact? I'll 'do the experiment' later today when I have a system to work on, but in the meantime I was wondering if anyone else has heard of DECODE having a big performance impact... A cow-orker is convinced that a query is returning results slowly because it uses a couple of DECODES. Nothing difficult, just a DECODE(FIELD,1,'this',2,'that','other') kind of thing. I won't know for sure til I do the experiment, my guess is that DECODE doesn't take much time at all. BUt I'm just curious to hear if anyone else has heard that use of Decode has serious performance implications. Will let you know the results of my experiment... John. Busy lad.
Post #150,358 by ben_tilly 4/6/04 10:14:19 PM Reply	Highly unlikely A couple of decodes is highly unlikely to be a problem. However decode does take extra CPU. If that is a bottleneck, then you might want to think about it. When a system is heavily loaded, then anything could be the straw that breaks the camel's back. However scanning through large decodes can get very slow. If your decodes get to be very long lists, then sometimes you can benefit from finding a different strategy. If you wish to educate your well-meaning co-worker about how to address performance problems with Oracle, I can highly recommend [link\|http://www.amazon.com/exec/obidos/tg/detail/-/0072230657/104-4462303-5667164?v=glance\|Effective Oracle by Design]. Tom Kyte does a lot to explain what a helpful mindset is, and also to point you at what likely problem areas really are. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,362 by Meerkat 4/6/04 10:28:33 PM Reply	Sounds like a great book We have Kyte's 'Export one-on-one' book lying around, sounds like this other one will soon be joining the collection. The other reason why I believe removing the Decode won't help all that much is because it'll just be shifted out of SQL and into program code. The decodes in question aren't long lists, just three options, affecting four fields. Will be able to do the experiment in about two hours from now (waiting for production stuff to finish to I can run tests. Yes, dev test & prod on the same box. No, not my decision :-) John. Busy lad.
Post #150,448 by kelzer 4/7/04 6:11:50 PM Reply	Re: Sounds like a great book Yeah, 'Export one-on-one' was a great follow up to 'Import one-on-one'. Both belong in the library of anyone involved in international trade. :)
Post #150,450 by Another Scott 4/7/04 6:16:56 PM Reply	Heh. We've got a comedian among us. :-) Cheers, Scott.
Post #150,483 by Meerkat 4/7/04 11:57:45 PM 4/7/04 11:59:02 PM Reply	My subconscious inadvertantly told you all what I spent most of time doing on Oracle that day. D'oh! And to make this (just slighlty) relevant - anyone else had mystery Ora-600 errors when trying to export tables created with 'create table compress...' ? (No, still haven't done the 'prove decode doesn't slow things down much' experiment. Real Work takes priority, for some silly reason) John. Busy lad. Edited by Meerkat April 7, 2004, 11:59:02 PM EDT Expand All History
Post #150,371 by ChrisR 4/6/04 10:52:32 PM Reply	Depends on what clause If the decode is in the WHERE clause, then the query plan can't use a straight up index. If it's in the SELECT clause, it shouldn't cause too much degradation. Then again, I'd use the standard CASE construct, but it should pretty well perform the same.
Post #150,384 by ben_tilly 4/7/04 1:14:51 AM Reply	Just to clarify that The presence of a DECODE statement in the where clause does not block indexes from working. But unless you have a function-based index, a DECODE on an indexed field will not be able to take advantage of an index on that field. You can sometimes get around this by adding a condition which is not as precise as the DECODE (such as an IN clause) which the index can work on in addition to the DECODE. If, that is, an index is good in your case. Sometimes not using an index is a better query plan. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,386 by Meerkat 4/7/04 2:02:20 AM Reply	It's just in the SELECT clause I've been doing my own little education campaign to try and stop folks using funcions in a WHERE :) John. Busy lad.
Post #150,411 by ben_tilly 4/7/04 10:57:43 AM Reply	Functions in the WHERE can be fine Depends on the context and how you do them. For instance if you want all records for a given day I might write it like this in Oracle: \n ... some_dm >= TRUNC(:date) AND some_dm < TRUNC(:date) + 1\n If there is an index on some_dm, it can now be used. And sure there is a function, but a smart optimizer should avoid having to recalculate it all of the time. I don't have proof that Oracle actually does it, but I've never had a query performance problem be tracked down to this either. And I don't have tools to test it at home. (However I'd be somewhat surprised if it both could correctly spot the optimization when it let it use indexes, which I've seen it do, and not just have logic to factor out the function in general.) Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,398 by CRConrad 4/7/04 9:50:49 AM Reply	Belated answer: "What they said". Yes, big long lists in lots of `DECODE`s in the same query are slow, no matter where they occur; and no, a short little `DECODE` in the main `SELECT` clause, not the `WHERE`, probably doesn't matter much at all. Just don't write crap like I did here a few weeks ago; printed-out, the 54 columns with `DECODE`s in my `SELECT` clause come to about two pages. (Depending on font size, of course; say, two pages of Courier New 11-pt.) That WILL be slow, even if my 54 `DECODE`s are all in the `SELECT` clause... I'll figure out a better way of transposing rows to columns, using code tables for maintainability, soon. (Where the fuck is Bryce, when for once you need him?!? ;-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #150,410 by ben_tilly 4/7/04 10:50:12 AM Reply	Transposing rows to columns is a PITA Tom Kyte has a solution in PL/SQL that he has presented in a couple of places. I didn't really like it. I generally handle it in a client-side language. I've found that to be fairly easy to do in Perl, and it lets the database focus on what it does best while moving the extra CPU load elsewhere. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,491 by CRConrad 4/8/04 1:52:00 AM Reply	"When the only tool you have is a hammer..." Ben writes: Tom Kyte has a solution in PL/SQL that he has presented in a couple of places. I didn't really like it. I'll check it out, anyway. (IIRC, this is the same guy you mentioned earlier in this thread [or a previous one]; so if he's good enough for you to mention twice [of which one occurrence without the disclaimer, IIRC], he's at least worth checking out for me.) I generally handle it in a client-side language. Yeah, I'd kind of like that, too but... [see below] I've found that to be fairly easy to do in Perl, Not the language I would choose, I suspect; I'd probably cobble together something in C, or whatever is available on the client's big Unix box. (Yeah, I know... But, hey, we bill per hour!) and it lets the database focus on what it does best while moving the extra CPU load elsewhere. Here's the hammer: Not, as you may have thought, that SQL (and Delphi! :-) is all I know, but that our system is implemented on that one single server(); I don't think we have access to any other. So, while I could "mov[e] the extra CPU load elsewhere", in terms of taking it out of the database... The only place I could take it, would be "elsewhere" on the same CPU. :-( (And I don't think that by talking of "client-side language" you're implying I should suck the whole table down onto my desktop PC here, right? :-) Apart from the feeble CPU, this old Win-box doesn't have all that much memory, either... Not to mention the network-bandwidth nightmare it would be!) (): Notwithstanding for the moment that "the server" is actually two separate boxes, for development and production, respectively. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #150,493 by ben_tilly 4/8/04 2:23:07 AM Reply	But that isn't the only tool that I have As you'll note from the fact that I pointed out that there is a PL/SQL solution, I'm aware of the existence of other tools. And you'll note that I used Perl because that was already in use in my situation. I could have done it in another language, but I'm not going to call out of Perl just to demonstrate how 1337 I am. In any case when I did it client-side, I sucked my result-set and then reformatted it to resend. Depending on your result set that might be an issue. For mine my "client" was on a webserver, and the result-set was enough to fit in a browser's display. Therefore the amount of data was not going to be enough to wipe me out on RAM. YMMV. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #150,495 by Meerkat 4/8/04 3:02:39 AM Reply	At least Perl is a good hammer to have I had a unix-hating coworker whose 'hammer' was Excel. This cow-orker still insisted they couldn't do a line count on large files because "It's more than 65000 rows, it won't load into Excel". Yep, wc -l was too difficult ("I don't do command-line"), so they loaded the file into Excel and went to the last row to get the rowcount... Sometimes, just sometimes, I feel real clever at work! John. Busy lad.
Post #150,498 by Steve Lowe 4/8/04 3:12:58 AM Reply	use that one a lot I use wc -l so much I've aliased it to just plain w Also global changes in text files..can change all occurences of a string that appears in each line of a 250K line file faster with perl or sed than the file can be loaded into a windows app. ----- Steve
Post #152,303 by broomberg 4/22/04 10:58:47 PM Reply	I don't like it Too slow for large files. And no feedback while it's working. Here's mine in Perl: \n#!/usr/local/bin/perl -w\n\nrequire 'mylib.pl'; #gets the cm - comma function\nuse strict;\n\nmy $count = 0;\nmy $MOD = 10_000;\n\nwhile (<>){\n\t$count++;\n\n\tprint STDERR "Working on [" . cm($count) . "]\\r" \n\t\tunless ($count % $MOD){\n}\n\nprint "\\n\\nCounted [" . cm($count) . "]\\n\\n";\n
Post #152,318 by Meerkat 4/23/04 2:23:05 AM Reply	No feedback? That's what top / glance is for :) John. Busy lad.
Post #150,497 by CRConrad 4/8/04 3:11:51 AM Reply	No no no, you misunderstand! The "hammer..." thing was not a reference to you and Perl; it was about me having just that one CPU to... uh, "hammer" with my processing. (No, it's not an AMD-64 CPU! :-) Perhaps a bit misplaced, in that the appropriate ending would have been, not "...then everything looks like a nail", but something like "...then you can just as well go on hammering [i.e, using it] the way you're used to". Or, IOW: I could probably increase the system's overall efficiency by moving that transposing load to some other "tool" (CPU) -- but, at least in my hands, that one "hammer" (server CPU) that I have, is probably most efficiently used for "hammering" (running SQL in the DB), rather than trying to "screw" with it... :-) That's all I meant. (As for the actual issue at hand, I'm doing this transposing in an SQL View, for the data to be loaded into the next table; this is just one step in our daily DW ETL processing. Millions and millions of rows, and the only "client"-type CPU I have unfettered access to is this NT4 desktop box with an "x86 Family 6 Model 8 Stepping 6, GenuineIntel" processor [what's that, a Pentium II? Or an early Pentium III?] at ~860 Mhz, and 512 MB RAM... That's just not on; especially as I'm using it for other stuff at the same time -- including important stuff, like talking bull on zIWT! :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #150,618 by tuberculosis 4/9/04 3:01:43 AM Reply	Eliminate decode wherever you can I tend to view the presence of decode an indicator that I'm missing a lookup table somewhere. Not always, but more often than not programmers use decode to turn codes into strings - the codes and strings ought to be in a table. Otherwise, traipsing through the code trying to figure out which code means what gets awfully tedious. Democracies are not well-run nor long-preserved with secrecy and lies. --Walter Cronkite

Welcome to IWETHEY!