Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Severe performance problem with SQL

Re: Severe performance problem with SQL

From: Ed Prochak <prochak_at_my-deja.com>
Date: Fri, 06 Aug 1999 16:35:16 GMT
Message-ID: <7of2s4$e1b$1@nnrp1.deja.com>


Do you have an index on STOPTIME? run EXPLAIN PLAN on these two veriations. I suspect on the first it manages to use an index. On the other, it doesn't use that index because the direction of the conditional is reversed. Possibly adding a HINT to use the index DESCending might help.

BTW you say the requirement is two months but you actually use 60 days. (but to make it use months might block use of the index, so you are likely OK, except around February).

Without more details, it's really hard to say much more. The rest of your SELECT may have effects on this too. Good Luck!

In article <7od12s$nt$1_at_nnrp1.deja.com>,   raorg_at_my-deja.com wrote:
> Hi,
> I have a view based on an SQL which is selecting stuff from a five
> tables. A part of the where clause is that the stoptime must be within
> the last two months or null.
>
> The where clause includes the following statement:
> ... AND
> (table1.STOPTIME is null OR table1.STOPTIME < sysdate)...
>
> This takes about 20 secs to run.

Looking at this again, there's definite differences. This condition says fetch STOPTIMEs older than today or are unset(NULL). This returns all the rows of table1 (unless you have entries with stop times in the future). If it does return all rows, then the way the rows are fetched will be much different than the query using the condition below. (Why this one would be faster might have more to do with the other conditions and tables in your view.)

Again, check the execution plan to find out where these differ. That will help lead you to a better solution.

>
> If I change the above to:
> ... AND
> (table1.STOPTIME is null OR table1.STOPTIME > sysdate-60)...
>
> the statement takes 880 seconds to run.

>
> Can anyone help me find something equivalent to the above without the
> severe performance problem. Or modify the above...
>
> Help will be much appreciated.
> Thanks,
> Rajesh.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3702

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Aug 06 1999 - 11:35:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US