1 Reply Latest reply: Feb 5, 2013 4:41 PM by JustinCave RSS

    Which of the 2 queries is ideal based on the scenario

    SamFisher
      Hello All,

      I have a collection table l_party_tab (number type) which has 5 records(party_ids).
      Also, I have 10,000 records by joining dtls & attr table(outer query).
      I've written 2 queries to see if the given input party_id's are present in the dtl table with 'CSPP' authorization.

      Test1:
      FOR i in 1..l_party_tab.count
      LOOP
      SELECT cr_party_id into l_cspp_auth_tab(i)
                   FROM xxccr_om_qp_pgtmv_dtls xoqpd,
                        xxccr_om_qp_pgtmv_attrs xoqpa
                  WHERE xoqpa.object_type = 'BEGEO'
                        AND xoqpa.external_attribute_name IN
                               ('AUTHORIZATION')
                        AND xoqpd.pgtmv_begeo_id = xoqpa.object_id
                        and attribute1='CSPP'
                        and cr_party_id=l_party_tab(i);                   
      END LOOP;
      Test2:
      SELECT cr_party_id bulk collect into l_cspp_auth_tab
                   FROM xxccr_om_qp_pgtmv_dtls xoqpd,
                        xxccr_om_qp_pgtmv_attrs xoqpa
                  WHERE xoqpa.object_type = 'BEGEO'
                        AND xoqpa.external_attribute_name IN
                               ('AUTHORIZATION')
                        AND xoqpd.pgtmv_begeo_id = xoqpa.object_id
                        and attribute1='CSPP'
                        and exists
        (select 1 from table(l_party_tab) a where xoqpd.cr_party_id=a.column_value);  
      I'm curious to know if there would be any scenario where test2 is better than test1 also can you please explain me the flow for better understanding since I'm a rookie.

      Thx
      Shank
        • 1. Re: Which of the 2 queries is ideal based on the scenario
          JustinCave
          Well...

          First off, they produce different results unless you can guarantee that your first query returns exactly 1 row for every element in the L_PARTY_TAB collection. But if you can guarantee that, there would seem to defeat the purpose of the query if you are saying that you are trying to determine whether the elements of the collection are present in the table.

          Second, given your tables, your data, and your process, what do your benchmarks tell you? Which one runs faster?

          Third, since the collection has a very small number of elements, you would probably want to use a CARDINALITY hint in the second query to let the optimizer know that the collection is going to be very small.

          Justin