Skip to Content
avatar image
Former Member

HANA Join Cardinality - Impact?

Hi,

I was wondering what the impact on the cardinality settings on Attribute and Analytical views are. I am aware of the theory behind cardinalities 1:1, :n, n:1 and n:m. I can imagine what the impact of a "Join Type" (eg Inner, Left Outer etc) would be as the SQL script behind the scenes will use this join type to join the views. However, what is the role of the cardinality? For. e..g, if I get my cardinality wrong but my Join right, would a reporting query on an attr/analytical view bring back wrong results? or will there be just a performance impact? Does the SQL optimizer or the OLAP/Join engine optimize the SQL query generated based on the cardinality rule?

Any thoughts on this would be highly appreciated.

Thanks in Advance,

Anooj

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    May 31, 2012 at 06:18 AM

    My two cents:

    In many cases, the cardinality representation is related to the logical data model. It depicts the relationship from the master and child table perspective.

    From the underlying join point of view, the optimizer will define the driving table and joined table. Depending upon the join (1:1 resulting in Equi join, 1:n resulting in inner / outer join), the optimizer (Rule based or Cost based) will try to optimize the Query generation.

    It is recommended that the master table in the join condition should be smaller than the child table, hence the relationship 1:n would be optimized than n:1.

    Hope this helps.

    Regards,

    Ravi

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 31, 2012 at 07:38 AM

    Hi Anooj

      As per my understanding it does not effect the no.of records, only the type of join effects result rows ,the cardinality will have impact on performance.

    Thanks

    Santosh

    Add comment
    10|10000 characters needed characters exceeded

    • See if you can test it with 2 tables having 10 million and 50 million records each.

      Check how the query performance works in the execution plan. It should tell you how the index scan and joins are performed.

      Regards,

      Ravi

  • May 31, 2012 at 04:20 PM

    Anooj:

    The cardinality and join are complex topics which directly relate to SQL performance.

    Here are my thoughts on your questions:

    1 - However, what is the role of the cardinality? For. e..g, if I get my cardinality wrong but my Join right, would a reporting query on an attr/analytical view bring back wrong results? or will there be just a performance impact?

    Cardinality is used by the SQL engine to  determine the optimal query plan to complete execution of a query.   Yes, the attr / anal view will bring back wrong results (in context to no.of rows returned and performance).    If cardinality is wrong, then your join condition has to be fine tuned.  The performance of the join will be impacted if the cardinality / join is wrong.

    2 - Does the SQL optimizer or the OLAP/Join engine optimize the SQL query generated based on the cardinality rule? Yes, the SQL engine uses the cardinality factor

    Regards,

    Rama

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Anooj,

      It is quite obvious that for the 1:n (Master - child) the relationship has to be defined on the Primary Key and reference key values. By default the PK is unique. If you haven't set the Unique key constraint either with PK or Unique Key, the relationship will always throw error.

      On similar lines, when you define the foreign key relationship in reference to Primary key, the check is performed by the database when you try to insert data in the child table. If the reference key value does not exist in the Primary key table, it gives referential Integrity constraint error.

      Regards,

      Ravi

  • avatar image
    Former Member
    Dec 21, 2012 at 07:39 PM

    I'm having confusion with certain cardinalities, especially many to one.

    For example, if I have 2 tables. First table has 100 records and second has 10 records, if I use many to one cardinality (let's use inner join), I'm expecting the records to reduce, yet it does not happen.

    This is confusing me because the opposite approach increases the record count (1...m).

    Anyone have an idea?

    Add comment
    10|10000 characters needed characters exceeded

  • May 21, 2016 at 11:06 AM

    This message was moderated.

    Add comment
    10|10000 characters needed characters exceeded