In various SQL implementations, a hint is a description added to the SQL standard that instructs and forces a database engine to execute a query in a specific way that is specified by a user even if there is a better way to execute the query. For example a hint may tell the engine which indexes are used or whether to use an index at all. Implementation: Different database engines such as MySQL or Oracle have implemented hints using different ways.
Questions tagged [query-hints]
70 questions
4
votes
2 answers
Does Sql Server 2014's "Hekaton" compiled stored procedures address parameter sniffing issues?
SQL Server 2014's "Hekaton" in-memory table optimization proclaims, "Native compilation of business logic in stored procedures." Because of issues with "parameter sniffing" (see here and here) in SQL Server 2012 and earlier, however, I have always…
Mike
- 7,500
- 8
- 44
- 62
4
votes
1 answer
Why does SQL ignore an index hint and opt for a different index?
Given a table that has two indexes on it, one sorted in the reverse from the other and given these two queries.
Select value From SomeTable wITH (INDEX(IV_Sort_Asc))
Select value From SomeTable wITH (INDEX(IV_Sort_Desc))
I've come across a case in…
Middletone
- 4,190
- 12
- 53
- 74
3
votes
2 answers
optimize for and recompile hint in Oracle?
Is there any hints in Oracle that works the same way as these SQL Server hints?
Recompile: That a query is recompiled every time it's run (if execution plans should vary greatly depending on parameters). Would this be best compared to cursor_sharing…
KOT
- 1,986
- 3
- 21
- 35
3
votes
1 answer
Stored procedures and OPTIMIZE FOR UNKNOWN
I've read up on the SQL Server 2008 OPTIMIZE FOR UNKNOWN query hint. I understand how it works.
However, I have a question on where and when to use it. It cannot be specified inside a UDF. It can be specified inside a stored proc. However, this MSDN…
IamIC
- 17,747
- 20
- 91
- 154
3
votes
6 answers
SearchView not showing queryHint text
I have a search view with a background drawable, but I can't seem to get the text to appear no matter what I try. No text shows up in the SearchView.
I've tried the android:text and android:queryHint and even setting it programmatically using…
VIN
- 6,385
- 7
- 38
- 77
3
votes
1 answer
Query Optimizer can't push predicate past rollup? HINTs doesn't work also
This is the schema :
And this is the sql that as I understand is too complex for SQL Optimizer:
SELECT * FROM
(
select pp.Id as PaymentPartId, b.Id as BudgetId, grouping(bp.ID) as g1 , sum(pp.Amount) PaymentsSum, sum(bp.Amount) BudgetSum
…
Roman Pokrovskij
- 9,449
- 21
- 87
- 142
2
votes
0 answers
How to set custom hint via Spring JPA annotation
In a project, I need to specify oracle hint in query. Spring JPA gives us to ability defining hint in @QueryHint (javax.persistence) annotation for query definition. But when I set oracle hint, app throws Ignoring unrecognized query hint…
picassoo
- 21
- 3
2
votes
1 answer
How can I use the READPAST hint in NHibernate?
Is there any way I can get NHibernate to use the READPAST hint when selecting data from SQL Server?
matthew
- 243
- 4
- 8
2
votes
1 answer
Why do I have to Force Order with these hierarchy queries/
Below is an example of a query I might run where, for each category, I want the NumberOfCourses to represent not only that specific category but also any child categories under it. I think the query is fairly self explanatory.
select c.CategoryID,…
BVernon
- 3,205
- 5
- 28
- 64
2
votes
2 answers
When UPDLOCK get released in SQL server?
Recently I have gone through with Hints and Locks in SQL server. While google about this topic I have read one blog where some query have been written which I am not bale to understand. Here it is
BOL states: Use update locks instead of shared…
saurabh gangrade
- 33
- 1
- 8
2
votes
2 answers
SQL 2005: NOLOCK hint dramatically increases reads. WTF?
I have a stored procedure that does a lot more reads when the NOLOCK hint is added to the query. I'm baffled - does anyone know why, please?
Details:
The query is:
SELECT * FROM dbo. WITH (NOLOCK).
It was doing 40,000 reads and there…
David Wimbush
- 202
- 4
- 11
2
votes
1 answer
Experience with when to use OPTIMIZE FOR UNKNOWN
I have read the theory and views behind SQL Server 2008's "OPTIMIZE FOR UNKNOWN" query plan option. I understand what it does well enough.
I did some limited experiments and found that with a warm cache, it only was of benefit on > 100k rows.…
IamIC
- 17,747
- 20
- 91
- 154
2
votes
2 answers
SQL Server : the maximum recursion 100 has been exhausted before statement completion
I have a query that is returning an error with the maximum levels of recursion exceeded.
I know how to fix this by adding OPTION (maxrecursion 0) to the query however, I have tried adding this at various places in the query and I cant find where to…
WraithNath
- 17,658
- 10
- 55
- 82
2
votes
1 answer
Oracle Convert Select Union All to Parallel Select
I don't know if it's possible or not in terms of oracle execution architecture so I just wanted to ask.
I have a table that stores some schemas names (eg. sch1, sch2, sch3 ) and I am creating a union all query dynamically within a cursor using this…
Deniz
- 191
- 1
- 6
- 17
2
votes
1 answer
Jpa entity graph with hints and named query
I'm working to explore the new features of JPA 2.1 with spring ex. EntityGraph feature by making a sample CRUD operations using a sample relations between products, purchase order and order items.
below are the code I made for the main bean, I…
gasser
- 279
- 1
- 4
- 16