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
1
vote
0 answers
How do we workout with SQL Hints in tSQLt unit testing?
I have been trying to create unit tests for one of the stored procedures in my database using tSQLt and facing one issue,
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without…
Vighneshwar Madas
- 62
- 1
- 8
1
vote
1 answer
using Hint in a hosted variable?
All,
I want to use a hint in a hosted variable; in fact, we need to use a dynamic value of the hint (Hint should be valued at runtime). can we write an sql statement from this:
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
…
Aymanadou
- 1,200
- 1
- 14
- 36
1
vote
2 answers
How can I choose different hints for different joins for a single table in a query hint?
Suppose I have the following query:
select * from A, B, C, D
where A.x = B.x
and B.y = C.y
and A.z = D.z
I have indexes on A.x and B.x and B.y and C.y and D.z
There is no index on A.z.
How can I give a hint to this query to use an INDEX hint on A.x…
aw crud
- 8,791
- 19
- 71
- 115
1
vote
1 answer
Oracle SQL ignoring ordered index hint
I have the following query with the specified ordered index hint. I am wondering whether I'm doing something wrong that makes it ignore my ordered index hint.
select /*+ ORDERED USE_NL (ptp payr) INDEX (ptp, PER_TIME_PERIODS_N50) INDEX (ppa,…
Superdooperhero
- 7,584
- 19
- 83
- 138
1
vote
0 answers
MySQL locking hints and query optimization
I need to lock a full table in shared mode. If I execute something like "select 1 from (select * from mytable lock in share mode) t where 1=2", will the query optimizer remove the nested subquery and basically make the query no-op? In my simple…
Maxim Mazeev
- 11
- 1
0
votes
1 answer
EclipseLink expressions dilemma
I am trying to utilize EclipseLink expression since I found it quite convenient for dynamically combining query conditions.But that must be working with ReadAllQuery which do not have QueryHint (for batching) options that is A Must for my…
Lopakhin
- 269
- 1
- 3
- 16
0
votes
0 answers
Giving Optimizer Hint on DDL Queries
How to give optimizer hints on DDL query ?
I have a use case to set lock_wait_timeout in an alter query. I do prefer to give it for that statement alone. Is it possible to give optimizer hint in the same statement itself?
PS : I do not prefer…
INDIAN DECODER
- 95
- 1
- 9
0
votes
1 answer
Using query hints to use a index in an inner table
I have a query which uses the view a as follows and the query is extremely slow.
select *
from a
where a.id = 1 and a.name = 'Ann';
The view a is made up another four views b,c,d,e.
select b.id, c.name, c.age, e.town
from b,c,d,e
where c.name =…
lse23
- 1,267
- 2
- 10
- 19
0
votes
1 answer
Oracle exploit parallel execution in DMS (i.e. UPDATE query)
As you can see at this link sometimes the PARALLEL HINT doesn't work in forking parallel execution of an update query.
For select I must even force a FULL TABLE SCAN or a FAST FULL INDEX SCAN. How does it work for DML statement?
I used the directive…
Revious
- 7,816
- 31
- 98
- 147
0
votes
1 answer
Oracle index specified in index hint is invalid
I have 2 scenarios where I'm using the hint IGNORE_ROW_ON_DUPKEY_INDEX. The first seems to be working fine. The second scenario is failing with the error below and I'm unsure how to fix it.
ORA-38913: Index specified in the index hint is…
Pugzly
- 844
- 3
- 14
0
votes
1 answer
How to use mysql query optimzer hints in Yii2?
I've stumbled upon the optimizer hints MySQL feature here that I did not know existed and tried to figure out how to use this with Yii2.
MySQL supports setting specific options by using optimizer hints, e.g.:
SELECT /*+ NO_RANGE_OPTIMIZATION(t3…
Theo
- 2,262
- 3
- 23
- 49
0
votes
2 answers
Is it possible to specify fetchgraph/loadgraph in SpringDataJpa @QueryHint annotation
Is it possible somehow to specify the javax.persistence.fetchgraph or javax.persistence.loadgraph using @QueryHint in Spring Data Jpa?
I have an entity graph
@Entity
@NamedEntityGraph(
name = "shop_with_all_associations",
…
Max
- 766
- 9
- 19
0
votes
1 answer
ADO Transaction and READPAST
I don't know if this is the best way, if there is a better way, please post.
I have an application that read a file and insert records.
The entire file is processed in one transaction.
Before a record is inserted the table needs to be checked for…
Hannes
- 3
- 1
0
votes
1 answer
C# Entity Framework use SQL options/hints
I am working on some search optimizations in a WPF application.
There is a search XAML view, that uses a SQL Server view with IQueryable.
In SQL Server, I can use OPTION(QUERYTRACEON 8649) when I get the SELECT part from the view, but in the view…
user3239349
- 877
- 1
- 12
- 33
0
votes
0 answers
Query performance using CTE
I am going to use hint OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) at the end of query in my sp.Query contains union all operator so shall i use cte?will it improve or affect the performance?.suggest please.
Example
;with cte
{
QUERY…
Ram
- 727
- 2
- 16
- 33