| date comparison [message #19894] |
Wed, 17 April 2002 02:01  |
priya
Messages: 108
Registered: February 2000
|
Senior Member |
|
|
Hi All,
I have a query like the following
select payname,sum(value)
from pay_result
where to_date(concat('01/',concat(paymonth,concat('/',payear))),'DD/MM/YYYY') between $first_date and $end_date
and p_id = '89898089'
group by payname
order by payname
The above query take a long time . Is there any other command which i can replace to reduce the time of retrieval of the data.
Thanks in advance
Priya
|
|
|
|
| Re: date comparison [message #19895 is a reply to message #19894] |
Wed, 17 April 2002 03:26  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
You don't provide enough information to answer. Are you using RBO or CBO? Is there an index on p_id?
Try SET AUTOTRACE ON in SQL*Plus and then run your query. Is there a full table scan? If so, that's your problem.
|
|
|
|
| Re: date comparison [message #19901 is a reply to message #19894] |
Wed, 17 April 2002 04:59 |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Do you have an index for p_id?
If not create one and analyze your table.
CREATE INDEX your_index_name ON pay_result(id);
ANAYLZE TABLE pay_result COMPUTE STATISTICS;
If this should not work do a "set autotrace on" in sqlplus and send the explain plan to the forum.
What you may also do, but i have no idear if it will reduce the query time is to replace your to_date by something simpler like:
to_date('month||'/'||year,'MM/YYYY'))
Hope that helps
Mike
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
