Thanks for the question, Sree.
Asked: March 13, 2024 - 3:48 am UTC
Last updated: March 18, 2024 - 2:39 am UTC
Version: 19C
Viewed 100+ times
You Asked
1. I have few number column with data type declared as Number, Number (5), Integer, Numeric(10).
I know in few cases the maximum data is 2 digits and I see that is declared as Number(38)/ NUMBER / Numeric(30) /Integer
if i don't declare as number(2), instead if i declare as ( Number(38)/ NUMBER / Numeric(30) /Integer) will there be any performance
issue when I have a table with millions of records and that is used in updating the data or used in Where clause
2. Varchar2
I have a column with 1 character (Y/N)
if i declare this as Varchar2(1 CHAR) instead of VARCHAR2(1 BYTE). Will there be any performance issue when we use this column in
where condition for millions of records?
3. IS it advisable to use ANSI Datatypes in table declaration or always preferable to use Oracle Data types, will there be any performance issue?
Please advise
and Connor said...
1) Performance wise unlikely but there are reasons you to have reasonable precision when using numbers is
a. You'll use more memory when querying (the client typically needs to allocate memory based on *possible* number of digits coming back)
b. Correctness. NUMBER(2) for a 2 digit number stops an incorrect 3 digit number coming in.
c. Precision. "NUMBER" allows for decimals, rounding errors due to floating point etc.
2) No difference
3) ANSI datatypes are mapped internally to Oracle datatypes so no difference.