Performance on large table join return top result
We're execution POC on a public sector customer.
In their enviorement, they have a SQL like this:
select top 100 * from table A join table B on A.party_id=b.party_id ( No filter here）
table A: about 40Million records, table B: 1.2 billion records, it's in a 6 nodes cluster. each table parttion by HASH 40 across all nodes.
It take 11 mins to finish this SQL, and query nodes memory almost used up.
While other Database, such as Oracle can use hint first_rows, this wll return top 100 result without query on all table. It only takes serval seconds on other DB.
My questions here, does this any Statement optimization in HANA can return top 100 result as quickly as it can.