SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.
Questions tagged [sql-tuning]
328 questions
0
votes
1 answer
Bind Dates in SQL Tuning advisor
I'm trying to bind a date to a variable using Oracle SQL Developers SQL Tuning advisor, but it recognizes it as a VARCHAR2 and not a DATE. Is there a way to specify the data type of the parameters you bind?
SQL:
SELECT * FROM Actv
WHERE ActvId =…
Jimenemex
- 3,104
- 3
- 24
- 56
0
votes
2 answers
Oracle complex query with multiple joins on same table
I am dealing with a monster query ( ~800 lines ) on oracle 11, and its taking expensive resources.
The main problem here is a table mouvement with about ~18 million lines, on which I have like 30 left joins on this table.
LEFT JOIN mouvement…
mouad tk
- 24
- 3
0
votes
1 answer
Using static list of values in a IN clause fluent nhibernate
I have to write a select query with around 10,000 id's in a in clause. Query is very slow as i have to write 10 in clause with 1000 ids in each.
One way i found on internet is that before firing a select, add these 10000 ids in a table and do a join…
user3176053
- 11
- 1
- 5
0
votes
4 answers
How can i improve performance of below query
select
m.*
from
MEDICALHIERARCHY_LEAF_LEVEL_V_5_0_LC_1 m
inner join
(select synid
from SYNID_PROFILE_MAP_5
where upper(map_type) = upper('primary')
and upper(map_to_profile) = upper('Diagnostic Tests &…
SAJAL MATHUR
- 67
- 1
- 2
- 15
0
votes
2 answers
SQL Query : should return Single Record if Search Condition met, otherwise return Multiple Records
I have table with Billions of Records, Table structure is like :
ID NUMBER PRIMARY KEY,
MY_SEARCH_COLUMN NUMBER,
MY_SEARCH_COLUMN will have Numeric value upto 15 Digit in length.
What I want is, if any specific record is matched, I will have to get…
124
- 2,757
- 26
- 37
0
votes
1 answer
Oracle Tuning Inline View with group by
I have an overly complex query that contains an inline view with a group by that I wish to try to remove for performance reasons, but I can't seem to think of a way to do so. An overly simplified representation of this query would be:
Select…
Trev
- 23
- 2
0
votes
1 answer
Tune SQL Server query
My goal is to purge data in chunks and was thinking to loop through ID_COl value after putting it into #temptable .
DECLARE @PD datetime = DATEADD(day, -5, GETUTCDATE());
DELETE GP
FROM dbo.Table_A N
INNER JOIN dbo.Table_BL GP
ON…
kibfais
- 3
- 1
0
votes
2 answers
Avoiding multiple repetitive sub queries that are being used to derive columns in the select
I have a view that consists of multiple sub queries that are used to derive columns in the select list (for keeping it simple I didn't specify all the sub queries). My question here is that is it totally okay to write such a query with so many sub…
Feivel
- 111
- 4
0
votes
1 answer
Oracle EBS expression on indexed column prevents use of index
Oracle database 11.1.0.7 and EBS Applications 11.5.10.
I have a poorly performing custom view, which includes Oracle EBS base view APPS.RA_CUSTOMER_TRX. This base view is against base table RA_CUSTOMER_TRX_ALL as shown below.
FROM…
Joe
- 193
- 3
- 4
- 14
0
votes
1 answer
deleting 800 million orphan records from a table containing 1.5 billion records in oracle
My question is about deleting orphan records in oracle. I have table A in oracle 11.2 database which has 1.4 billion total records with 800 million orphan records. I identified the 800 million orphan records in table A. Created the table B with just…
notexpert
- 11
- 2
0
votes
2 answers
Are sql tuning ways always same for different DB engine？
I used Oracle for the half past year and learned some tricks of sql tuning,but now our DB is moving to greenplum and the project manager suggest us to change some of the codes that writted in Oracle sql for their efficiency or grammar.
I am curious…
Robinson
- 45
- 1
- 8
0
votes
2 answers
Adding Index To A Column Having Flag Values
I am a novice in tuning oracle queries thus need help.
If I have a sql query like:
select a.ID,a.name.....
from a,b,c
where a.id=b.id
and ....
and b.flag='Y';
then will adding index to the FLAG column of table b help to tune the query…
user6157109
- 63
- 1
- 1
- 5
0
votes
2 answers
Can converting a SQL query to PL/SQL improve performance in Oracle 12c?
I have been given an 800 lines SQL Query which is taking around 20 hours to fetch around 400 million records.
There are 13 tables which are partitioned by month.
The tables have records ranging from 10k to 400 million in each partition.
The tables…
Oradev
- 1
0
votes
3 answers
Tuning a query that selects the latest rows based on the date
Edited: Sorry I forgot to add an important condition value1 - value 2 <> 0;. Without it result have ~111k records. And as I rewrite query using CTE go get set of code(s) in max date to join with main table it seems index can't help my case, i…
Pham X. Bach
- 5,284
- 4
- 28
- 42
0
votes
0 answers
MySQL - Varied query runtime for 'IN' Clause
I have a table of about million rows and I am trying to filter out rows based on a particular column using an IN clause.
Sample data:
status
---------
'XXX-01'
'XXX-02'
'XXX-06'
'XXX-010'
'XX-XXX-027'
'XX-XXX-030'
Query:
The following query took…
Sathiya Sarathi
- 429
- 6
- 23