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
0
votes
1 answer
Oracle sql case sensitive index
In my project database present table Products, one column called 'Name'
So, this table can contain 'Product A' and 'Product a'
Both products should be selected in simple select query.
I am created index:
CREATE INDEX IDX_PRODUCT_NAME ON…
Ihor Yatsenko
- 91
- 8
0
votes
3 answers
Why NOLOCK is ignored "in the FROM clause that apply to the target table of an UPDATE or DELETE statement"?
I am confused by the BOL phrase:
"READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that…
Gennady Vanin Геннадий Ванин
- 10,136
- 11
- 75
- 106
0
votes
0 answers
How to force oracle to use index or ordered hints for remote joins
I'm using Oracle 11g. I have a query that joins local table with remote tables using db links. I want the driving table to be the remote table as I primarily filter using remote table to get a few rows. I then want to join them with local table.
The…
Vijay Jagdale
- 2,321
- 2
- 18
- 16
0
votes
2 answers
Why is my query hint being ignored?
I don't get why I can't use the primary key as index in my view.
Here's the main table
CREATE TABLE [dbo].[xFedDBLogMsg](
[ID] [int] IDENTITY(1,1) NOT NULL,
[msgType] [int] NOT NULL,
[date] [datetime] NOT NULL,
[delay] [time](7) NOT…
Serge Profafilecebook
- 1,165
- 1
- 14
- 32
0
votes
1 answer
Using Query Hint on FOR XML
does anyone know how about using Query Hints with the "FOR XML" statement? My (Sub-)Query looks like this:
SELECT ','+RTRIM(username) FROM @TmpDistinctUsers
WHERE vergabe_nr = b1.vergabe_nr
ORDER BY rn DESC
FOR XML…
Weissvonnix
- 731
- 7
- 23
0
votes
1 answer
MongoDB index use on find all without hint
I've got a question on how to write an index properly to avoid resorting to a hint.
Sample "Test" Collection Schema
{
_id: ObjectId(),
a: ,
b: ,
c: ,
d: ,
e: {
f:…
John Turner
- 45
- 2
- 6
0
votes
1 answer
How can i exclude a mongo index from a query?
Does anyone know of a way to run a query in MongoDB, and specify that a named index NOT be used?
We have multiple indexes on our data and there are situations where mongo is making a poor choice about which index to use to satisfy some types of…
BrentR
- 878
- 6
- 20
0
votes
2 answers
TSQL optimize multiple Intersect
The problem query uses multiple Intersect.
Changing it to an in ('alpha','beta','gamma','delta','epsilon','phi') group by having count() = 6 is not an option as the application supports like with wild cards (e.g. alpha%). But the count() = 6…
paparazzo
- 44,497
- 23
- 105
- 176
0
votes
1 answer
SQL Server Query Optimization Using Join Hints
We have a query in our system that has been a problem in the amount of logical reads it is using. The query is run often enough (a few times a day), but it is report in nature (i.e. gathering data, it is not transactional).
After having a couple of…
James
- 572
- 3
- 9
- 17
-2
votes
1 answer
Optimize Oracle query with Hints
I have this complex Oracle query.
SELECT
r3.object_id
FROM
nc_references r
INNER JOIN nc_objects o ON r.object_id = o.object_id
AND o.object_class_id = 90000330 /* Work Item */
INNER JOIN nc_params …
Peter Penzov
- 1,126
- 134
- 430
- 808