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
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
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 test_date1.
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
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
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
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
07
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 because at-least
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
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 tried
]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent
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
recipients of list
ORACLE-L [EMAIL PROTECTED]
sfaroult@orio cc:
le.com Subject: Re: slow query help
Sent by:
[EMAIL PROTECTED]
om
18/12/2002
-
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...
It looks like that query was possibly generated by a tool. If not you
should go talk
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
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
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
17 matches
Mail list logo
