| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need URGENT HELP on PL/SQl
Steven,
I am sorry to have to say that, but your queries look like a case
study of all things which you musnt't do if you are keen on speed :
- Replace all your
in (select ....)
by correlated subqueries
exists (select ...)
It may be faster as he may not be faster (depends on how many rows are
returned by the subquery), but it's worth a try. For your information,
there is a paper on the subject on the Oriole Corp site under the 'Aunt
Augusta' section, it will explain you it at length.
- replace functions applied to columns, such as
to_char(startdate,'YYYYMMDD') <= '19990414' by (reverse) functions applied to constants, eg
startdate <= to_date('19990414', 'YYYYMMDD')
Unless you have Oracle 8.1 and created indexes on f(column), a
function applied to a column prevents Oracle from using the index.
- as a rule, try (when possible) to have positive rather than negative
conditions.
There are other things to do, but just try this and you should notice a serious improvement ...
--
Regards,
Stéphane Faroult
Oriole Corporation
 |
 |