Archives of the TeradataForum
Message Posted: Mon, 23 Jul 2007 @ 13:33:34 GMT
<-- Anonymously Posted: Monday, July 23, 2007 06:48 -->
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.
/*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 )
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|