Results 1 to 9 of 9

Thread: Tough query

  1. #1
    Join Date
    May 2004
    Location
    San Diego, CA
    Posts
    7

    Angry Unanswered: Tough query

    Need a little help with tuning of my query. The following select statement going to compare two tables for missing records. One of the table local and the other remote. There is any hints exist for remote databases?

    SELECT
    COUNT(A.CUSTID)
    FROM
    DBW_PROMOC A
    WHERE NOT EXISTS
    (SELECT 'X' FROM PROMOC@RPT B
    WHERE A.CUSTID = B.CUSTID AND A.PROMO = B.PROMO AND A.EDATE = B.EDATE);

    NOTE: This query takes over an hour to run, even all three fields are Primary keys in both tables.

    A little bit more info about my query: local database 9.0.1 and CBO, but remote is 7.3.4 and RBO. Both table have over 23 millions records.

    Thanks in advance,
    Alex

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Select A.custid, A.promo, A.edate
    From Dbw_promoc A
    Minus
    Select B.custid, B.promo, B.edate
    From Dbw_promoc@rpt B
    -- Lists All Records In A And Not In B
    Union
    Select B.custid, B.promo, B.edate
    From Dbw_promoc@rpt B
    Minus
    Select A.custid, A.promo, A.edate
    From Dbw_promoc A
    -- Lists All Records In B And Not In A
    /
    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
    May 2004
    Location
    San Diego, CA
    Posts
    7

    Angry

    This method works better, but not enough for my needs (too slow). Anybody have any idea with PL/SQL???

    Thanks,
    Alex

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Ready, Fire, Aim

    >Anybody have any idea with PL/SQL???

    PL/SQL will NEVER be faster than well formed/tuned SQL;
    because when all is said and done SQL must be executed.
    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.

  5. #5
    Join Date
    May 2004
    Location
    San Diego, CA
    Posts
    7

    Angry

    I know that SQL always faster, but... PL/SQL is more flexible and I have a feeling that by creating Function or Procedure (compiled) I can accomplish my task. The problem is... I couldn't find a right way to do it.

    Thanks,
    Alex

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I know that SQL always faster
    Then why, oh why, are fixated on PL/SQL?
    All PL/SQL can do is eventually invoke SQL;
    and incur the overhead of a context switch between the SQL engine & PL/SQL engine.
    What part of "SQL always faster" do you NOT understand?
    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.

  7. #7
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Cut the hostilities, guys.

    The first remark on the use of PL/SQL is right. This is no PL/SQL issue. The nature of the demand is SQL-based, so even if you would wrap it into a procedure, there will still be SQL executed, so it is not a question of flexibility of more efficient handling...........

    Have you done some experimenting with a smaller subset and 'traditional' hints ( like /*+first_rows*/)
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  8. #8
    Join Date
    May 2004
    Location
    San Diego, CA
    Posts
    7

    Angry

    Looks the same as previous one.

    Thanks,
    Alex

  9. #9
    Join Date
    May 2004
    Location
    San Diego, CA
    Posts
    7

    Thumbs up

    I found a way around it.

    Thanks all for your input,
    Alex

Posting Permissions

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