Join SQL query optimization

Vote count:

0

I've been trying to improve performance of my query which is executing quite a long time. I've already checked explain plan and it looks ok (two nested loops, usage of index) Can you spot any improvement possibilities? Maybe some of the functions I'm using aren't too fast? Or maybe its just a problem of large data set and there is not much I can do? Thanks for any advice!

SELECT COUNT(*),
  TRUNC(a_tab.some_date) ,
  TO_CHAR(MIN(a_tab.some_date),'dd/MM/YYYY HH24:mm:ss') ,
  TO_CHAR(MAX(a_tab.some_date),'dd/MM/YYYY HH24:mm:ss')
FROM TABLE_A a_tab
JOIN TABLE_B b_tab
ON a_tab.id              = b_tab.a_tab_id
where b_tab.keyword_name = 'NAME_X'
AND b_tab.keyword_value  = 'VALUE_X'
AND a_tab.some_date BETWEEN to_date('01/05/2014','dd/MM/YYYY') AND to_date('01/06/2014','dd/MM/YYYY')
AND extract (hour FROM a_tab.some_date) BETWEEN 0 AND 13
GROUP BY TRUNC(a_tab.some_date)
ORDER BY TRUNC(a_tab.some_date);

asked 36 secs ago

xwhyz

305