Improving the below oracle query to be more effective
I am a new learner to the oracle world of sql queries but still how I need to bring the data from two tables for which I have fetched the query , now please advise is the below query is correct or still I can make it more efficient , any suggestions to improve it are welcome , Here below is the my query
oracle
asked Nov 24 '18 at 17:12
ssssss
56110
add a comment |
I am a new learner to the oracle world of sql queries but still how I need to bring the data from two tables for which I have fetched the query , now please advise is the below query is correct or still I can make it more efficient , any suggestions to improve it are welcome , Here below is the my query
oracle
asked Nov 24 '18 at 17:12
ssssss
56110
1
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25
add a comment |
I am a new learner to the oracle world of sql queries but still how I need to bring the data from two tables for which I have fetched the query , now please advise is the below query is correct or still I can make it more efficient , any suggestions to improve it are welcome , Here below is the my query
oracle
asked Nov 24 '18 at 17:12
ssssss
56110
I am a new learner to the oracle world of sql queries but still how I need to bring the data from two tables for which I have fetched the query , now please advise is the below query is correct or still I can make it more efficient , any suggestions to improve it are welcome , Here below is the my query
oracle
oracle
asked Nov 24 '18 at 17:12
ssssss
56110
asked Nov 24 '18 at 17:12
ssssss
56110
edited Nov 25 '18 at 3:30
sss
asked Nov 24 '18 at 17:12
ssssss
56110
asked Nov 24 '18 at 17:12
ssssss
56110
asked Nov 24 '18 at 17:12
ssssss
56110
56110
1
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25
add a comment |
1
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25
1
1
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25
add a comment |
1 Answer
1
active
oldest
votes
You should not convert your date datatypes to character when doing the comparisons. This has two negative effects
- You effectively "hide" the statistics on those columns, which means
the optimizer may not make the best choice of execution plan - If you have indexes on the columns, you are preventing their use.
If your CREATED_ON and MODIFIED_ON columns do not store a time portion, then your predicates can be simply
BC.CREATED_ON = trunc(SYSDATE)
OR
BC.MODIFIED_ON = trunc(SYSDATE)
If your columns do include a time portion, then you should formulate your predicates as shown
BC.CREATED_ON >= trunc(sysdate) and BC.CREATED_ON < trunc(sysdate)+1
or
BC.MODIFIED_ON >= trunc(sysdate) and BC.MODIFIED_ON < trunc(sysdate)+1
What you are trying to do, is avoid having a function applied to the columns, TO_CHAR() in your question
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
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%2f53460548%2fimproving-the-below-oracle-query-to-be-more-effective%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
You should not convert your date datatypes to character when doing the comparisons. This has two negative effects
- You effectively "hide" the statistics on those columns, which means
the optimizer may not make the best choice of execution plan - If you have indexes on the columns, you are preventing their use.
If your CREATED_ON and MODIFIED_ON columns do not store a time portion, then your predicates can be simply
BC.CREATED_ON = trunc(SYSDATE)
OR
BC.MODIFIED_ON = trunc(SYSDATE)
If your columns do include a time portion, then you should formulate your predicates as shown
BC.CREATED_ON >= trunc(sysdate) and BC.CREATED_ON < trunc(sysdate)+1
or
BC.MODIFIED_ON >= trunc(sysdate) and BC.MODIFIED_ON < trunc(sysdate)+1
What you are trying to do, is avoid having a function applied to the columns, TO_CHAR() in your question
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
add a comment |
You should not convert your date datatypes to character when doing the comparisons. This has two negative effects
- You effectively "hide" the statistics on those columns, which means
the optimizer may not make the best choice of execution plan - If you have indexes on the columns, you are preventing their use.
If your CREATED_ON and MODIFIED_ON columns do not store a time portion, then your predicates can be simply
BC.CREATED_ON = trunc(SYSDATE)
OR
BC.MODIFIED_ON = trunc(SYSDATE)
If your columns do include a time portion, then you should formulate your predicates as shown
BC.CREATED_ON >= trunc(sysdate) and BC.CREATED_ON < trunc(sysdate)+1
or
BC.MODIFIED_ON >= trunc(sysdate) and BC.MODIFIED_ON < trunc(sysdate)+1
What you are trying to do, is avoid having a function applied to the columns, TO_CHAR() in your question
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
add a comment |
You should not convert your date datatypes to character when doing the comparisons. This has two negative effects
- You effectively "hide" the statistics on those columns, which means
the optimizer may not make the best choice of execution plan - If you have indexes on the columns, you are preventing their use.
If your CREATED_ON and MODIFIED_ON columns do not store a time portion, then your predicates can be simply
BC.CREATED_ON = trunc(SYSDATE)
OR
BC.MODIFIED_ON = trunc(SYSDATE)
If your columns do include a time portion, then you should formulate your predicates as shown
BC.CREATED_ON >= trunc(sysdate) and BC.CREATED_ON < trunc(sysdate)+1
or
BC.MODIFIED_ON >= trunc(sysdate) and BC.MODIFIED_ON < trunc(sysdate)+1
What you are trying to do, is avoid having a function applied to the columns, TO_CHAR() in your question
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
You should not convert your date datatypes to character when doing the comparisons. This has two negative effects
- You effectively "hide" the statistics on those columns, which means
the optimizer may not make the best choice of execution plan - If you have indexes on the columns, you are preventing their use.
If your CREATED_ON and MODIFIED_ON columns do not store a time portion, then your predicates can be simply
BC.CREATED_ON = trunc(SYSDATE)
OR
BC.MODIFIED_ON = trunc(SYSDATE)
If your columns do include a time portion, then you should formulate your predicates as shown
BC.CREATED_ON >= trunc(sysdate) and BC.CREATED_ON < trunc(sysdate)+1
or
BC.MODIFIED_ON >= trunc(sysdate) and BC.MODIFIED_ON < trunc(sysdate)+1
What you are trying to do, is avoid having a function applied to the columns, TO_CHAR() in your question
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
edited Nov 24 '18 at 21:38
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
answered Nov 24 '18 at 19:47
BobCBobC
2,9021613
2,9021613
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%2f53460548%2fimproving-the-below-oracle-query-to-be-more-effective%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
1
HOw would we know what "correct" is? How do you know it is not "efficient?" What indexes do you have on the table? Did you run an explain plan? This question is too broad. Can't really help without more details. Sorry
– OldProgrammer
Nov 24 '18 at 17:52
What query? Looks like you removed it in an edit, leaving the question meaningless. Please don't do that.
– William Robertson
Nov 25 '18 at 12:25