All Forums Database
Langaliya.Nirav 15 posts Joined 04/11
09 Nov 2014
Suggestion to use ALL or MAX function

I want your suggestion to on using ALL / MAX function in this case.

 

​DROP TABLE STUDENT_VT;
CREATE VOLATILE TABLE STUDENT_VT , NO LOG
	(
	 NAME CHAR(20) NOT NULL
	,SUB_1  INTEGER 
	,SUB_2  INTEGER
	,SUB_3 INTEGER
	) 
	PRIMARY INDEX (NAME)
	ON COMMIT PRESERVE ROWS;

	INSERT INTO STUDENT_VT ( 'Nirav',45,	54,	87);
	INSERT INTO STUDENT_VT ('Jishin'	,45	,44	,67 );
	INSERT INTO STUDENT_VT ( 'Maulik	',85	,40,	67);
	INSERT INTO STUDENT_VT ( 'Maharshi'	,45	,94,	67);
	INSERT INTO STUDENT_VT ( 'Hitesh'	,45,	44,	97);

 

SELECT NAME, SUB_1, SUB_2, SUB_3
FROM STUDENT_VT 
WHERE (SUB_2) >= ALL 
(SELECT SUB_2 FROM STUDENT_VT) ;

NAME SUB_1 SUB_2 SUB_3

Maharshi             45 94 67

 

  1) First, we do an all-AMPs SUM step to aggregate from
     STUDENT_VT by way of an all-rows scan with no residual
     conditions.  Aggregate Intermediate Results are computed globally,
     then placed in Spool 2.  Final result rows are placed into Spool 2,
     which is built locally on the AMPs.
  2) Next, we do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by
     way of an all-rows scan into Spool 4 (all_amps), which is
     duplicated on all AMPs.  The size of Spool 4 is estimated with
     high confidence to be 480 rows (8,160 bytes).  The estimated time
     for this step is 0.01 seconds.
  3) We do an all-AMPs JOIN step from LN056J.STUDENT_VT by way of an
     all-rows scan with no residual conditions, which is joined to
     Spool 4 (Last Use) by way of an all-rows scanLN056J.STUDENT_VT
     and Spool 4 are joined using an exclusion product join, with a
     join condition of ("STUDENT_VT.SUB2 < SUB2").  The result
     goes into Spool 1 (group_amps), which is built locally on the AMPs.
     The size of Spool 1 is estimated with index join confidence to be
     6 rows (318 bytes).  The estimated time for this step is 0.01
     seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1. 

SELECT NAME, SUB_1, SUB_2, SUB_3
FROM STUDENT_VT  
WHERE (SUB_2) >=  
(SELECT MAX(SUB_2) FROM STUDENT_VT) ;

 

NAME SUB_1 SUB_2 SUB_3

Maharshi             45 94 67

 

This query is optimized using type 2 profile DR147051, profileid 10001.
  1) First, we do an all-AMPs SUM step to aggregate from
     STUDENT_VT by way of an all-rows scan with no residual
     conditions.  Aggregate Intermediate Results are computed globally,
     then placed in Spool 3.  The size of Spool 3 is estimated with
     high confidence to be 1 row (19 bytes).  The estimated time for
     this step is 0.02 seconds.
  2) Next, we do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by
     way of an all-rows scan into Spool 1 (all_amps), which is built
     locally on the AMPs.  The size of Spool 1 is estimated with high
     confidence to be 1 row (25 bytes).  The estimated time for this
     step is 0.00 seconds.
  3) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
     by way of an all-rows scan and send the rows back to the
     Dispatcher.
  4) We do an all-AMPs RETRIEVE step from LN056J.STUDENT_VT by way of
     an all-rows scan with a condition of ("STUDENT_VT.SUB2 >=
     :%SSQ20") into Spool 5 (group_amps), which is built locally on the
     AMPs.  The size of Spool 5 is estimated with no confidence to be 2
     rows (106 bytes).  The estimated time for this step is 0.01
     seconds.
  5) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 5 are sent back to the user as the result of
     statement 1. 

--Nirav Langaliya
dnoeth 4628 posts Joined 11/04
09 Nov 2014

Hi Nirav
>= ALL is very uncommon SQL syntax, better go with = MAX, which has a better plan.
But you could also utilize an OLAP function:

SELECT NAME, SUB_1, SUB_2, SUB_3
FROM STUDENT_VT
QUALIFY RANK() OVER (ORDER BY SUB_2 DESC) = 1;
As soon as you need a GROUP/PARTITION BY this will use less resources

Dieter

You must sign in to leave a comment.