-
analytical function help
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)
-
OR this OR that?
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:
Code:
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');
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|