Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: subquery vs intersect

    I am using following two ways to fetch data from database

    1 option is using intersect = it takes few secs to execute
    2 option is using subquery = system hangs


    1 option)
    SELECT cp.benum,
    cp.bedate,
    cp.iec
    FROM commodityprofile cp
    where
    cp.iec in (select distinct(cp1.IEC)
    from CommodityProfile cp1
    Group By cp1.IEC
    having sum(cp1.ASSESSABLEVALUE) > 7.363636363636364E8
    )
    intersect
    SELECT cp.benum,
    cp.bedate,
    cp.iec
    FROM commodityprofile cp
    where cp.bedate >= to_date('1/1/1990','mm/dd/yyyy') and bedate <= to_date('1/1/2005','mm/dd/yyyy')


    2 option)

    SELECT cp.benum,
    cp.bedate,
    cp.iec,
    cp.portofshipmentcode,
    cp.coocode,
    cp.portofimportcode,
    cp.cha_id
    FROM commodityprofile cp
    where
    cp.bedate >= to_date('1/1/1990','mm/dd/yyyy') and bedate <= to_date('1/1/2005','mm/dd/yyyy') and
    cp.iec in (select distinct(cp1.IEC)
    from CommodityProfile cp1
    Group By cp1.IEC
    having sum(cp1.ASSESSABLEVALUE) > 7.363636363636364E8
    )

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What does EXPLAIN PLAN say about the queries?

    They aren't the same, because the first compares the 2 sets of rows on all 3 columns (benum, bedate, iec) whereas the seconds compares only on (iec).

    Perhaps there is an index on (benum, bedate, iec) but not on (iec)?

Posting Permissions

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