Questions tagged [optimizer-hints]
36 questions
1
vote
1 answer
Is a type conversion in a inner join safe if reliant on data/row filtering?
If a join condition does data conversion that's only valid on some of the rows, but your where condition filters the rows to the "valid" rows is that a "safe" join condition?
I'm of the opinion it's not but I'm by no means and expert and am just…
broll
- 65
- 7
1
vote
3 answers
Oracle-style execution hints
When you write rather complex SQL for Oracle, sooner or later you will have to apply the odd execution hint because Oracle can't seem to figure out the "best" execution plan itself. …
Lukas Eder
- 211,314
- 129
- 689
- 1,509
1
vote
1 answer
Are Oracle SQL Optimizer Hints applicable to SSIS?
I have added a SELECT /*+USE_CONCAT*/ at my query. I found great difference at SQL Developer (only top 200 rows) but I can't say that I found the same improvement at SSIS. Maybe that's because of the huge number of the returned records but I also…
1131
- 407
- 3
- 15
1
vote
3 answers
When to use hints in oracle query
I have gone through some documentation on the net and using hints is mostly discouraged. I still have doubts about this. Can hints be really useful in production specially when same query is used by hundreds of different customer.
Is hint only…
BoringProgrammer
- 91
- 1
- 11
1
vote
1 answer
How to use the hint FULL for a count all in Oracle
I'm new to the concept of using the hint optimizer in oracle and I would like to understand if I'm using the hint FULL properly
I have a query
SELECT COUNT(*) FROM MyTable
WHERE MyTable.name='RandomName'
So let's say the result got me 12345
Now…
napi15
- 2,354
- 2
- 31
- 55
1
vote
1 answer
make the optimizer use all columns of an index
we have a few tables storing temporal data that have natural a primary key consisting of 3 columns. Example: maximum temperature for this day. This is the Composite Primary key index (in this order):
id number(10): the id of the timeserie.
day date:…
EasterBunnyBugSmasher
- 1,507
- 2
- 15
- 34
1
vote
1 answer
Should i use MAXDOP to improve my maintenance stored procedure?
Okay so i understand the basics of MAXDOP, but i want to understand if this a valid scenario for using it.
I have a stored procedure which is quite resource hungry, but has been optimized to the max. It currently takes 30 minutes (local) to refresh…
RPM1984
- 72,246
- 58
- 225
- 350
1
vote
1 answer
Default degree of parallelism in oracle hints
I have used default degree of parallelism in order to gain performance tuning and I got the best results too. but I doubt it will impact when some other job access the same table at same time.
sample code below.
select /*+ FULL(customer)…
VRVigneshwara
- 1,009
- 8
- 10
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
2 answers
Oracle SQL strategy for slow parameterized/filtered queries due to static optimizer strategy
More simply put than the below: if one has one or multiple query parameters, e.g. x_id, (or report / table function parameters) that are performance crucial (e.g. some primary key index can be used) and it may be (depending on the use case/report…
Andreas Covidiot
- 4,286
- 5
- 51
- 96
1
vote
1 answer
Is direct-path insert a good way to do bulk inserts in Oracle?
We're trying to figure out the best way to handle BULK INSERTs using Oracle (10gR2), and I'm finding that it can be a pretty complicated subject. One method that I've found involves using the Append optimizer hint:
INSERT /*+ Append*/
INTO…
Jason Baker
- 192,085
- 135
- 376
- 510
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
3 answers
I'm getting this error : 'AttributeError: module 'keras.optimizers' has no attribute 'Adam''
I'm trying to run this code in Google Colab :
from keras import optimizers
from tensorflow.keras.optimizers import Adam
for i in range(5):
print(i)
model_mix = Model(inputs=[visible, visible1], outputs=x)
adam = optimizers.Adam(lr=0.01, beta_1=0.9,…
farheen fatima
- 13
- 4
0
votes
2 answers
How to update the weights of a model only if loss for current batch is smaller than previous
I'm trying to update the weights of a model during training only for those batches in which the loss is smaller than that obtained in the previous batch.
So, in the batches loop, I store the loss obtained at each iteration, and then I have tried…
kentropy
- 103
- 4
0
votes
4 answers
Use Hints for execution of all the conditions in where clause
Consider a query like :
select * from
where and and ;
Suppose Oracle executes the condition (any condition) and if not true, it does not execute the other conditions. So if I have other conditions…
HitchHiker
- 825
- 2
- 11
- 31