Mixing
Can I use conditional values in conjunction with Bitmap Join Indexes?
I am looking into adding Bitmap join indexes on an Oracle 11g database for a table that has approximately 1 billion rows. It is only being updated in a weekend process once a month. My fact table has 80 some odd columns on it, and is joined to many dimension tables. Most columns have low cardinality compared to the number of rows (all less than .001%).
Now, my question is, if I built a bitmap join index like the following (field1 is PK on both):
CREATE BITMAP INDEX testidx_1
on fact(dimension.field2)
from fact, dimension
where fact.field1 = dimension.field1;
I understand if I then do
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1;
that it will use the bitmap join index optimally.
BUT, say I put in something like this:
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1
and dimension.field2 = 'somevalue'
will it then NOT use the bitmap join index? Would I have to create a BJI for each conditional value I wanted to select?
I used this as my reference: https://logicalread.com/oracle-11g-best-uses-bitmap-join-index-mc02/#.XCz2oVxKiUl
oracle join indexing bitmap
asked Jan 2 at 17:48
RichRich
114
add a comment |
I am looking into adding Bitmap join indexes on an Oracle 11g database for a table that has approximately 1 billion rows. It is only being updated in a weekend process once a month. My fact table has 80 some odd columns on it, and is joined to many dimension tables. Most columns have low cardinality compared to the number of rows (all less than .001%).
Now, my question is, if I built a bitmap join index like the following (field1 is PK on both):
CREATE BITMAP INDEX testidx_1
on fact(dimension.field2)
from fact, dimension
where fact.field1 = dimension.field1;
I understand if I then do
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1;
that it will use the bitmap join index optimally.
BUT, say I put in something like this:
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1
and dimension.field2 = 'somevalue'
will it then NOT use the bitmap join index? Would I have to create a BJI for each conditional value I wanted to select?
I used this as my reference: https://logicalread.com/oracle-11g-best-uses-bitmap-join-index-mc02/#.XCz2oVxKiUl
oracle join indexing bitmap
asked Jan 2 at 17:48
RichRich
114
add a comment |
I am looking into adding Bitmap join indexes on an Oracle 11g database for a table that has approximately 1 billion rows. It is only being updated in a weekend process once a month. My fact table has 80 some odd columns on it, and is joined to many dimension tables. Most columns have low cardinality compared to the number of rows (all less than .001%).
Now, my question is, if I built a bitmap join index like the following (field1 is PK on both):
CREATE BITMAP INDEX testidx_1
on fact(dimension.field2)
from fact, dimension
where fact.field1 = dimension.field1;
I understand if I then do
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1;
that it will use the bitmap join index optimally.
BUT, say I put in something like this:
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1
and dimension.field2 = 'somevalue'
will it then NOT use the bitmap join index? Would I have to create a BJI for each conditional value I wanted to select?
I used this as my reference: https://logicalread.com/oracle-11g-best-uses-bitmap-join-index-mc02/#.XCz2oVxKiUl
oracle join indexing bitmap
asked Jan 2 at 17:48
RichRich
114
I am looking into adding Bitmap join indexes on an Oracle 11g database for a table that has approximately 1 billion rows. It is only being updated in a weekend process once a month. My fact table has 80 some odd columns on it, and is joined to many dimension tables. Most columns have low cardinality compared to the number of rows (all less than .001%).
Now, my question is, if I built a bitmap join index like the following (field1 is PK on both):
CREATE BITMAP INDEX testidx_1
on fact(dimension.field2)
from fact, dimension
where fact.field1 = dimension.field1;
I understand if I then do
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1;
that it will use the bitmap join index optimally.
BUT, say I put in something like this:
select fact.fieldx, fact2.fieldy, dimension.field2
from fact, dimension
where fact.field1 = dimension.field1
and dimension.field2 = 'somevalue'
will it then NOT use the bitmap join index? Would I have to create a BJI for each conditional value I wanted to select?
I used this as my reference: https://logicalread.com/oracle-11g-best-uses-bitmap-join-index-mc02/#.XCz2oVxKiUl
oracle join indexing bitmap
oracle join indexing bitmap
asked Jan 2 at 17:48
RichRich
114
asked Jan 2 at 17:48
RichRich
114
asked Jan 2 at 17:48
RichRich
114
asked Jan 2 at 17:48
RichRich
114
asked Jan 2 at 17:48
RichRich
114
114
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%2f54010892%2fcan-i-use-conditional-values-in-conjunction-with-bitmap-join-indexes%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%2f54010892%2fcan-i-use-conditional-values-in-conjunction-with-bitmap-join-indexes%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
