Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    15

    Unanswered: How to optimize my query

    I have not been able to run query bellow

    select sum(1)AS TOTAL_CLICKS,
    click.url AS URL,
    cj.rating AS CJR,
    cntr.website AS WEBSITE,
    sum(commission_adv_curr) AS COMMISSION_ADV_CURR,
    sum(commission_pub_curr)AS COMMISSION_PUB_CURR,
    sum(commission_usd )AS COMMISSION_USD,
    ee.fname|| ' '|| ee.lname as ASSIGNED_OWNER
    from click_ref click,
    cntr_day_p cntr,
    crating c,
    website,
    company_employee ce,
    employee ee
    where ( click.url like '%myspace%'
    or click.url like '%facebook%')
    and cntr.date_ >= '05-aug-2008'
    and cntr.date_ < '06-sep-2008'
    and cntr.website = click.website
    and cntr.website = website.id
    and website.company = cj.company
    and ce.employee = ee.id(+)
    and website.company = ce.company(+)
    group by
    click.url,
    cntr.website,
    c.rating,
    ee.fname|| ' '|| ee.lname as ASSIGNED_OWNER

    Does anybody has any suggestions how to optimize this query?

    Thanks,

    Katya

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT   SUM(1) AS Total_Clicks,
             Click.url AS url,
             cj.Rating AS cjr,
             cntr.WebSite AS WebSite,
             SUM(Commission_Adv_Curr) AS Commission_Adv_Curr,
             SUM(Commission_Pub_Curr) AS Commission_Pub_Curr,
             SUM(Commission_Usd) AS Commission_Usd,
             ee.fName
             ||' '
             ||ee.lName AS Assigned_Owner
    FROM     Click_ref Click,
             cntr_Day_p cntr,
             cRating c,
             WebSite,
             Company_Employee ce,
             Employee ee
    WHERE    (Click.url LIKE '%myspace%'
               OR Click.url LIKE '%facebook%')
             AND cntr.Date_ >= '05-aug-2008'
             AND cntr.Date_ < '06-sep-2008'
             AND cntr.WebSite = Click.WebSite
             AND cntr.WebSite = WebSite.Id
             AND WebSite.Company = cj.Company
             AND ce.Employee = ee.Id (+) 
             AND WebSite.Company = ce.Company (+) 
    GROUP BY Click.url,
             cntr.WebSite,
             c.Rating,
             ee.fName
             ||' '
             ||ee.lName
    Why are you comparing Dates to strings?
    Do all fields in the WHERE clause have indexes?
    Are statistics current on all objects?

    Post EXPLIAN_PLAN for this query.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    15
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------
    Plan hash value: 818230225

    ----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 43G| 7329G| | 3622M (1)|5936:51:39| | |
    | 1 | SORT GROUP BY | | 43G| 7329G| 15T| 3622M (1)|5936:51:39| | |
    |* 2 | HASH JOIN | | 43G| 7329G| 2179M| 25M (2)| 41:53:28 | | |
    |* 3 | HASH JOIN RIGHT OUTER | | 29M| 1844M| | 157K (4)| 00:15:28 | | |
    | 4 | TABLE ACCESS FULL | EMPLOYEE | 1287 | 23166 | | 43 (0)| 00:00:01 | |
    |* 5 | HASH JOIN | | 29M| 1341M| 30M| 156K (3)| 00:15:23 | | |
    | 6 | TABLE ACCESS FULL | CJRATING | 1684K| 11M| | 2872 (3)| 00:00:17 | |
    |* 7 | HASH JOIN RIGHT OUTER | | 1836K| 71M| 5620K| 146K (3)| 00:14:26 | |
    | 8 | TABLE ACCESS FULL | COMPANY_EMPLOYEE | 287K| 2247K| | 539 (3)| 00:00:0
    |* 9 | HASH JOIN | | 907K| 28M| 30M| 143K (3)| 00:14:08 | | |
    | 10 | PARTITION RANGE ITERATOR| | 907K| 19M| | 113K (4)| 00:11:10 | 338 |
    | 11 | TABLE ACCESS FULL | CNTR_DAY_P | 907K| 19M| | 113K (4)| 00:11:10 | 338 |
    | 12 | TABLE ACCESS FULL | WEBSITE | 2857K| 27M| | 25059 (2)| 00:02:28 | |
    | 13 | REMOTE | | 209M| 22G| | 23M (2)| 39:10:21 | | |
    ----------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    2 - access("CNTR"."WEBSITE"="CLICK"."WEBSITE")
    3 - access("CE"."EMPLOYEE"="EE"."ID"(+))
    5 - access("WEBSITE"."COMPANY"="C"."COMPANY")
    7 - access("WEBSITE"."COMPANY"="CE"."COMPANY"(+))
    9 - access("CNTR"."WEBSITE"="WEBSITE"."ID")

    There are indexes on website.company, on ctr.website, website.id, cjrating.company, company_employee.employee, employee.employee_id.
    Bellow is the modified sql to compare date with date:

    SELECT SUM(1) AS Total_Clicks,
    Click.url AS url,
    c.Rating AS rating,
    cntr.WebSite AS WebSite,
    SUM(Commission_Adv_Curr) AS Commission_Adv_Curr,
    SUM(Commission_Pub_Curr) AS Commission_Pub_Curr,
    SUM(Commission_Usd) AS Commission_Usd,
    ee.fName
    ||' '
    ||ee.lName AS Assigned_Owner
    FROM Click_ref Click,
    cntr_Day_p cntr,
    Cjrating c,
    WebSite,
    Company_Employee ce,
    Employee ee
    WHERE (Click.url LIKE '%myspace%'
    OR Click.url LIKE '%facebook%')
    AND cntr.Date_ >= to_date('05-aug-2008', 'dd-mon-yyyy')
    AND cntr.Date_ < to_date('06-sep-2008', 'dd-mon-yyyy')
    AND cntr.WebSite = Click.WebSite
    AND cntr.WebSite = WebSite.Id
    AND WebSite.Company = c.Company
    AND ce.Employee = ee.Id (+)
    AND WebSite.Company = ce.Company (+)
    GROUP BY Click.url,
    cntr.WebSite,
    rating,
    ee.fName
    ||' '
    ||ee.lName

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Providing your cntr_day table holds data for a long time period (compared to a month which your querying for) an index on cntr_day.Date_ would probably help.

    Also an index on Click.url would probably help assuming most of the websites are not facebook or myspace.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    15
    Alan, thank you for your help. However, I cannot modify production tables. Is there is any way to modify my query instead?

    Thanks,

    Katya

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Can you change the criteria on Click.url to be on cntr.website instead?

    This should help a bit but you really need an index on cntr.Date_ aswell.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •