Archives of the TeradataForum

Message Posted: Mon, 23 Jul 2007 @ 13:33:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, July 23, 2007 06:48 -->

Dear All,

Please help me to evaluate the better of the queries(below).

Can you please suggest me the scenarios in which either of these could be used?

The intention of the query is to find the latest record from a history table. One uses rank and qualify (Query A) and the other max function (Query B) .

The difference I found in the EXPLAINS is that the query using Rank and Qualify scans the table only once, whereas with the MAX- method, the table is scanned twice. Given that my table is bulky (over 100 million Rows), should query A be the automatic choice? I remember having read that its 'better' to avoid OLAP functions.

Appreciate all your insights.

Regards

     /*QUERY A*/

     select
      AAG.*
     from
      ddedwmig_vbdw.v_ACCOUNT_ACCOUNT_GROUP AAG
      CROSS JOIN CPMS_CURR_PERIOD CCP
     where
      Source_System_Id = '10300' and
      ACCOUNT_ASSOC_START_DT  between CCP.START_DT and CCP.END_DT

     qualify
      row_number() over (partition by account_num, account_modifier_num,
     selection_criterion_id
      order by account_num ,account_modifier_num, selection_criterion_id,
     account_assoc_start_dt desc ) = 1
     ;

     /*QUERY B */
     sel * from ddedwmig_vbdw.v_ACCOUNT_ACCOUNT_GROUP
     where
     (account_num, account_modifier_num, selection_criterion_id,
     ACCOUNT_ASSOC_START_DT) in
     (
     sel account_num, account_modifier_num, selection_criterion_id,
     max(ACCOUNT_ASSOC_START_DT) from
      ddedwmig_vbdw.v_ACCOUNT_ACCOUNT_GROUP CROSS JOIN CPMS_CURR_PERIOD CCP
     where
      Source_System_Id = '10300' and
      ACCOUNT_ASSOC_START_DT  between CCP.START_DT and CCP.END_DT
     group by 1, 2, 3
     )

	<Prev	Next>		<<First	<Prev	Next>	Last>>