Archives of the TeradataForum
Message Posted: Fri, 02 Jan 2004 @ 16:15:37 GMT
McBride, Michael wrote:
A year ago i run some tests on a 4-node 5300 (2GB memory, 36 AMPs à 36519 cylinders, 144 disks 36GB RAID 1). The following query was similar to the requested one:
/*** select all data for rows with the highest quantity for each partkey/shipdate combination -> 237.288 rows ***/ sel * from lineitem where (l_partkey, l_shipdate, l_quantity) in ( sel l_partkey, l_shipdate, max(l_quantity) from lineitem l1 group by 1,2 ); -- cputime: 124,58 -- IOs: 152616 sel * from lineitem l1 qualify rank() over (partition by l_partkey, l_shipdate order by l_quantity desc) = 1; -- cputime: 96,87 -- IOs: 87376
Especially if you have to use nested subqueries or it's a joined view instead of a base table OLAP functions are usually more efficient.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|