Indexing and minimizing reading of blocks
I am struggling a lot with this problem.
Given two relations:
A = (aid, a, t, p, z) and B(bid, x, aid, y). With the following dimensions, A has 10000 pages 25 entries/page and B has 1000 pages, 40 entries/page.
The database has the following indexes:
unclustestered B+ index on B.y
hash indexes on all table keys.
Consider the DBMS has these 2 join algorithms possible:
join with nested loop
join with index
How to find a plan which minimizes blocks reads, for following query:
select A.t, B.y
from A, B where B.aid = A.aid and
B.y > '0'
My approach is to utilize the fact that there are hash indexes on all table keys and to use
join with index in that case we have the pages read as :
10000 pages + 1000 * 40 * 1,2 = 58000 pages read.
Is it safe to assume this will be most optimal way?
Because join with nested loop seems naturally to be the more demanding way to achieve this. I hope some DB guru will offer some ideas.
Thanks.
database
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
add a comment |
I am struggling a lot with this problem.
Given two relations:
A = (aid, a, t, p, z) and B(bid, x, aid, y). With the following dimensions, A has 10000 pages 25 entries/page and B has 1000 pages, 40 entries/page.
The database has the following indexes:
unclustestered B+ index on B.y
hash indexes on all table keys.
Consider the DBMS has these 2 join algorithms possible:
join with nested loop
join with index
How to find a plan which minimizes blocks reads, for following query:
select A.t, B.y
from A, B where B.aid = A.aid and
B.y > '0'
My approach is to utilize the fact that there are hash indexes on all table keys and to use
join with index in that case we have the pages read as :
10000 pages + 1000 * 40 * 1,2 = 58000 pages read.
Is it safe to assume this will be most optimal way?
Because join with nested loop seems naturally to be the more demanding way to achieve this. I hope some DB guru will offer some ideas.
Thanks.
database
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
add a comment |
I am struggling a lot with this problem.
Given two relations:
A = (aid, a, t, p, z) and B(bid, x, aid, y). With the following dimensions, A has 10000 pages 25 entries/page and B has 1000 pages, 40 entries/page.
The database has the following indexes:
unclustestered B+ index on B.y
hash indexes on all table keys.
Consider the DBMS has these 2 join algorithms possible:
join with nested loop
join with index
How to find a plan which minimizes blocks reads, for following query:
select A.t, B.y
from A, B where B.aid = A.aid and
B.y > '0'
My approach is to utilize the fact that there are hash indexes on all table keys and to use
join with index in that case we have the pages read as :
10000 pages + 1000 * 40 * 1,2 = 58000 pages read.
Is it safe to assume this will be most optimal way?
Because join with nested loop seems naturally to be the more demanding way to achieve this. I hope some DB guru will offer some ideas.
Thanks.
database
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
I am struggling a lot with this problem.
Given two relations:
A = (aid, a, t, p, z) and B(bid, x, aid, y). With the following dimensions, A has 10000 pages 25 entries/page and B has 1000 pages, 40 entries/page.
The database has the following indexes:
unclustestered B+ index on B.y
hash indexes on all table keys.
Consider the DBMS has these 2 join algorithms possible:
join with nested loop
join with index
How to find a plan which minimizes blocks reads, for following query:
select A.t, B.y
from A, B where B.aid = A.aid and
B.y > '0'
My approach is to utilize the fact that there are hash indexes on all table keys and to use
join with index in that case we have the pages read as :
10000 pages + 1000 * 40 * 1,2 = 58000 pages read.
Is it safe to assume this will be most optimal way?
Because join with nested loop seems naturally to be the more demanding way to achieve this. I hope some DB guru will offer some ideas.
Thanks.
database
database
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
asked Jan 20 at 14:14
mcdonald reversedmcdonald reversed
11
11
add a comment |
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
});
}
});
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%2f54277328%2findexing-and-minimizing-reading-of-blocks%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
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%2f54277328%2findexing-and-minimizing-reading-of-blocks%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
