Optimizing a view on multiple big tables
0
I have many big tables with lidar data in oracle and I want to creata a view so my users can query them all in one place.
I made a view like this:
CREATE OR REPLACE VIEW SCHEMA.BIGVIEW AS
SELECT 1 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE1
WHERE CONDITIONS_TABLE1
UNION ALL
SELECT 2 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE2
WHERE CONDITIONS_TABLE2
UNION ALL
SELECT 3 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE3
WHERE CONDITIONS_TABLE3
UNION ALL
SELECT 4 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE4
WHERE CONDITIONS_TABLE4
--(...ETC, 22 TABLES LIKE THIS)
;
And it works but it's quite slow...
So I have 22 LIDAR tables/views with the same structure, each with their own WHERE conditions and many with millions of rows.
I wonder if there is a way to optimize this view (other than materialize it)?
Thanks!
sql oracle view
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
add a comment |
0
I have many big tables with lidar data in oracle and I want to creata a view so my users can query them all in one place.
I made a view like this:
CREATE OR REPLACE VIEW SCHEMA.BIGVIEW AS
SELECT 1 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE1
WHERE CONDITIONS_TABLE1
UNION ALL
SELECT 2 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE2
WHERE CONDITIONS_TABLE2
UNION ALL
SELECT 3 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE3
WHERE CONDITIONS_TABLE3
UNION ALL
SELECT 4 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE4
WHERE CONDITIONS_TABLE4
--(...ETC, 22 TABLES LIKE THIS)
;
And it works but it's quite slow...
So I have 22 LIDAR tables/views with the same structure, each with their own WHERE conditions and many with millions of rows.
I wonder if there is a way to optimize this view (other than materialize it)?
Thanks!
sql oracle view
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30
add a comment |
0
0
0
I have many big tables with lidar data in oracle and I want to creata a view so my users can query them all in one place.
I made a view like this:
CREATE OR REPLACE VIEW SCHEMA.BIGVIEW AS
SELECT 1 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE1
WHERE CONDITIONS_TABLE1
UNION ALL
SELECT 2 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE2
WHERE CONDITIONS_TABLE2
UNION ALL
SELECT 3 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE3
WHERE CONDITIONS_TABLE3
UNION ALL
SELECT 4 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE4
WHERE CONDITIONS_TABLE4
--(...ETC, 22 TABLES LIKE THIS)
;
And it works but it's quite slow...
So I have 22 LIDAR tables/views with the same structure, each with their own WHERE conditions and many with millions of rows.
I wonder if there is a way to optimize this view (other than materialize it)?
Thanks!
sql oracle view
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
I have many big tables with lidar data in oracle and I want to creata a view so my users can query them all in one place.
I made a view like this:
CREATE OR REPLACE VIEW SCHEMA.BIGVIEW AS
SELECT 1 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE1
WHERE CONDITIONS_TABLE1
UNION ALL
SELECT 2 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE2
WHERE CONDITIONS_TABLE2
UNION ALL
SELECT 3 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE3
WHERE CONDITIONS_TABLE3
UNION ALL
SELECT 4 as JD_ID, PT_ID, PT_GEOM, Z_NAVD88
FROM SCHEMA.V_TABLE4
WHERE CONDITIONS_TABLE4
--(...ETC, 22 TABLES LIKE THIS)
;
And it works but it's quite slow...
So I have 22 LIDAR tables/views with the same structure, each with their own WHERE conditions and many with millions of rows.
I wonder if there is a way to optimize this view (other than materialize it)?
Thanks!
sql oracle view
sql oracle view
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
edited Nov 15 '18 at 16:08
Akarius
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
asked Nov 14 '18 at 17:06
AkariusAkarius
34616
34616
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30
add a comment |
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53305397%2foptimizing-a-view-on-multiple-big-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53305397%2foptimizing-a-view-on-multiple-big-tables%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
No doubt, but you haven't provided enough information because your view is built on more views.
– Gordon Linoff
Nov 14 '18 at 20:14
a first question would be: are your V_TABLE% objects tables or again views? And maybe more important: where is the time spend: if you have a selective condition and fitting Indexes then the access should be efficient for every UNION ALL block. To determine which part of the view access is slow you could generate an execution plan with rowsource statistics (for example by the use of an gather_plan_statistics hint)
– Martin Preiss
Nov 14 '18 at 20:18
Thanks for your answer.
– Akarius
Nov 15 '18 at 14:29
About the 22 V_TABLE%, some are table, some are views on other tables but all with the same structure. We do this because we don't want tables with more than 20M rows. Of course, the fields in the where conditions are not ALL indexed and I know this is one of the reason why it's slow...but I could add one field, by example IN_BIGVIEW, in each table and set it to 1 if it respects all conditions that are in the WHERE clause then index it. Would it be more efficient?
– Akarius
Nov 15 '18 at 14:30
Also, the view as it is right now, is it ok? Is it a way to create it more efficiently, independently of index or other performance parameters?
– Akarius
Nov 15 '18 at 14:30