SQL avoid calling same function twice in select statement
I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.
I want to call function only once and copy its value to another column. Is it possible in oracle ?
SELECT ID
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK"
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
from table
where ID=condition;
oracle function select
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
add a comment |
I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.
I want to call function only once and copy its value to another column. Is it possible in oracle ?
SELECT ID
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK"
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
from table
where ID=condition;
oracle function select
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
One option to consider is to addRESULT_CACHEto the function.
– Jeffrey Kemp
Nov 27 '18 at 5:14
add a comment |
I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.
I want to call function only once and copy its value to another column. Is it possible in oracle ?
SELECT ID
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK"
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
from table
where ID=condition;
oracle function select
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
I have a select statement calling same function two times for returning two different columns, Since the same function is called twice, it's creating a performance issue.
I want to call function only once and copy its value to another column. Is it possible in oracle ?
SELECT ID
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "BLOCK"
,PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) "MASK"
from table
where ID=condition;
oracle function select
oracle function select
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
edited Nov 20 '18 at 7:13
user7294900
22.4k113360
22.4k113360
asked Nov 20 '18 at 6:50
raviravi
1,90522547
asked Nov 20 '18 at 6:50
raviravi
1,90522547
asked Nov 20 '18 at 6:50
raviravi
1,90522547
1,90522547
One option to consider is to addRESULT_CACHEto the function.
– Jeffrey Kemp
Nov 27 '18 at 5:14
add a comment |
One option to consider is to addRESULT_CACHEto the function.
– Jeffrey Kemp
Nov 27 '18 at 5:14
One option to consider is to add
RESULT_CACHE to the function.– Jeffrey Kemp
Nov 27 '18 at 5:14
One option to consider is to add
RESULT_CACHE to the function.– Jeffrey Kemp
Nov 27 '18 at 5:14
add a comment |
1 Answer
1
active
oldest
votes
You can use with clause to get data once
with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block
from table
where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
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%2f53387661%2fsql-avoid-calling-same-function-twice-in-select-statement%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 can use with clause to get data once
with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block
from table
where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
add a comment |
You can use with clause to get data once
with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block
from table
where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
add a comment |
You can use with clause to get data once
with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block
from table
where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
You can use with clause to get data once
with block as (
select PKGRESTFUNCTION.getBlock(table.ID, table.TYPE) as block
from table
where ID=condition;
) select ID, block.block as "BLOCK" , block.block as "MASK" from block
The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
answered Nov 20 '18 at 6:54
user7294900user7294900
22.4k113360
22.4k113360
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%2f53387661%2fsql-avoid-calling-same-function-twice-in-select-statement%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
One option to consider is to add
RESULT_CACHEto the function.– Jeffrey Kemp
Nov 27 '18 at 5:14