analytical function help

[lolu]

hI,

The following query is taking more than 20 secs .The table has over 3 million records.
Oracle is using index on cr_date.

Is there a way to tune this query using over partition by range or any date analytical function.

SELECT
COUNT(UNIQUE SID) SID_COUNT,TO_CHAR(CR_DATE,'MM/DD/YYYY') MY_DATE
FROM SID_SUM WHERE
CR_DATE BETWEEN TO_DATE('08/14/2008','MM/DD/YYYY') AND TO_DATE('08/15/2008','MM/DD/YYYY')) OR
CR_DATE BETWEEN TO_DATE('08/07/2008','MM/DD/YYYY') AND TO_DATE('08/08/2008','MM/DD/YYYY')
GROUP BY TO_CHAR(CR_DATE,'MM/DD/YYYY')

Explain plan for the above query

1.7 SELECT STATEMENT SID_SM Cost = 7

2.1 SORT GROUP BY

3.1 CONCATENATION

4.1 PARTITION RANGE SINGLE

5.1 INDEX RANGE SCAN UK_SID_SUM INDEX (UNIQUE)

4.2 PARTITION RANGE SINGLE

5.1 INDEX RANGE SCAN UK_SID_SUM INDEX (UNIQUE)

[LKBrwn_DBA]

It's the "OR" that causes the query to execute as "UNION", therefore you see the two index scans in the plan.


Maybe you could try this and it would help:

SELECT *
  FROM (SELECT   COUNT (DISTINCT SID) sid_count,
                 TO_CHAR (cr_date, 'MM/DD/YYYY') my_date
            FROM sid_sum
           WHERE cr_date BETWEEN TO_DATE ('08/07/2008', 'MM/DD/YYYY')
                             AND TO_DATE ('08/15/2008', 'MM/DD/YYYY')
        GROUP BY TO_CHAR (cr_date, 'MM/DD/YYYY'))
 WHERE my_date IN ('08/07/2008', '08/08/2008', '08/14/2008', '08/15/2008');