Skip to Content
author's profile photo Former Member
Former Member

Overflow error in Join cache - SAP HANA

Hi ,

I am getting an overflow error while executing the following statement in sql editor in Hana studio

select a.field1,a.field2 from table1 a,table2 b where a.field1=a. field1 and a.field = 'value';

table1 -- more than 2 billion records -- partitioned -- columnar

table2 -- more than 100 million records -- non partitioned -- columnar

when executed geting an "Overflow in join cache memory error "

same error is coming when I tried to join table1 with other tables

I also tried to do join on the above table in an analytical view and got the same error while data preview

Is it because the table has more data or it has been partitioned ?

Is there a way to increase the join cache memory??

Please help and thanks in advance

Thanks,

Gayathri

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Mar 26, 2012 at 01:19 PM

    Gayathri,

    your example SQL statement would output 2 billion rows. You definitely should add a GROUP BY clause and probably also restrict your result set with more WHERE clauses.

    Regards,
    Marc
    SAP Customer Solution Adoption (CSA)

    Add a comment
    10|10000 characters needed characters exceeded

    • Gayathri:

      Did you try adding the field2 filter 1st like:

      select a.field1,a.field2 from table1 a,table2 b where a.field2 = 'value' and a.field1=b. field1 ;

      Also try out:

      a) Force a inner join:

      select a.field1, a.field2 from table1

      wher table1.field2 = 'value'

      INNER JOIN table2

      ON table1.field1 = table2.field1

      b) Try deleting say 100,000 records from your table that is one short of 2 billion records and try SQL - just to make sure that it is not a partition issue - only if you can restore this data back into the original table

      Good luck!

      Rama

  • Posted on Apr 25, 2012 at 06:17 AM

    Hello,

    Your original post said:

    select a.field1,a.field2 from table1 a,table2 b where a.field1=a. field1 and a.field = 'value';

    Yes, that would cause an overflow, because it has a typo (no conditions on table2).

    Could you please confirm that was a typo in your post and not in your sql?

    regards

    Rick


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.