Your first thought may be, "Cool! Someone is sharing little tricks that he's learned just to be a nice guy." You can think that, but it's really more like, "This geek can't remember anything past quitting time and can't find it without using Alta Vista."
Whatever.
This page will be dynamic. As I stumble across something or remember it I'll add it on. All tricks are based on Oracle SQL and SQL*Plus. They've last been tested on Oracle 7.2, but don't hold that against them.
Recently I've started accumulating messages that I find interesting from the oracle-l@teleport.com mailing list.
(And you might want to take a look at another John Marlen's Oracle Tips or Oracle Magazine's Tip of the Week.)a
Some more great in your face tips on using Oracle can be found at Philip Greenspun's Page .
This is an oddball where a friend sent mail asking how to do a limited pivot. I think it's best explained by the mail itself:
>
> Does anyone have a query which might transpose columns into rows? In the
> example below ADV_TYPE_CD always has 3 values (API, BSW, TEMP) for a MV_ID.
> Redesigning the table isn't an option. I have come across such problems
> before and remember employing a complex union.
>
> However, I don't recall the exact details.
>
> Thanks.
>
> Here is an example:
>
> Table: ADV
>
> ADV_ID ADV_TYPE_CD VOL MV_ID .........
> ------- ----------- ----- -------
> 5 API 100 1000
> 6 BSW 200 1000
> 7 TEMP 90 1000
> 11 API 101 2000
> 12 BSW 202 2000
> 13 TEMP 91 2000
>
> Desired Output:
>
> MV_ID API BSW TEMP
> 1000 100 200 90
> 2000 101 202 91
> . . . .
> . . . .
> . . . .
Unless I'm missing something....:
create
table adv_test (adv_id number,
adv_type_cd varchar(4),
vol number,
mv_id number);
insert into adv_test values ('5','API','100','1000');
insert into adv_test values ('6','BSW','200','1000');
insert into adv_test values ('7','TEMP','90','1000');
insert into adv_test values ('11','API','101','2000');
insert into adv_test values ('12','BSW','202','2000');
insert into adv_test values ('13','TEMP','91','2000');
rem Desired Output:
rem MV_ID API BSW TEMP
rem 1000 100 200 90
rem 2000 101 202 91
select mv_id,
max(decode(adv_type_cd,'API',vol,null)) API,
max(decode(adv_type_cd,'BSW',vol,null)) BSW,
max(decode(adv_type_cd,'TEMP',vol,null)) TEMP
from adv_test
group by mv_id;
MV_ID API BSW TEMP
---------- ---------- ---------- ----------
1000 100 200 90
2000 101 202 91
I dunno if the max is the best function to use, but it does the
trick if there's only going to be one value.
If there are going to be an arbitrary number of ADV_TYPE_CD the
problem gets a lot more interesting.
If you've filtered with an IN (..,..) you've noticed some heavy performance hits. If you look at the explain plan, you'll find that it is producing table scans for each of the elements in the IN list. There's a logically equivalent method using a DECODE() function that reduces it to a single scan. If you have a filter that looks like:
where state_code in ('TX','OK','LA','NM');
You can replace it with:
where decode(state_code,'TX',1,'OK',1,'LA',1,'NM',1,0) = 1;and significantly reduce your table scans. (Note the 0 as the default in the decode results.)
The code is ugly. And the longer the list, the uglier the code. But then, the longer the list the more you need the performance.
But because it's so ugly, I won't use it unless I need to. Never with a couple of values on a table with just a few rows -- the IN is so much more readable. But a with handful of values in a list with thousands of rows to evaluate, I'll use it in a heartbeat.
You should be figuring it out by now that I kind of like the DECODE() function. In this case, I'm bemoaning the fact that there is no GREATER() function in Oracle's SQL. Sure, I like GREATEST() which is quite handy, and the "<" operator is irreplacable.
But there are just times when neither of them fit. Say when you want to compute a date based discount. Certainly you can do that with union such as:
select price, travel_date from quote where travel_date < target_date union select price*0.8, travel_date from quote where travel_date => target_date;
Which is fine in simple selects. However in complex ones with performance issues, it's can be better to get it done with functions. In many function sets, there is a GREATER(x,y) that returns a 1 if x=>y and a 0 otherwise. This can be used in computations, replacing the above SQL with:
select price*(1-0.2*greater(travel_date,target_date),
travel_date
from quote;
To recreate the funcionality, one can use a DECODE() with a GREATEST().
GREATER(x,y) = DECODE(GREATEST(x,y),x,1,0)
Our first example turns into:
select
price*(1-0.2*decode(greatest(travel_date,target_date),target_date,1,0)),
travel_date
from quote;
This can be used for the functions LESS() and BETWEEN() also.
LESS(x,y) = DECODE(LEAST(x,y),x,1,0)
BETWEEN(x,y1,y2) = DECODE(LEAST(x,y1),y1,1,0)*DECODE(GREATEST(x,y2),y2,1,0)
Yes, it's ugly. Yes, it's unreadable. And, yes, it should probably be done by creating a funcion. But if you need the performance and don't have the privileges to create functions, you have an option.
Hmmn. I bet I can use this to do an interpolation....
Running out of space in a rollback segment while maintaining a big table is very frustrating. Take a look at COPY in the SQL*Plus User's Guide and Reference. Especially look at what it says about the COPYCOMMIT variable.
I did all sorts of strange combinations of functions in earlier versions of Oracle to manipulate dates. I got to throw a lot of them away when they gave TRUNC() a date format. Just RTFM.
This one is fun. I'm often creating SQL that creates SQL and then runs it. There are various reasons for doing this, carrying it somewhere on a floppy, recreating data structures, getting around some volume bottle neck that the relational model implies, etc.
This particular trick is where you want to accomplish some other function every so many lines. Usually it's a commit, or a sleep, or something where I'm trying not to overflow my rollback segment, or maybe allowing the [cough] users to have some of the machine time occasionally while I'm doing some maintenance.
In this example, the insert that I'm doing is going to kick a trigger that sends messages down a pipe that causes all sorts of denormalized expansions to occur. It's enough work that it irritates the users. Thus I'm going to kick in 25 or so rows and let it settle for 600 seconds. I'm going to use the HEADING to do the COMMIT and SLEEP.
column insert_line heading 'commit; |host sleep 600| '
set underline off
set linesize 160
set pagesize 25
set feedback off
spool ps2pn.sql
select 'insert into provider_networks (network_id,prov_id,'||
'prov_net_eff_date,prov_net_term_date) values (''PN'','''||
prov_id||''','''||prov_net_eff_date||''','||
decode(prov_net_term_date,null,'null',''''||
prov_net_term_date||'''')||');'
insert_line
from provider_networks n
where nvl(n.prov_net_term_date,sysdate)>=sysdate
and not exists (select 'x'
from provider_networks m
where n.prov_id=m.prov_id
and m.network_id='PN'
and nvl(m.prov_net_term_date,sysdate)>=sysdate)
and n.network_id='PS';
spool off
@ps2pn
As usual, the created code is a concatenation of column data and constants placed in a single output column. I use the COLUMN ... HEADING ... formating feature of SQL*Plus to create code that occurs every pagesize or so lines.
Last updated December 9, 1998