Setting expiration time when caching view
I want to cache the result from a view in Oracle. My view is declared as:
CREATE VIEW SOME_VIEW AS
SELECT
/*+ RESULT_CACHE */
u.name
c.info
FROM
Users u
LEFT OUT JOIN Contacts c ON c.user_id = u.id
Now I want to explicitly set the cache expiration time of the for this view. If I have understood it right, the expiration is applied to the cache globaly.
Is it possible to set the expiration time per query?
oracle caching oracle12c
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
add a comment |
I want to cache the result from a view in Oracle. My view is declared as:
CREATE VIEW SOME_VIEW AS
SELECT
/*+ RESULT_CACHE */
u.name
c.info
FROM
Users u
LEFT OUT JOIN Contacts c ON c.user_id = u.id
Now I want to explicitly set the cache expiration time of the for this view. If I have understood it right, the expiration is applied to the cache globaly.
Is it possible to set the expiration time per query?
oracle caching oracle12c
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
4
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
6
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
2
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20
add a comment |
I want to cache the result from a view in Oracle. My view is declared as:
CREATE VIEW SOME_VIEW AS
SELECT
/*+ RESULT_CACHE */
u.name
c.info
FROM
Users u
LEFT OUT JOIN Contacts c ON c.user_id = u.id
Now I want to explicitly set the cache expiration time of the for this view. If I have understood it right, the expiration is applied to the cache globaly.
Is it possible to set the expiration time per query?
oracle caching oracle12c
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
I want to cache the result from a view in Oracle. My view is declared as:
CREATE VIEW SOME_VIEW AS
SELECT
/*+ RESULT_CACHE */
u.name
c.info
FROM
Users u
LEFT OUT JOIN Contacts c ON c.user_id = u.id
Now I want to explicitly set the cache expiration time of the for this view. If I have understood it right, the expiration is applied to the cache globaly.
Is it possible to set the expiration time per query?
oracle caching oracle12c
oracle caching oracle12c
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
asked Jan 3 '17 at 8:39
SchaliasosSchaliasos
7,53533974
7,53533974
4
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
6
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
2
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20
add a comment |
4
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
6
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
2
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20
4
4
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
6
6
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
2
2
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20
add a comment |
1 Answer
1
active
oldest
votes
Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) that select from tables whose data doesn't change very often.
Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:
update Contacts c
set c.user_id = c.user_id + 0
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
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%2f41439436%2fsetting-expiration-time-when-caching-view%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
Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) that select from tables whose data doesn't change very often.
Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:
update Contacts c
set c.user_id = c.user_id + 0
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
add a comment |
Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) that select from tables whose data doesn't change very often.
Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:
update Contacts c
set c.user_id = c.user_id + 0
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
add a comment |
Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) that select from tables whose data doesn't change very often.
Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:
update Contacts c
set c.user_id = c.user_id + 0
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
Result Set Caching stashes the result set of a query in-memory. Any query which returns this result set (or a subset of it) hits the cache instead of the database. The lifespan of the cache is from the first time the source query is executed until the underlying tables are changed through DML. This makes the /*+ RESULT_CACHE */ hint ideal for queries which are expensive to execute (or executed very often) that select from tables whose data doesn't change very often.
Consequently there isn't any mechanism for us to invalidate the result set cache: the database manages it for us invisibly. Nevertheless, if you really wanted to invalidate the cache periodically (your database is performing too well or something) maybe you could schedule a job to execute a meaningless update on one of the tables the view relies on:
update Contacts c
set c.user_id = c.user_id + 0
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
answered Dec 30 '18 at 17:27
APCAPC
118k15118229
118k15118229
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%2f41439436%2fsetting-expiration-time-when-caching-view%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
4
Maybe you want to be looking at materialized views if you want this level of control?
– Alex Poole
Jan 3 '17 at 9:17
6
Usually Result Cache expires automatically as soon as one of the underlying tables (USERS and CONTACTS in your case) is updated. Otherwise you select from Result Cache which makes sense.
– Wernfried Domscheit
Jan 3 '17 at 9:31
2
Your link has nothing to do with the Oracle database. It's about a product called "ATG Repository". The Oracle SQL reference is here: docs.oracle.com/database/121/SQLRF/toc.htm The result cache is documented in the performance manual: docs.oracle.com/database/121/TGDBA/… There is a section specifically for usage of the result cache in views: docs.oracle.com/database/121/TGDBA/…
– a_horse_with_no_name
Jan 3 '17 at 11:20