Query [message #472102] |
Wed, 18 August 2010 17:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
The below query is taking 30:00 minutes to get the result.
Please help me how to inmrove the performance of this query.
The table having 350000 records.
The query is retriving 2500 records.
SELECT COUNT(*) FROM emp_history WHERE REPORT_dt like '1990%';
Thanks in advance
|
|
|
|
|
Re: Query [message #472293 is a reply to message #472112] |
Thu, 19 August 2010 14:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
michael_bialik
Messages: 621 Registered: July 2006
|
Senior Member |
|
|
If REPORT_dt column defined as DATE and if you have an index defined on it and if 1990 selects small percentage of rows then using
implicitly converts DATE to VARCHAR ( TO_CHAR(REPORT_DT) LIKE '1990%' ) and disables possible index usage.
Try:
...REPORT_dt BETWEEN TO_DATE('19900101','YYYYMMDD') AND TO_DATE('19901231','YYYYMMDD')
HTH.
|
|
|
Re: Query [message #472314 is a reply to message #472293] |
Thu, 19 August 2010 16:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You forgot to factor in times:
REPORT_dt BETWEEN TO_DATE('19900101','YYYYMMDD') AND TO_DATE('19901231 235959','YYYYMMDD HH24MISS')
|
|
|