how does oracle executes where clause on a view
0
I have a doubt on how oracle executes where clause on a view. Can someone please clarify this doubt. For example, I have a view created using below mentioned SQL.
create view simpleView as
select a.col1,b.col
from tableA a, tableB b
where a.col1 = b.col1
and a.col2 = b.col2;
And there is an index created on tableA(col1).
I will execute the SQL as
select * from simpleView where col1 = 'literalValue';
My doubt here is,
- Will the oracle will execute inner join first and then filter the records based on the literalValue?
- Will the execution uses index for better performance?
sql oracle indexing view
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
add a comment |
0
I have a doubt on how oracle executes where clause on a view. Can someone please clarify this doubt. For example, I have a view created using below mentioned SQL.
create view simpleView as
select a.col1,b.col
from tableA a, tableB b
where a.col1 = b.col1
and a.col2 = b.col2;
And there is an index created on tableA(col1).
I will execute the SQL as
select * from simpleView where col1 = 'literalValue';
My doubt here is,
- Will the oracle will execute inner join first and then filter the records based on the literalValue?
- Will the execution uses index for better performance?
sql oracle indexing view
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
1
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
2
(Unrelated) Tip of today: Switch to modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 14 '18 at 10:34
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51
add a comment |
0
0
0
I have a doubt on how oracle executes where clause on a view. Can someone please clarify this doubt. For example, I have a view created using below mentioned SQL.
create view simpleView as
select a.col1,b.col
from tableA a, tableB b
where a.col1 = b.col1
and a.col2 = b.col2;
And there is an index created on tableA(col1).
I will execute the SQL as
select * from simpleView where col1 = 'literalValue';
My doubt here is,
- Will the oracle will execute inner join first and then filter the records based on the literalValue?
- Will the execution uses index for better performance?
sql oracle indexing view
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
I have a doubt on how oracle executes where clause on a view. Can someone please clarify this doubt. For example, I have a view created using below mentioned SQL.
create view simpleView as
select a.col1,b.col
from tableA a, tableB b
where a.col1 = b.col1
and a.col2 = b.col2;
And there is an index created on tableA(col1).
I will execute the SQL as
select * from simpleView where col1 = 'literalValue';
My doubt here is,
- Will the oracle will execute inner join first and then filter the records based on the literalValue?
- Will the execution uses index for better performance?
sql oracle indexing view
sql oracle indexing view
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
asked Nov 14 '18 at 10:20
Sunil KumarSunil Kumar
6
6
1
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
2
(Unrelated) Tip of today: Switch to modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 14 '18 at 10:34
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51
add a comment |
1
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
2
(Unrelated) Tip of today: Switch to modern, explicitJOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.
– jarlh
Nov 14 '18 at 10:34
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51
1
1
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
2
2
(Unrelated) Tip of today: Switch to modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 14 '18 at 10:34
(Unrelated) Tip of today: Switch to modern, explicit
JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 14 '18 at 10:34
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51
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%2f53297854%2fhow-does-oracle-executes-where-clause-on-a-view%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%2f53297854%2fhow-does-oracle-executes-where-clause-on-a-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
1
If you want to understand how it executes queries, you need to learn how to generate and read Execution plans. That'll be far more productive and accurate in the long run than posting individual queries on SO and asking other people to speculate on what the optimizer will do in each case.
– Damien_The_Unbeliever
Nov 14 '18 at 10:24
The execution order may change from time to time, depending on current table data.
– jarlh
Nov 14 '18 at 10:27
And if this is just a speculative query rather than based on a real DB then the only possible answers are "maybe" and "maybe".
– Damien_The_Unbeliever
Nov 14 '18 at 10:30
2
(Unrelated) Tip of today: Switch to modern, explicit
JOINsyntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.– jarlh
Nov 14 '18 at 10:34
@Damien_The_Unbeliever, you forgot, "It depends" :)
– BobC
Nov 16 '18 at 1:51