Suggestion to use ALL or MAX function

Database
Enthusiast

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. 

Tags (1)
1 REPLY
Senior Apprentice

Re: Suggestion to use ALL or MAX function

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