Hana column store error 2617 exceeded 2167466761

[former_member390551]

Hi All,

I have a HANA table with more than 2.3 billion records and when executing a query I get the following error, sap Hana column store error 2617 exceeded limit 2167466761.

The table has partitions, but when executing a full table scan I get this error, when I use the 'where' condition the query executes fine.

Select median(FIELD A) from TABLE - get error

Select median(FIELD A) from TABLE where YEAR(FIELD B) < '2017' - return results, this will run on half of the table records.

I use HANA 2 SPS 04.

Any ideas?

Thanks,

Guy

[lbreddemann]

Any ideas? Sure.

While the table itself is partitioned and does not seem to have any issues with storing more than 2bn records, intermediate tables required for the query execution are not partitioned.

Those intermediate tables are limited by the 2bn records/table limit.

Now, with median() you ask for all all records to be sorted and then for the value that is just at the middle point of all records. This requires to, well, sort all the records and sorting requires an intermediate materialisation.

When doing this on more than 2bn records, you run into the error.

[former_member390551]

Hi Lars,

Yes, the problem is indeed an intermediate table of over 2b records, sometimes the optimizer materialized this table, and that is where I get the error.

The calculation has to be over all the table records, any idea to where to look for the solution?

Regards,

guy

[lbreddemann]

Hey Guy,

sure there are more ideas on that 🙂

I looked up the oldy-but-goodie book "SQL for Smarties" by Joe Celko and used the approach "31.3.7. Median with Characteristic Function".

A crude test like this

   SELECT AVG(DISTINCT  val)
   FROM (
   		SELECT P1.val
      FROM bn0 AS P1, bn0 AS P2
      
      GROUP BY P1.val
      
      HAVING TO_DOUBLE(SUM(CASE 
      				WHEN P2.val <= P1.val 
      				THEN to_bigint(1) 
      				ELSE to_bigint(0) 
      			END)) 
        	>= (to_bigint(COUNT(*)) / 2.0)
      	 AND TO_DOUBLE (SUM(CASE 
      	 			WHEN P2.val >= P1.val
           			THEN to_bigint(1) 
           			ELSE to_bigint(0) 
           		END))
        	>= (to_bigint(COUNT(*))/2.0)
        );     

worked for me on a large (> 2bn record) data set, where median() consistently fails.

I'm not going to explain this solution here, but I recommend the book as it has this and so many more approaches to challenges with SQL.