analytical function help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: analytical function help

  1. #1
    Join Date
    Apr 2009
    Posts
    1

    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)

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool 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
  •  



Click Here to Expand Forum to Full Width