#SQLSERVERLearning : Exception on Invalid/Incorrect Hints?

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All findings or Observations with SQL Server for any Oracle guy can be accessed here.

Ideally, don’t encourage the use of Hints in any database, provided we dint have any other alternative.
Hint helps us to play with execution plan for testing by influencing Optimizer to use, provided query options (Join method, Access path, Index usage, pushing predicate….).

In today’s blog will discuss, what happen when we specify incorrect/invalid hints in Oracle and SQL server.
Does database give any exception or warning, that provided hints are incorrect and cannot be enforced or it simply ignores it.

So what exactly Hint is? Whether its an option for Oracle Optimizer to consider or an order to follow.

The hint is a Command.!
If Hint is Valid, Authentic as per SQL Compliance, the Optimizer is bound to follow it.
But there are many cases when Hint can be Invalid.

Will talk through an example, when we would try to apply Invalid Hint and check how Oracle and SQL Server behave.

Let’s start with Oracle, we will use standard Emp and Dept tables for demo.
Before starting with the demo will like to talk a bit about Hash Join.

Hash join is only use to join two tables based on Equality expression.It can’t evaluate >= or <= or any other non equality expression.
hence if we try to push hash join for below query it will be invalid.

SELECT /*+ USE_HASH(DEPT)*/EMP.*,DEPT.*
FROM EMP , DEPT
WHERE EMP.DEPTNO <= DEPT.DEPTNO;

We are trying to join based on Expression other than equality and hence it would require either Nested loop or Merge join to evaluate queries.

For above SQL, Oracle uses below Plan.
-----------------------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 |
| 1 | MERGE JOIN | | 59 |
| 2 | SORT JOIN | | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPT | 7 |
| 4 | INDEX FULL SCAN | PK_DEPT | 7 |
|* 5 | SORT JOIN | | 14 |
| 6 | TABLE ACCESS FULL | EMP | 14 |
-----------------------------------------------------------------

In oracle, when we try to Run above statement, it would run successfully without giving any warning or errors.
We would need to cross verify whether hints are applied or not by checking underlying plan.
For Checking whether there was any issue to apply mention hint, we would need to enable 10053 trace and check Dumping hints section to verify whether any error was reported.

Let see how SQL Server behaves for the same case.

SELECT * FROM HUMANRESOURCES.DEPARTMENT D , HUMANRESOURCES.EMPLOYEE E
WHERE D.MODIFIEDDATE <= E.MODIFIEDDATE OPTION (HASH JOIN);

In SQL Server, we apply hint using OPTION Clause.
When we try to run above statement, we will get an exception message from the SQL Server Engine.

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

hint

SQL Server provides a way of throwing back some error, when we try to provide any Invalid hints.
It won’t execute statement with some other valid joins methods, instead try to Force mention hint.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, SQL Server Learning and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s