Tag : query hint


Understanding Map join in Hive

Apache Hive is a big data query language which is used to read, transform and write large datasets in a distributed environment. It has a SQL like syntax which gets translated into a MapReduce job in order to execute on Hadoop clusters. In Hadoop ecosystem, we use Hive for batch processing to extract, transform and load the data into a data warehouse system or in a file system which can be HDFS, Amazon S3, Azure Blob or Azure DataLake. However, Hive is not meant for OLTP tasks as it has high latency. In this post, we are going to learn Map Join which can be used to improve the performance of a hive query. We will also discuss the parameters required in order to enable the Map join along with its limitations.

What is Map join in Hive

Join clause in hive is used to combine records from two tables … More


The join order has been enforced because a local join hint is used – Warning in SQL Server 3

When we use a local JOIN hint in T-SQL query, sometimes, SQL Server throws a warning message as “Warning: The join order has been enforced because a local join hint is used.“. For example, below is a query with a local join hint:

After execution, we will get below warning message with result set:

In above code, we are using a local JOIN hint “INNER MERGE JOIN” which forces this query to override the physical join (which could be nested loop or merge or hash join) chosen by SQL Server with the physical join given in the query hint (which is MERGE join in this case). If you want to read more about the physical joins, visit my post “Nested loop, Merge, and Hash Joins in SQL Server“.

Here, SQL Server enforces a join order during query execution because a local join hint has … More


Convert a comma separated list into table 4

In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code:

Why OPTION MAXRECURSION?

Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we will not put this hint at the end, any string which requires more loop than 100 will raise an error.
To avoid the error we have used OPTION(MAXRECURSION 32767).

Thanks for reading.… More