Optimizing query for Oracle
I want to tune the query below which runs in a loop and values for minprice and maxprice are dynamically substitued.
select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10
so in loop there will be multiple query with different values
select *
from product
where name = 'a'
and minprice >= 40
and maxprice <=70
select *
from product
where name = 'a'
and minprice >= 50
and maxprice <=60
One way is to write a stored procedure and do all the looping for different prices at database level.
Is there any other way to tune so that there will be single query with multiple dynamic values
sql oracle plsql
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
add a comment |
I want to tune the query below which runs in a loop and values for minprice and maxprice are dynamically substitued.
select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10
so in loop there will be multiple query with different values
select *
from product
where name = 'a'
and minprice >= 40
and maxprice <=70
select *
from product
where name = 'a'
and minprice >= 50
and maxprice <=60
One way is to write a stored procedure and do all the looping for different prices at database level.
Is there any other way to tune so that there will be single query with multiple dynamic values
sql oracle plsql
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
2
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09
add a comment |
I want to tune the query below which runs in a loop and values for minprice and maxprice are dynamically substitued.
select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10
so in loop there will be multiple query with different values
select *
from product
where name = 'a'
and minprice >= 40
and maxprice <=70
select *
from product
where name = 'a'
and minprice >= 50
and maxprice <=60
One way is to write a stored procedure and do all the looping for different prices at database level.
Is there any other way to tune so that there will be single query with multiple dynamic values
sql oracle plsql
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
I want to tune the query below which runs in a loop and values for minprice and maxprice are dynamically substitued.
select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10
so in loop there will be multiple query with different values
select *
from product
where name = 'a'
and minprice >= 40
and maxprice <=70
select *
from product
where name = 'a'
and minprice >= 50
and maxprice <=60
One way is to write a stored procedure and do all the looping for different prices at database level.
Is there any other way to tune so that there will be single query with multiple dynamic values
sql oracle plsql
sql oracle plsql
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
edited Nov 23 '18 at 13:34
a_horse_with_no_name
307k46468567
307k46468567
asked Nov 23 '18 at 12:58
user3227660user3227660
12
asked Nov 23 '18 at 12:58
user3227660user3227660
12
asked Nov 23 '18 at 12:58
user3227660user3227660
12
12
2
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09
add a comment |
2
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09
2
2
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09
add a comment |
1 Answer
1
active
oldest
votes
SQL is a set oriented langauge, it works best when there are no loops, as it gives the optimizer the best possible way to achieve you end results.
If currently to you are doing
for i in (select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10 )
loop
--inserting/updating/deleting
insert into another_table values(1.product_id,i.name);
end loop;
change that to
insert
into another_table
select producr_id,name
from product
where name = 'a'
and minprice >= 0
and maxprice <=60;
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447169%2foptimizing-query-for-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
SQL is a set oriented langauge, it works best when there are no loops, as it gives the optimizer the best possible way to achieve you end results.
If currently to you are doing
for i in (select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10 )
loop
--inserting/updating/deleting
insert into another_table values(1.product_id,i.name);
end loop;
change that to
insert
into another_table
select producr_id,name
from product
where name = 'a'
and minprice >= 0
and maxprice <=60;
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
add a comment |
SQL is a set oriented langauge, it works best when there are no loops, as it gives the optimizer the best possible way to achieve you end results.
If currently to you are doing
for i in (select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10 )
loop
--inserting/updating/deleting
insert into another_table values(1.product_id,i.name);
end loop;
change that to
insert
into another_table
select producr_id,name
from product
where name = 'a'
and minprice >= 0
and maxprice <=60;
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
add a comment |
SQL is a set oriented langauge, it works best when there are no loops, as it gives the optimizer the best possible way to achieve you end results.
If currently to you are doing
for i in (select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10 )
loop
--inserting/updating/deleting
insert into another_table values(1.product_id,i.name);
end loop;
change that to
insert
into another_table
select producr_id,name
from product
where name = 'a'
and minprice >= 0
and maxprice <=60;
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
SQL is a set oriented langauge, it works best when there are no loops, as it gives the optimizer the best possible way to achieve you end results.
If currently to you are doing
for i in (select *
from product
where name = 'a'
and minprice >= 0
and maxprice <=10 )
loop
--inserting/updating/deleting
insert into another_table values(1.product_id,i.name);
end loop;
change that to
insert
into another_table
select producr_id,name
from product
where name = 'a'
and minprice >= 0
and maxprice <=60;
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
answered Nov 23 '18 at 14:08
George JosephGeorge Joseph
1,590510
1,590510
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53447169%2foptimizing-query-for-oracle%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Show us the complete procedure (edit your question by clicking on the edit link below it, do not post code in comments)
– a_horse_with_no_name
Nov 23 '18 at 14:09