You would have thought to, but it
doesn't seem to be the case.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___March 19th
A small follow up question:
Why will oracle do a to_date() on second query for each row returned, as the
date mentioned is a literal and doesn't change for the duration of the query.
Won't it be calculated once and used for all the rows with trunc() applied to
each row?
Regards
Naveen
-Origi
>
>Dear all,
>
>Please find below the 2 queries :
>
>select dep_date from test_date1
>where to_char(dep_date,'dd-mon-') =
>'12-jan-2002'
>
>select dep_date from test_date1
>where TRUNC(dep_date) = to_DATE(
>'12-jan-2002','dd-mon-')
>
>The execution plan for both the queries shows a FTS
>on
Which version of Oracle ?
The second query has to do a trunc() and a to_date()
on every single row. The first only has to do a to_char
on every single row. There is a CPU difference between
the two queries and if you run the execution plan through
v9 with cpu_costing, you will find a difference
two
functions versus one maybe
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: woensdag 5 maart 2003
7:34To: Multiple recipients of list ORACLE-LSubject:
Slow queryDear
all, Please find below the 2
queries : select dep_date from
test_date1 w
Raj,
I thought it would only do the union all if it was able to use an index and
all the instr's look as though they'd stop that. Even then I thought it was
generally just rule that would do that unless you used the use_concat hint.
I can't see why a full table scan of each wouldn't be fairly
Title: RE: slow query help
Joan,
Here is a suggestion ...
if this is going to be your most used part, I'd look into Intermedia ... you'll have a lot more options to work with and they will work good.
Until then, I'd recommend replacing instr() with appropriate LIKE clause b
ry possibility - that
> should save Oracle some heartache.
>
> Regards,
> Mark.
>
>
> Stephane
> Faroult To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
>
Joan,
Can't this be done as a series of ors instead of the union alls as this
would presumably reduce it to one full table scan of each table.
e.g.
SELECT T11.TRUNK TRUNK_FOUND
,T21.IDTARGET_ID
,T21.SSN TARGET_SSN
,T21.FULLNAME
> algorithm was worse though .
>
> Raj
>
> -Original Message-
> From: Mark Richard [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, December 17, 2002 4:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: slow query help
>
> I tend to agree with Stephane...
>
d to trying every possibility - that
> should save Oracle some heartache.
>
> Regards,
> Mark.
>
>
> Stephane
> Faroult To: Multiple recipients of list
>ORACLE-L <[EMAIL PROTECTED]>
>
Title: RE: slow query help
It need not be generatd by a tool, I have worked for a blood bank in one of my previous projects. When it comes to matching a donor in th records, you have to take a lot of precautions to see if you have a duplicate donor etc.
To me this seems to be logic to find
Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
Subject: Re: slow query help
Sent by:
Joan Hsieh wrote:
>
> Hi,
>
> This is the query bothered us very much recently. It run at least 15
> min. and sometimes crashed the temp tablespace. Do you have any idea how
> to make it run better. Our developer tried used two cursors to compare
> the result, but the result is not optimized. We
On Tue, 29 May 2001,Oweson Flynn scribbled on the wall in glitter crayon:
->(Roland, that is a bit like asking 'How do I implement world peace?' or 'How
->do I understand women?' - we can't help make a query run faster per se - a
you do not understand women, if you are very, very, very lucky you
Chase it with a big stick
Nah just kidding
Provide some more detail like version and what you are trying to actually
do and you are going to get a better answer.
As I always understood it, to get the best performance all other things
being correct with the system is that you have to make your
Oh No, Oh No - Be nice now ... (I can see all the 'undocumented' parameters
being hauled out again ...)
(Roland, that is a bit like asking 'How do I implement world peace?' or 'How
do I understand women?' - we can't help make a query run faster per se - a
little detail would be nice - like 'what
17 matches
Mail list logo
