ORACLE – Using DENSE_RANK and KEEP to simplify multi-layer analytic queries

In queries, I commonly need to extract only the top of a sorted list of results. As time has progressed, ROW_NUMBER() and the OVER() clause have proven essential in writing simple and fast queries to meet this need. Below is an example. In this case, we have an table that captures unit pricing of products by product name and date. We want to write a function that simply returns a text version of the price for display and “NO PRICE FOUND” if nothing is available.

SQL> DESC unit_price
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCT_NAME                                       VARCHAR2(50)
 PRICE_DATE                                         DATE
 PRICE                                              NUMBER


SQL> SELECT *
  2  FROM unit_price
  3  ORDER BY product_name, price_date;

PRODUCT_NAME                                       PRICE_DAT      PRICE
-------------------------------------------------- --------- ----------
SCREWDRIVER                                        21-DEC-13       6.25
SCREWDRIVER                                        22-DEC-13       6.27
SCREWDRIVER                                        23-DEC-13        6.4
SHOVEL                                             21-DEC-13      10.48
SHOVEL                                             22-DEC-13      10.49
SHOVEL                                             23-DEC-13       10.6

6 rows selected.


SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT TO_CHAR( price )
 13      INTO latest_price
 14      FROM
 15      (
 16        SELECT
 17          price,
 18          ROW_NUMBER() OVER ( ORDER BY price_date DESC ) rn
 19        FROM unit_price
 20        WHERE product_name = prodname
 21      )
 22      WHERE rn = 1;
 23
 24      RETURN latest_price;
 25
 26    EXCEPTION
 27      WHEN no_data_found THEN
 28        RETURN 'NO PRICE FOUND';
 29    END;
 30
 31  BEGIN
 32
 33    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 34    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 35
 36    wrench_price := get_latest_price( 'WRENCH' );
 37    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 38
 39  END;
 40  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

The get_latest_price() function does a good job of quickly extracting the latest available price for the passed product. You might argue that this is the most readable approach to extracting the required data. However, there are two points of get_latest_price() that make it slightly complicated:

1) The query that has two layers: an inner query to extract a list of of prices and their associated row numbers ordered by date, and an outer query to select only the first row of the inner query.
2) Since a NO_DATA_FOUND is an expected condition, an EXCEPTION block is needed to handle the return.

Below is an example of using the KEEP and DENSE_RANK analytic functions to achieve the same behavior with a single-layer query and no EXCEPTION block. Depending on your personal preference, you may not find this version easier to read. I also do not believe there is any cost/benefit in terms of speed.

SQL> DECLARE
  2
  3    scredriver_price VARCHAR2( 50 );
  4    wrench_price VARCHAR2( 50 );
  5
  6    FUNCTION get_latest_price( prodname VARCHAR2 )
  7    RETURN VARCHAR2
  8    IS
  9      latest_price VARCHAR2( 50 );
 10    BEGIN
 11
 12      SELECT NVL( TO_CHAR( MAX( price ) KEEP ( DENSE_RANK FIRST ORDER BY price_date DESC ) ), 'NO PRICE FOUND' )
 13      INTO latest_price
 14      FROM unit_price
 15      WHERE product_name = prodname;
 16
 17      RETURN latest_price;
 18
 19    END;
 20
 21  BEGIN
 22
 23    scredriver_price := get_latest_price( 'SCREWDRIVER' );
 24    DBMS_OUTPUT.PUT_LINE( 'SCREWDRIVER PRICE: ' || scredriver_price );
 25
 26    wrench_price := get_latest_price( 'WRENCH' );
 27    DBMS_OUTPUT.PUT_LINE( 'WRENCH PRICE: ' || wrench_price );
 28
 29  END;
 30  /
SCREWDRIVER PRICE: 6.4
WRENCH PRICE: NO PRICE FOUND

PL/SQL procedure successfully completed.

Oracle LISTAGG() — ORA-01489: result of string concatenation is too long

The LISTAGG() function in Oracle is a very useful to aggregate delimited lists of column values into single strings. However, it has a limitation based on the maximum length of VARCHARs within the database. In Oracle 11g, this maximum is 4000 characters. Thankfully, this limitation has been extended to 32,767 in 12c. Here is an example of what you may experience when exceeding this internal limit:

SQL> DESC str_data;
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 VALUE                            VARCHAR2(30)


SQL> SELECT SUM( LENGTH( value ) ) FROM str_data;

SUM(LENGTH(VALUE))
------------------
              4580


SQL> SELECT LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY id DESC )
  2  FROM str_data;
SELECT LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY id DESC )
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

In this example, we are simply trying to convert all values into a comma delimited list. However, the total length of the aggregated values exceeds the 11g limit of 4000 characters. This causes an internal overrun when trying to aggregate these values. Let’s say we can loosen the requirements for our return value a little. Maybe we can return as much of the list as possible, but not exceed the system-imposed limit of 4000 characters. Although not very straightforward, additional analytic functions can be used to determine when this maximum is reached and prevent the internal overrun from occurring. In this case, we’ll use a running total in the form SUM( LENGTH( value || ',' ) ) OVER ( ORDER BY id ASC ) to determine at which point we exceed the total character limit. From this row forward, we will begin taking substrings of the values to stay at the limit. The first row to exceed the character limit will be truncated to meet a total of 4000 characters. All strings thereafter will effectively be reduced to NULL. Notice the comma delimiter has been moved out of the LISTAGG() function and into the inner query so we can count the delimiters as part of the 4000 character limit.

SQL> SELECT LISTAGG( value ) WITHIN GROUP ( ORDER BY id ASC ) aggvalue
  2  FROM
  3  (
  4    SELECT
  5      id,
  6      CASE
  7        WHEN tot_length > 4000 THEN SUBSTR( value, 1, LENGTH( value ) - ( tot_length - 4000 ) )
  8        ELSE value
  9      END value
 10    FROM
 11    (
 12      SELECT id, value || ',' value, SUM( LENGTH( value || ',' ) ) OVER ( ORDER BY id ASC ) tot_length
 13      FROM str_data
 14    )
 15  );

AGGVALUE
--------------------------------------------------
7WI7POD2EGGHV2T709,AQ6YA2D0S4Z65XN58X,CUX6P8LG2U8B
,D2DV99I0UNJ8C4MPTF62XFAJBM04,OX66C2F765,LXL,2SF11
M4,OMGPOD0LYF60ZLNKBSSEDRA,8PLD95W0BDVUP206LC3LJ8S
DL7GEEF,XB12YJWL9S1VPOOQX4V0W02,TX2SNKOXNFYT,ZMGED
YB7,JAP3HBEH5R93VL3LJW80OYNA6CIU,3XLOJ1J312,OWITEC
K5CMEN9V8PM8XSFB3TA,NYN6E7TQT3,8GJYWHKZ5B,RZCID5MZ
76,2JZD6D7QK4O9L4FDEHE429ZH,B1,1XM5U8T,C4753UKWWEP
XVKR2H593U1,7BUVECHZY9MIHCZ0ZO,EWVS9I2W8P9DYOY3KFC
KU,18E7JR,5UO,QTUQDQ43LVYM8Q3WOKV5PXT4FKBOXK,F28P1
P8N,8,HERBBW3M7VRH45E5,BADJO3NOXYQXW,CKH8,0I7JK,1C
LHD4DZL7OC66BQ29S6Z,D0M4OLOZZCP55HS1WJYM8AI,7BDTO7
BXPNHA224DCBCUTY7890,1GINLBZXDS0XP06F7KWE9UAG28,3H
B9V5PYI1NENU212DIS1E,MH4BB4P9Q2N9TIIYFQOL22NXHM,2H
H4CLMML0U,PLOX86C1HT14U31S33HETLTMBPAUT,HN1ELU9EOF
AN,XLIXGO41FLOL1BY,1KTPI7NY4IQ7DRYBF4Q20TB,F22PCDD
VT,EXIAE148,TG2CU,FN2491GMWEVL0TXR45V2DD,DVWHWO45I
QRHNEBG4,T3B8ZVDAPIGQE3BKJ1Y5L4M6,9F4DWXZZDLD,Q6L0
CEJ9TAAILK,KRY6CQCDRAAOW24UUI6JDDPUYXGU,Z1YNW5C4YQ
71HAXEID3BGTVYAR,OF07QEBF6F010U2W,GGSW3Y9DAQTATHCB
6JVD2N8WBYFDFZ,XAOUDQS2QR,08,SIMKWDWP4WSE89JYKBKFO
NKAF,YKV,VGQIWVTA547PPW4WPD,A7POS38KK5SY,5VFOA1JO3
S09CTKAOTC5AKDZ,B31NS2AZMDZYOLS6FKJI8,S2X,FCCST6IP
YTMZ2EE9AH52U8,OSSGSQ9SG04V4643XRBLVKTTT0E5,BUE85S
KKFA6YYI48JI6,SQY,2RIS6ME136P5X,PMF9WQTP44C0WS,41D
LW,CF,MJHV4VYTVZS2SMG2JFRFLCQGFP3QH,FB6X50MRIZ3,3P
FL6,SDBNO5F19LMM8D51WX,EEZ1,JU41XW183FE6IUDFIRN,5O
0Z264TK87C7GOL2HAC00SQ8I,FPQ9JN2MM08VI48,AV2RKCNSJ
SFLD7HESW,D8ATY8OK37QV,W3KBK69YI6VXUJ0EXL9Q,2JJJMV
R2JGGV0,YEQDA39A2UUC6ATTMSF7GQG3MQPE,6SV8AEO0VKS9,
WQUGPEUYNWLG0K4GSO916B48175,MVOBXA66HHOC,ZNTGDQ6CA
W,4D4YXZTCAJAK6SMH2SS3OW,MOVEEI23R9TAU9ZIR0YXIS9BI
5,907SCT9JCF1X4JCKF,S593RQLG665UEOF,UGKICNABXNBDJD
YKFKLX9,HDYHNN8S4VDP2P7QSPSKGB3K0L,QROKW2SY86LFF7A
CE3R3R97,28DGI,P8FBSP7OCY9BN5Z05YK26BATVT010,1I7OI
E5MVMANE,9Q9C3MAKRSRL2,CT486JMRPSSUNF,YGG2K5ZGBLS7
T395V79,26XF361LWSPLQ0UDXKIRYLB1AT,MW1NXTD12NHNHYP
11LMKQOSW,TKXCZP208OIQ5QNEHHUVAKTJ8,SYLOGQMK9T332V
6573GOG23FMYC,D1HPALEIQV9RJ8UQANN2ZQ,9MBBFWYE,R3AK
EHLTQFXRCAFQQRIMOMZ0D,47KSCIZKBXQGJ03KTC0OZD3T1,KD
T4XFRYAYBQZ9P2QY,UCCYPAZDGN6T566MWZ3W0EV,3UJFRNQ7F
60AVC7KKLK4H4Y,ZDM34UIX6VDGXPKVT63,JD,GLO0HIT,275I
WH94L1DR,2RGDSO50H53K1NKNNV0D0VCEY,HUKVUM4OUDD6I0Y
GN9S2J,45Z19,81GY,CJZMMTXY,T2OMCOMSYWJ3U,WUZ2OLM2Q
W54NSE,NL58QHTMU80R5AG47I1,8GZPBY7BEQ,AE449LN4R2,U
TW36XF7GV9DCYJCJ484X8C51RYLV9,W44K6NQ5N6N4N9YH,ETR
2ZF4WKUO27Y3KZTK0P,0QSCS76TTGKPNVPNSPY88,O886GOOZN
HE76J969YC5JBYXLVA,1TWEY8X91G6UB8OR9W2YL4VIK7SA,I4
IH,ELQCG7ZF1K61WT80V,05FM0OP,R1S,0KG1K5Y9C3JS5RBS,
OH4NJ5H,3NHD9Q00,MR6V2K4EZ5WQF7D,6ELX09O,JXEP,CL5J
CNDDC,H9K,KPMAUWY6RA,L5PKKCXQJJHK4SKFOGZVH,WZYFWKG
K17B9W7PA2M1R4FC,2ZAVUOI6O,LL50WZXVSERU2M2P8,BU0Y,
8AQ8YRQRWU28M90HU6W9FW4OIY5II,MSWBE,NPPRNH0GCMKJE0
L9X3NR9WRHWCKS6G,9YP9HNXQ,JO8XZQSG89J585GJ19JTX8Q,
CT12XB1HSV,8NJX4M4YEMPT0ZSAL5G2,IAZW2TX057EVPEONEI
7KXKACMCN,S3D,6N5PDV5RC,9B3INDBI531D,4MNZUXAA,VKL2
AUFQVH,13YPXUT6TGLESA4KA6G37XUDDD3,6Z24,BY1GH,OEWP
46,5CGBIYSB9FG1U3TVBYSKG,SA9O357E,0QUOM2YBBKLDS5AD
HN6,5DYZF1JGQCEC095,W247VJ6DLI5RWVKBKCLF,D5CFDNCTQ
1P7ELC7DURW4RCX,SBP0,SNA0BFSKPA72SXQ448F2GGDS,IYN4
EF5G120N651HKWJTU,G6JVS6EKH58OGT7P5B93F4V6MTC,AB98
V1TGBSZMMAQHHEU,TU0ZYQ2,ZVI,HNS,624RM7CSL0KW,QEFOC
M91BBVWBJTGS,S,V83M7ZFUKWH,GIN74CJZ383ENK,0CK,VT05
XDVIWJQHSTJ4GVCFW9WRJT6MJ,DQDXNDUD64N21RXR8V,WIXLH
PEM6SHY99AG,VM7P94XD7T04M,PO,5135Z3GVTMGF3HWE4,HZG
9PQMLBGBUCN0,FAJURGT6O8E,MYIMLIZDA,NEMBYSITYVQNBOU
YN68VKBP4,XZB8NFN,1MHN99S1EWQYHW6SUEJP0WV,LN6GXGQW
C5Y,4QPK4MISLAZRDVXNBI3MAO37QIYR,PV,NVDY02IM7V4YJ4
49ZT,MT24U9OC5BEE96DQ,XGAID8KTS7GP,HDVR0FXKP4B,WVD
8UBGKESD23F,AKKM6P13FU6Q2UOEC5W,2WACM83ELTG2L0JQF5
E,Q23AP0VWKOZ50PYWTN1RJH50UQG,03A380WSW596W2J,JEZE
OKHYNKWLSKRQ45,S,VXWV73PQKD2W4FJAYFNO6,0LOI0B9ZLXH
IEM24E5V0K,B42I7DSCPTAJGX6G2BO2V,I7KXGTG7DIT33VW,N
YSWHYGK25G28RH3WW2JCOL5V,WEFDQUFV750C1LD0,O,YAAEVM
GKIZJGOAWB7,I1O883JOAW27W,WGADJUZVQDWKI2CJG3EP,XNI
ZKBYU,7FFTU84W7XS,L6AKM0SOLB4K,W0MCD7,A57QSMZ,G9I0
VKHAJS9XAZXDB,F7LB3I205DTA43,AOHVMUOPAJ,C4KMLNYDX3
1JANDOUMD,3YF6LR11QZTYFO4OO3RRD5NLX,6Q8,IS8YSYWM,C
NRCDR68YQ5G7R1F10LW,0KTYYLWCAS1BU444BWKO8MX0AX,9KO
TW6HBLD8P2YAAK9LC3XZP9,UVAIO7IA,8H9CX8CU6A6U8I7A47
XJIT,O7P5J4OYYJ8M7SMG,D4TYRG4ZIF7PCUEG6,V1WBTQIT21