Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    29

    Unanswered: Query Fine Tuning

    Hi,
    Can anyone help me fine tune the below qry. Even though the query is not that much complicated and all still takes a full 3 minutes to give the results. Here goes the qry...........
    SELECT
    JCDW_GEOGRAPHY_D.BUSINESS_UNIT,
    JCDW_CONTRACT_D.CUSTOMER_NAME,
    JCDW_CONTRACT_D.CUSTOMER_NUMBER,
    JCDW_CONTRACT_D.CUSTOMER_BILL_TO_CITY,
    NVL(
    SUM(JCDW_CONTRACT_F.SALES_AMT),0),
    JCDW_TIME_D.YEAR,
    JCDW_TIME_D.QTR_YEAR_DESCRIPTION,
    '(' || ( DECODE(TO_CHAR(JCDW_TIME_D.MONTH_NUMBER_IN_YR),
    '1', '01',
    '2', '02',
    '3', '03',
    '4', '04',
    '5', '05',
    '6', '06',
    '7', '07',
    '8', '08',
    '9', '09',
    '10', '10',
    '11', '11',
    '12', '12', 'N/A') ) ||')'|| DECODE(JCDW_TIME_D.MONTH_NAME,
    'JANUARY', 'JAN',
    'FEBRUARY', 'FEB',
    'MARCH', 'MAR',
    'APRIL', 'APR',
    'MAY', 'MAY',
    'JUNE', 'JUN',
    'JULY', 'JUL',
    'AUGUST', 'AUG',
    'SEPTEMBER', 'SEP',
    'OCTOBER', 'OCT',
    'NOVEMBER', 'NOV',
    'DECEMBER', 'DEC', 'N/A'),
    JCDW_TIME_D.WEEK_NUMBER_IN_YEAR,
    JCDW_GEOGRAPHY_D.CONTINENT,
    JCDW_GEOGRAPHY_D.REGION,
    JCDW_GEOGRAPHY_D.DISTRICT,
    JCDW_GEOGRAPHY_D.DEPOT_DESCRIPTION,
    JCDW_CONTRACT_D.CONTRACT_NUMBER,
    JCDW_GEOGRAPHY_D.DEPOT_DESCRIPTION
    FROM

    JCDW_GEOGRAPHY_D,
    JCDW_CONTRACT_D, -- 377798
    JCDW_CONTRACT_F, -- 575791
    JCDW_TIME_D --1826
    WHERE
    ( JCDW_CONTRACT_F.GEO_KEY=JCDW_GEOGRAPHY_D.GEO_KEY )
    AND ( JCDW_CONTRACT_F.TIME_KEY=JCDW_TIME_D.TIME_KEY )
    AND ( JCDW_CONTRACT_F.CONTRACT_KEY=JCDW_CONTRACT_D.CONTR ACT_KEY )
    AND (
    JCDW_GEOGRAPHY_D.CONTINENT IN ('DOMESTIC OPERATIONS', 'GEER HEADQUARTERS')
    )
    GROUP BY
    JCDW_GEOGRAPHY_D.BUSINESS_UNIT,
    JCDW_CONTRACT_D.CUSTOMER_NAME,
    JCDW_CONTRACT_D.CUSTOMER_NUMBER,
    JCDW_CONTRACT_D.CUSTOMER_BILL_TO_CITY,
    JCDW_TIME_D.YEAR,
    JCDW_TIME_D.QTR_YEAR_DESCRIPTION,
    '(' || ( DECODE(TO_CHAR(JCDW_TIME_D.MONTH_NUMBER_IN_YR),
    '1', '01',
    '2', '02',
    '3', '03',
    '4', '04',
    '5', '05',
    '6', '06',
    '7', '07',
    '8', '08',
    '9', '09',
    '10', '10',
    '11', '11',
    '12', '12', 'N/A') ) ||')'|| DECODE(JCDW_TIME_D.MONTH_NAME,
    'JANUARY', 'JAN',
    'FEBRUARY', 'FEB',
    'MARCH', 'MAR',
    'APRIL', 'APR',
    'MAY', 'MAY',
    'JUNE', 'JUN',
    'JULY', 'JUL',
    'AUGUST', 'AUG',
    'SEPTEMBER', 'SEP',
    'OCTOBER', 'OCT',
    'NOVEMBER', 'NOV',
    'DECEMBER', 'DEC', 'N/A'),
    JCDW_TIME_D.WEEK_NUMBER_IN_YEAR,
    JCDW_GEOGRAPHY_D.CONTINENT,
    JCDW_GEOGRAPHY_D.REGION,
    JCDW_GEOGRAPHY_D.DISTRICT,
    JCDW_GEOGRAPHY_D.DEPOT_DESCRIPTION,
    JCDW_CONTRACT_D.CONTRACT_NUMBER,
    JCDW_GEOGRAPHY_D.DEPOT_DESCRIPTION
    HAVING
    (
    NVL(
    SUM(JCDW_CONTRACT_F.SALES_AMT),0) IS NOT NULL
    AND NVL(
    SUM(JCDW_CONTRACT_F.SALES_AMT),0) != 0
    )
    . Some one please help me fast. Thanks in advance
    Regards
    Dinesh

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Query Fine Tuning

    Impossible to say without more information:

    1) Have all tables been analyzed?

    2) What indexes exist on each table?

    3) How many rows in JCDW_GEOGRAPHY_D?

    4) How many of those have CONTINENT IN ('DOMESTIC OPERATIONS', 'GEER HEADQUARTERS')?

    By the way, this won't improve performance much (if at all), but one of your HAVING conditions is redundant:

    NVL(SUM(JCDW_CONTRACT_F.SALES_AMT),0) IS NOT NULL

    Of course it's not null, you've NVL'd it! Just need:

    HAVING NVL(SUM(JCDW_CONTRACT_F.SALES_AMT),0) != 0

  3. #3
    Join Date
    May 2002
    Posts
    29
    Andrew,
    Thanks for ur reply. The things is all the tables are analysed and we do have the necessary indexes in place. The JCDW_GEOGRAPHY_D has got 203 records of that 102 is are there in the Continent field matches. I have removed the redundancy in the having field but still the query is takin the same time lenght. Please see if u can help me out anymore on the same.
    Thanks & Regards
    Dinesh

Posting Permissions

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