Reaching to the effective performance level of the query
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have this below oracle query Please advise me how can I make it more efficient so that it performance will improve , let me know please if i i have miss anything in it as I am a new one ,any early help would be much appreciated
below is the Plan for the query , Please advise how can i improve it
Plan hash value: 44516869
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1099 | 39564 | 3770 (1)| 00:00:46 |
|* 1 | HASH JOIN | | 1099 | 39564 | 3770 (1)| 00:00:46 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1095 | 32850 | 3345 (1)| 00:00:41 |
| 4 | TABLE ACCESS FULL | BUSINESS_CONTACTS | 1666 | 14994 | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PERSON_PK | 1 | |
sql oracle
asked Nov 23 '18 at 17:28
ssssss
56111
add a comment |
0
I have this below oracle query Please advise me how can I make it more efficient so that it performance will improve , let me know please if i i have miss anything in it as I am a new one ,any early help would be much appreciated
below is the Plan for the query , Please advise how can i improve it
Plan hash value: 44516869
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1099 | 39564 | 3770 (1)| 00:00:46 |
|* 1 | HASH JOIN | | 1099 | 39564 | 3770 (1)| 00:00:46 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1095 | 32850 | 3345 (1)| 00:00:41 |
| 4 | TABLE ACCESS FULL | BUSINESS_CONTACTS | 1666 | 14994 | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PERSON_PK | 1 | |
sql oracle
asked Nov 23 '18 at 17:28
ssssss
56111
1
Your outerPerson Pand innerPerson PRreferences are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regularJOIN, anyways). Side note: please don't use the comma-separatedFROMclause - always explicitly list outJOINs, and put (almost) all criteria into theONclause.
– Clockwork-Muse
Nov 23 '18 at 18:23
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09
add a comment |
0
0
0
I have this below oracle query Please advise me how can I make it more efficient so that it performance will improve , let me know please if i i have miss anything in it as I am a new one ,any early help would be much appreciated
below is the Plan for the query , Please advise how can i improve it
Plan hash value: 44516869
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1099 | 39564 | 3770 (1)| 00:00:46 |
|* 1 | HASH JOIN | | 1099 | 39564 | 3770 (1)| 00:00:46 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1095 | 32850 | 3345 (1)| 00:00:41 |
| 4 | TABLE ACCESS FULL | BUSINESS_CONTACTS | 1666 | 14994 | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PERSON_PK | 1 | |
sql oracle
asked Nov 23 '18 at 17:28
ssssss
56111
I have this below oracle query Please advise me how can I make it more efficient so that it performance will improve , let me know please if i i have miss anything in it as I am a new one ,any early help would be much appreciated
below is the Plan for the query , Please advise how can i improve it
Plan hash value: 44516869
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1099 | 39564 | 3770 (1)| 00:00:46 |
|* 1 | HASH JOIN | | 1099 | 39564 | 3770 (1)| 00:00:46 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1095 | 32850 | 3345 (1)| 00:00:41 |
| 4 | TABLE ACCESS FULL | BUSINESS_CONTACTS | 1666 | 14994 | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PERSON_PK | 1 | |
sql oracle
sql oracle
asked Nov 23 '18 at 17:28
ssssss
56111
asked Nov 23 '18 at 17:28
ssssss
56111
edited Nov 24 '18 at 2:30
sss
asked Nov 23 '18 at 17:28
ssssss
56111
asked Nov 23 '18 at 17:28
ssssss
56111
asked Nov 23 '18 at 17:28
ssssss
56111
56111
1
Your outerPerson Pand innerPerson PRreferences are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regularJOIN, anyways). Side note: please don't use the comma-separatedFROMclause - always explicitly list outJOINs, and put (almost) all criteria into theONclause.
– Clockwork-Muse
Nov 23 '18 at 18:23
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09
add a comment |
1
Your outerPerson Pand innerPerson PRreferences are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regularJOIN, anyways). Side note: please don't use the comma-separatedFROMclause - always explicitly list outJOINs, and put (almost) all criteria into theONclause.
– Clockwork-Muse
Nov 23 '18 at 18:23
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09
1
1
Your outer
Person P and inner Person PR references are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regular JOIN, anyways). Side note: please don't use the comma-separated FROM clause - always explicitly list out JOINs, and put (almost) all criteria into the ON clause.– Clockwork-Muse
Nov 23 '18 at 18:23
Your outer
Person P and inner Person PR references are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regular JOIN, anyways). Side note: please don't use the comma-separated FROM clause - always explicitly list out JOINs, and put (almost) all criteria into the ON clause.– Clockwork-Muse
Nov 23 '18 at 18:23
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09
add a comment |
1 Answer
1
active
oldest
votes
0
First, you can rewrite the query using proper, explicit, standard JOIN syntax and no subqueries. I believe the query is:
select p.business_name as Aggregator_Name,
pc.business_name as Child_Business_Name,
bc.IDV_STATUS
from business_contacts bc join
person p
on bc.person_id = p.person_id join
person pc
on pc.parent_person_id = p.person_id;
Then for this query you want indexes on person(person_id, business_name) and person(parent_person_id, business_name).
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
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
});
}
});
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%2f53450839%2freaching-to-the-effective-performance-level-of-the-query%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
0
First, you can rewrite the query using proper, explicit, standard JOIN syntax and no subqueries. I believe the query is:
select p.business_name as Aggregator_Name,
pc.business_name as Child_Business_Name,
bc.IDV_STATUS
from business_contacts bc join
person p
on bc.person_id = p.person_id join
person pc
on pc.parent_person_id = p.person_id;
Then for this query you want indexes on person(person_id, business_name) and person(parent_person_id, business_name).
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
add a comment |
0
First, you can rewrite the query using proper, explicit, standard JOIN syntax and no subqueries. I believe the query is:
select p.business_name as Aggregator_Name,
pc.business_name as Child_Business_Name,
bc.IDV_STATUS
from business_contacts bc join
person p
on bc.person_id = p.person_id join
person pc
on pc.parent_person_id = p.person_id;
Then for this query you want indexes on person(person_id, business_name) and person(parent_person_id, business_name).
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
add a comment |
0
0
0
First, you can rewrite the query using proper, explicit, standard JOIN syntax and no subqueries. I believe the query is:
select p.business_name as Aggregator_Name,
pc.business_name as Child_Business_Name,
bc.IDV_STATUS
from business_contacts bc join
person p
on bc.person_id = p.person_id join
person pc
on pc.parent_person_id = p.person_id;
Then for this query you want indexes on person(person_id, business_name) and person(parent_person_id, business_name).
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
First, you can rewrite the query using proper, explicit, standard JOIN syntax and no subqueries. I believe the query is:
select p.business_name as Aggregator_Name,
pc.business_name as Child_Business_Name,
bc.IDV_STATUS
from business_contacts bc join
person p
on bc.person_id = p.person_id join
person pc
on pc.parent_person_id = p.person_id;
Then for this query you want indexes on person(person_id, business_name) and person(parent_person_id, business_name).
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
answered Nov 24 '18 at 1:54
Gordon LinoffGordon Linoff
796k37318423
796k37318423
add a comment |
add a comment |
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%2f53450839%2freaching-to-the-effective-performance-level-of-the-query%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
Your outer
Person Pand innerPerson PRreferences are pointing to the same row, which probably isn't what you want. You should be able to remove one or both of the inner references (and convert the subquery to a regularJOIN, anyways). Side note: please don't use the comma-separatedFROMclause - always explicitly list outJOINs, and put (almost) all criteria into theONclause.– Clockwork-Muse
Nov 23 '18 at 18:23
@Clockwork-Muse i agree
– nikhil sugandh
Nov 23 '18 at 18:24
Does your business_contacts table have an index on person_id? If I read this right, you're doing 1095 full table scans of business_contacts.
– eaolson
Nov 23 '18 at 21:09
What query? Looks like you removed it and part of the execution plan in an edit, which makes the question a bit meaningless. Voting to close.
– William Robertson
Nov 24 '18 at 10:09