Home » SQL & PL/SQL » SQL & PL/SQL » Query optimization (9i)
Query optimization [message #317278] Wed, 30 April 2008 06:17 Go to next message
Messages: 1
Registered: April 2008
Junior Member
I'm having some trouble with the query below, the execution during work time take between 10 and 30 seconds and when there's no load it takes around 6/7 secondes which is too much, I tried to optimize it but without success can anyone give me a tip

the query :
SELECT count(*) disNo,employee_no FROM osd.employee_actions
WHERE department_no = '1201102012'
AND	TO_CHAR(distrib_date_yy)||'/'||LPAD(TO_CHAR(distrib_date_mm),2,'0')||'/'||LPAD(TO_CHAR(distrib_date_dd),2,'0') 
BETWEEN '1429/04/01' AND '1429/04/30'
GROUP BY employee_no ORDER BY employee_no;

NB : the date field is split into 3 fields as you can see day month year for hijri date and can't do anything to fix that since it exist long before I start to work here

The table contain around 3 millions record, a select count(*) took around 15 second !


[Updated on: Wed, 30 April 2008 06:22] by Moderator

Report message to a moderator

Re: Query optimization [message #317281 is a reply to message #317278] Wed, 30 April 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 65084
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using real date will surely help in performances.
Check your indexes.

In addition,
SQL> select to_char(5,'fm00') v from dual;

1 row selected.

Re: Query optimization [message #317309 is a reply to message #317281] Wed, 30 April 2008 07:50 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
AND distrib_date_yy = 1429
AND distrib_date_mm = 4

An index on (department_no, distrib_date_yy, distrib_date_mm) also wouldn't hurt.

Ross Leishman
Previous Topic: cannot conver from BLOB TO RAW
Next Topic: Correct way of usingCursor
Goto Forum:

Current Time: Sat Jul 22 02:25:30 CDT 2017

Total time taken to generate the page: 0.07994 seconds