How does table partition and mapping work?
I have a database with one critical table which is widely used and queried. The lifetime of the data in this table is divided in two stages: Table1 and Table1_Hist, this way once the User has finished his work then the record is passed from Table1 to Table1_Hist for consultation and reports.
The structure of Table1 is
ID (Long) KEY,
Priority (INT)
val1 (VARBINARY(XXXX))
val2 (VARNINARY(XXXX))
This table is filled with millions of records per month and the records passed to Table1_Hist are made in no specific order, this means that User1 may insert a record in Table1 today and finish working with it today too, but User2 may insert a similar record in Table1 and finish working with it the next week or month or in 3 months.
The issue arises when the Table1_Hist starts to grow to the point of affecting the performance of the queries to this table. I had in mind to split this table in Table1_Hist_1, Table1_Hist_2... Table1_Hist_n and create an ID table Map where I can register the range of IDs stored on each table. For example, I may have a map saying that in Table1_Hist_1 are stored the IDs from 1 to 10M and so on, but as I said before, there is no order in time in which the records are inserted in these historical tables, hence I may have a Map from 1 to 10 pointing to table Table1_Hist_1 and also have a record ID=3 in Table1_Hist_2, just because the ID=3 was finished 2 months later and stored in the following table.
So, anyone knows about an efficient approach to split a table in multiple tables with their respective mapping?
database database-design query-performance
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
add a comment |
I have a database with one critical table which is widely used and queried. The lifetime of the data in this table is divided in two stages: Table1 and Table1_Hist, this way once the User has finished his work then the record is passed from Table1 to Table1_Hist for consultation and reports.
The structure of Table1 is
ID (Long) KEY,
Priority (INT)
val1 (VARBINARY(XXXX))
val2 (VARNINARY(XXXX))
This table is filled with millions of records per month and the records passed to Table1_Hist are made in no specific order, this means that User1 may insert a record in Table1 today and finish working with it today too, but User2 may insert a similar record in Table1 and finish working with it the next week or month or in 3 months.
The issue arises when the Table1_Hist starts to grow to the point of affecting the performance of the queries to this table. I had in mind to split this table in Table1_Hist_1, Table1_Hist_2... Table1_Hist_n and create an ID table Map where I can register the range of IDs stored on each table. For example, I may have a map saying that in Table1_Hist_1 are stored the IDs from 1 to 10M and so on, but as I said before, there is no order in time in which the records are inserted in these historical tables, hence I may have a Map from 1 to 10 pointing to table Table1_Hist_1 and also have a record ID=3 in Table1_Hist_2, just because the ID=3 was finished 2 months later and stored in the following table.
So, anyone knows about an efficient approach to split a table in multiple tables with their respective mapping?
database database-design query-performance
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
1
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54
add a comment |
I have a database with one critical table which is widely used and queried. The lifetime of the data in this table is divided in two stages: Table1 and Table1_Hist, this way once the User has finished his work then the record is passed from Table1 to Table1_Hist for consultation and reports.
The structure of Table1 is
ID (Long) KEY,
Priority (INT)
val1 (VARBINARY(XXXX))
val2 (VARNINARY(XXXX))
This table is filled with millions of records per month and the records passed to Table1_Hist are made in no specific order, this means that User1 may insert a record in Table1 today and finish working with it today too, but User2 may insert a similar record in Table1 and finish working with it the next week or month or in 3 months.
The issue arises when the Table1_Hist starts to grow to the point of affecting the performance of the queries to this table. I had in mind to split this table in Table1_Hist_1, Table1_Hist_2... Table1_Hist_n and create an ID table Map where I can register the range of IDs stored on each table. For example, I may have a map saying that in Table1_Hist_1 are stored the IDs from 1 to 10M and so on, but as I said before, there is no order in time in which the records are inserted in these historical tables, hence I may have a Map from 1 to 10 pointing to table Table1_Hist_1 and also have a record ID=3 in Table1_Hist_2, just because the ID=3 was finished 2 months later and stored in the following table.
So, anyone knows about an efficient approach to split a table in multiple tables with their respective mapping?
database database-design query-performance
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
I have a database with one critical table which is widely used and queried. The lifetime of the data in this table is divided in two stages: Table1 and Table1_Hist, this way once the User has finished his work then the record is passed from Table1 to Table1_Hist for consultation and reports.
The structure of Table1 is
ID (Long) KEY,
Priority (INT)
val1 (VARBINARY(XXXX))
val2 (VARNINARY(XXXX))
This table is filled with millions of records per month and the records passed to Table1_Hist are made in no specific order, this means that User1 may insert a record in Table1 today and finish working with it today too, but User2 may insert a similar record in Table1 and finish working with it the next week or month or in 3 months.
The issue arises when the Table1_Hist starts to grow to the point of affecting the performance of the queries to this table. I had in mind to split this table in Table1_Hist_1, Table1_Hist_2... Table1_Hist_n and create an ID table Map where I can register the range of IDs stored on each table. For example, I may have a map saying that in Table1_Hist_1 are stored the IDs from 1 to 10M and so on, but as I said before, there is no order in time in which the records are inserted in these historical tables, hence I may have a Map from 1 to 10 pointing to table Table1_Hist_1 and also have a record ID=3 in Table1_Hist_2, just because the ID=3 was finished 2 months later and stored in the following table.
So, anyone knows about an efficient approach to split a table in multiple tables with their respective mapping?
database database-design query-performance
database database-design query-performance
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
edited Dec 31 '18 at 0:11
Joe Almore
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
asked Dec 30 '18 at 23:24
Joe AlmoreJoe Almore
1,30742953
1,30742953
1
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54
add a comment |
1
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54
1
1
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54
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%2f53982240%2fhow-does-table-partition-and-mapping-work%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%2f53982240%2fhow-does-table-partition-and-mapping-work%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
Which rdbms are you using?
– twoleggedhorse
Dec 30 '18 at 23:45
@twoleggedhorse applies to any.
– Joe Almore
Dec 30 '18 at 23:54