Oracle number precision datatype performance impact
Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?
oracle
migrated from stackoverflow.com 8 mins ago
This question came from our site for professional and enthusiast programmers.
add a comment |
Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?
oracle
migrated from stackoverflow.com 8 mins ago
This question came from our site for professional and enthusiast programmers.
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
2
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago
add a comment |
Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?
oracle
Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?
oracle
oracle
asked 19 hours ago
Ayubxon Ubaydullayev
migrated from stackoverflow.com 8 mins ago
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com 8 mins ago
This question came from our site for professional and enthusiast programmers.
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
2
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago
add a comment |
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
2
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
2
2
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago
add a comment |
1 Answer
1
active
oldest
votes
The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be
- leave number columns without further definition -
number - enforce integer columns e.g.
number(20,0)or less commonlyinteger - for cases where it really matters (e.g. monetary values) specify scale and precision e.g.
number(15,2)
Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.
answered 18 hours ago
APCAPC
60138
2
"take the same amount of storage space: 22 bytes" - is not true.numberis a variable length data type and storing the value0uses less space than the value999999.99. See here: dbfiddle.uk/…
– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f226868%2foracle-number-precision-datatype-performance-impact%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
The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be
- leave number columns without further definition -
number - enforce integer columns e.g.
number(20,0)or less commonlyinteger - for cases where it really matters (e.g. monetary values) specify scale and precision e.g.
number(15,2)
Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.
answered 18 hours ago
APCAPC
60138
2
"take the same amount of storage space: 22 bytes" - is not true.numberis a variable length data type and storing the value0uses less space than the value999999.99. See here: dbfiddle.uk/…
– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
add a comment |
The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be
- leave number columns without further definition -
number - enforce integer columns e.g.
number(20,0)or less commonlyinteger - for cases where it really matters (e.g. monetary values) specify scale and precision e.g.
number(15,2)
Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.
answered 18 hours ago
APCAPC
60138
2
"take the same amount of storage space: 22 bytes" - is not true.numberis a variable length data type and storing the value0uses less space than the value999999.99. See here: dbfiddle.uk/…
– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
add a comment |
The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be
- leave number columns without further definition -
number - enforce integer columns e.g.
number(20,0)or less commonlyinteger - for cases where it really matters (e.g. monetary values) specify scale and precision e.g.
number(15,2)
Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.
answered 18 hours ago
APCAPC
60138
The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be
- leave number columns without further definition -
number - enforce integer columns e.g.
number(20,0)or less commonlyinteger - for cases where it really matters (e.g. monetary values) specify scale and precision e.g.
number(15,2)
Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.
answered 18 hours ago
APCAPC
60138
answered 18 hours ago
APCAPC
60138
answered 18 hours ago
APCAPC
60138
answered 18 hours ago
APCAPC
60138
60138
2
"take the same amount of storage space: 22 bytes" - is not true.numberis a variable length data type and storing the value0uses less space than the value999999.99. See here: dbfiddle.uk/…
– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
add a comment |
2
"take the same amount of storage space: 22 bytes" - is not true.numberis a variable length data type and storing the value0uses less space than the value999999.99. See here: dbfiddle.uk/…
– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
2
2
"take the same amount of storage space: 22 bytes" - is not true.
number is a variable length data type and storing the value 0 uses less space than the value 999999.99. See here: dbfiddle.uk/…– a_horse_with_no_name
17 hours ago
"take the same amount of storage space: 22 bytes" - is not true.
number is a variable length data type and storing the value 0 uses less space than the value 999999.99. See here: dbfiddle.uk/…– a_horse_with_no_name
17 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
if i define as a number without precision, how it works? what would be default precision?
– Ayubxon Ubaydullayev
2 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f226868%2foracle-number-precision-datatype-performance-impact%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
if you have any useful article, please write on comment
– Ayubxon Ubaydullayev
19 hours ago
2
The size of the column constraint (scale/precision) shouldn't matter; and the size of the number value itself is unlikely to be measurably significant. Do you have a reason to think either does make a difference?
– Alex Poole
18 hours ago