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

    Question Unanswered: Need help speeding up query

    Hi all, I have the following view created:

    CREATE OR REPLACE FORCE VIEW QSOL_OWNER.MAINTENANCE_OPTIONS_VU
    (PLANT_CD, SHO_NBR, OPT_ASSEMBLY_NBR, START_DT, STOP_DT,
    FIRST_BUILD, LAST_BUILD, BUILD_COUNT, FILM_CARD)
    AS
    SELECT PI_OEPL_OPTIONS_SNAP.plant_cd,
    PI_OEPL_OPTIONS_SNAP.sho_nbr,
    PI_OEPL_OPTIONS_SNAP.opt_assembly_nbr,
    PI_OEPL_OPTIONS_SNAP.start_dt,
    PI_OEPL_OPTIONS_SNAP.stop_dt,
    MIN(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt) first_build,
    MAX(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt) last_build,
    COUNT(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt build_count,
    MAINTENANCE_OPTIONS_SNAP.film_card
    FROM MAINTENANCE_OPTIONS_SNAP,
    PI_OEPL_OPTIONS_SNAP
    WHERE MAINTENANCE_OPTIONS_SNAP.plant_cd = PI_OEPL_OPTIONS_SNAP.plant_cd
    AND MAINTENANCE_OPTIONS_SNAP.sho_nbr = PI_OEPL_OPTIONS_SNAP.sho_nbr
    AND MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt BETWEEN PI_OEPL_OPTIONS_SNAP.start_dt AND PI_OEPL_OPTIONS_SNAP.stop_dt-1
    GROUP BY PI_OEPL_OPTIONS_SNAP.plant_cd,
    PI_OEPL_OPTIONS_SNAP.sho_nbr,
    PI_OEPL_OPTIONS_SNAP.opt_assembly_nbr,
    PI_OEPL_OPTIONS_SNAP.start_dt,
    PI_OEPL_OPTIONS_SNAP.stop_dt,
    MAINTENANCE_OPTIONS_SNAP.film_card;

    The MAINTENANCE_OPTIONS_SNAP has 1.5 million records
    The PI_OEPL_OPTIONS_SNAP has 14 million records

    I have an index created on the MAINTENANCE_OPTIONS_SNAP consisting of plant_cd, sho_nbr, eng_build_upfit_dt, and film_card

    I have an index created on the PI_OEPL_OPTIONS_SNAP consisting of plant_cd, sho_nbr, opt_assembly_nbr, start_dt, and stop_dt

    I need to loop through every record in the view by doing a 'select *' but it takes 3-4 hours to run. Is there anything else I can do to speed this up?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Get Dan Tow's book "SQL Tuining" & follow his rigorous methodology.

    Post an EXPLAIN_PLAN for this query from Hades.
    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
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Does Oracle know that there are 15 million rows in one of the tables? i.e. have the tables been analyzed?

  4. #4
    Join Date
    Jul 2003
    Posts
    15
    Yes, they have both been analyzed.

  5. #5
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    This sql probably won't work without being modified but try using analytics:

    SELECT PI_OEPL_OPTIONS_SNAP.plant_cd,
    PI_OEPL_OPTIONS_SNAP.sho_nbr,
    PI_OEPL_OPTIONS_SNAP.opt_assembly_nbr,
    PI_OEPL_OPTIONS_SNAP.start_dt,
    PI_OEPL_OPTIONS_SNAP.stop_dt,
    MIN(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt) OVER (partition by MAINTENANCE_OPTIONS_SNAP.plant_cd order by MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt ) first_build,
    MAX(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt) OVER (partition by MAINTENANCE_OPTIONS_SNAP.plant_cd order by MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt )last_build,
    COUNT(MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt) OVER (partition by MAINTENANCE_OPTIONS_SNAP.plant_cd order by MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt ) build_count,
    MAINTENANCE_OPTIONS_SNAP.film_card
    FROM MAINTENANCE_OPTIONS_SNAP,
    PI_OEPL_OPTIONS_SNAP
    WHERE MAINTENANCE_OPTIONS_SNAP.plant_cd = PI_OEPL_OPTIONS_SNAP.plant_cd
    AND MAINTENANCE_OPTIONS_SNAP.sho_nbr = PI_OEPL_OPTIONS_SNAP.sho_nbr
    GROUP BY PI_OEPL_OPTIONS_SNAP.plant_cd,
    PI_OEPL_OPTIONS_SNAP.sho_nbr,
    PI_OEPL_OPTIONS_SNAP.opt_assembly_nbr,
    PI_OEPL_OPTIONS_SNAP.start_dt,
    PI_OEPL_OPTIONS_SNAP.stop_dt,
    MAINTENANCE_OPTIONS_SNAP.film_card
    having MAINTENANCE_OPTIONS_SNAP.eng_build_upfit_dt BETWEEN PI_OEPL_OPTIONS_SNAP.start_dt AND PI_OEPL_OPTIONS_SNAP.stop_dt-1 ;


    Also, the original post is missing a ) after the COUNT, so did you not post the entire sql?
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

Posting Permissions

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