Impact analysis on column type changing AFTER migrate from Oracle to MariaDB?

hi,

we planning to migrate from Oracle to MariaDB and there will be change on data type, will there be any impact on column type changing ?

If no impact, any reason for it?

and usually any impact on GUI and report design?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark GeerlingsDatabase AdministratorCommented:
It all depends on exactly how many different data types you have now in Oracle and on exactly which data types those are, and/or on which lengths and/or numbers of significant digits you use now.

I don’t know MariaDB, so I don’t know exactly which data types it supports and for what lengths or numbers of significant digits.  I would expect some differences that could cause data truncation or rounding differences if an automated data transfer process is used.
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

"It all depends on exactly how many different data types you have now in Oracle and on exactly which data types those are, and/or on which lengths and/or numbers of significant digits you use now.
"

I have some information for you:

 NUMBER without () in oracle (e.g. NUMBER) convert to Double type in MariadB.
https://mariadb.com/kb/en/library/double/

NUMBER(3,0) (3 digits / no decimal places) will be convert to SMALLINT
https://mariadb.com/kb/en/library/smallint/


NUMBER(1,0) (1 digits / no decimal places) will be convert to TINYINT.
https://mariadb.com/kb/en/library/tinyint/

NUMBER(10,0)  (10 digits / no decimal places) and NUMBER (9,0) (9 digits / no decimal places) will be convert to BIGINT
 https://mariadb.com/kb/en/library/bigint/
nociSoftware EngineerCommented:
NUMBER is comparable to  a DOUBLE in the ORACLE database.  
NUMBERS and DOUBLES may always have a small rounding error when used with 0 decimal places.
So if integers were meant from the get go....

Conversion to the various INT;s will make this deterministic. (INTS are INTS, no rounding).

Storage will shrink a little. (8+ bytes -> 1/2/8 bytes of storage).
(NUMBER(10,0) requires a 8 byte int.) NUMER(9,0) also if >+/-2.10e9)

Computation depends on wether conversions are required during processing.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

marrowyungSenior Technical architecture (Data)Author Commented:
marrowyungSenior Technical architecture (Data)Author Commented:
noci,

"Computation depends on wether conversions are required during processing."

all type CONVERTED ONCE on MariadB, so you mean no computation ?

"NUMBER is comparable to  a DOUBLE in the ORACLE database.   "

means not the same but no impact ?

'"will make this deterministic. (INTS are INTS, no rounding)."

sorry don't understand.

"1/2/8 bytes of storage"

what is 1/2/8 ?

"NUMER(9,0) also if >+/-2.10e9)"

what is +/-2.10e9 ? any example ?
marrowyungSenior Technical architecture (Data)Author Commented:
hi any update for me?

"Storage will shrink a little. (8+ bytes -> 1/2/8 bytes of storage).
(NUMBER(10,0) requires a 8 byte int.) NUMER(9,0) also if >+/-2.10e9)

Computation depends on wether conversions are required during processing."

looks like on impact?
nociSoftware EngineerCommented:
In another Q of your there was a discussion about NUMBER(M,D)...
please read there. I was wrong in assuming NUMBER(M,D) would be equal to DOUBLE(M,D).   NUMBER allows for M=34.
NUMBER allows for more precision then a floating point number can.
I cannot find how the numbers are stored internally within Oracle RDBMS.

In that case computation on numeric strings will take more time than any Binary equivalent.   (as adding ,  multiplying, dividing, subtracting, comparing, [ comparing is mostly subtracting and then see if there is a Negative, 0 or positive result], etc.)
Storage for these short numbers might be about Equal.

Conversion is a one time action, some queries might involve computation. (summing up amounts in orders etc.).
Binary datatypes are better handled by CPU's.

Floating point numbers always are some approximation in powers of two.   the Mantissa is always between 0 & 1 exclusive. times 2 to the power exponent, in a normalized form (there are no leading zero's in the mantissa).    
In decimal it would show as  stored is (3,1) meaning 0.3 *10^1 = 3
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
Hi,

Please take a look at this link that describes how to configure MariaDB to understand subset of Oracle's PL/SQL language without any code change. This makes migration a little bit easier to begin the transformation.

Regards,
    Tomas Helgi
marrowyungSenior Technical architecture (Data)Author Commented:
Tomas Helgi Johannsson,

"Please take a look at this link that describes how to configure MariaDB to understand "

knew this sQL mode already and i used it already, and that mode is not perfect ! if I have to migrate code from oracle by using tools like Ispirer, I have to turn this mode off or everything will be consider as PL/SQL but not MySQL anymore.

we should only turn this on when everything after migrated and we start to build using PL/SQL AFTER migration.

noci,

"Conversion is a one time action, some queries might involve computation. (summing up amounts in orders etc.).
Binary datatypes are better handled by CPU's.
"
of course it is a one time action but the convert is, AFTER that, type in MariaDB changed, what could be the impact ?

"In that case computation on numeric strings will take more time than any Binary equivalent."

AFTER convert from oracle to mariaDB, number will change to int, so in MariaDB it can be faster as CPU handle int/binary faster than NUMBER() in oracle ?  

but is it Binary equivalent = INT ?

and storage less as it is INT type ?

so CPU less consume, storage less and RAM almost the same ?

"Floating point numbers always are some approximation in powers of two. "

sorry what is it suppose to say ?
nociSoftware EngineerCommented:
Yes on the CPU/RAM load.

On formats of floating point numbers... both below explain better.,
https://andybargh.com/fixed-and-floating-point-binary/
https://www.pitt.edu/~juy9/142/slides/L3-FP_Representation.pdf
marrowyungSenior Technical architecture (Data)Author Commented:
I am sorry that it seems we are going to the same direction as other question and that's why you keep saying this to me in other ticket too.

let's look back to the other part of this question :

"usually any impact on GUI and report design?"

this is another very important part and it is what the BI developer is asking.

any idea ?

"Floating point numbers always are some approximation in powers of two.   the Mantissa is always between 0 & 1 exclusive. times 2 to the power exponent, in a normalized form (there are no leading zero's in the mantissa).    
In decimal it would show as  stored is (3,1) meaning 0.3 *10^1 = 3"

DOUBLE in MariaDB count as float point, so this applies  ?
marrowyungSenior Technical architecture (Data)Author Commented:
noci,

"Floating point numbers always are some approximation in powers of two.   the Mantissa is always between 0 & 1 exclusive. times 2 to the power exponent, in a normalized form (there are no leading zero's in the mantissa).    
In decimal it would show as  stored is (3,1) meaning 0.3 *10^1 = 3"

spend a lot of time on your link today but confused on how related to what we are talking about here.

"Floating point numbers always are some approximation in powers of two"
yeah, the link said floating point are binary so need to write in power of 2 .

but the links just said the SAME number can be present in other way by using floating point notation, then ... ?

why does matter to type conversion ?
nociSoftware EngineerCommented:
integer is fixed in what it can tell....
a 1 digit binary number can represent 2 values (0, 1) 2^1
a 2 digit binary number can represent 4 values (00, 01, 10, 11) = 2^2
a 3 digiti binary number can represent 8 values (000,001,010,011,100,101,110,111) = 2 ^3
(same like for decimal)
1 digit = 10 values (0,1,2,3,4,5,6,7,8,9),  10^1
2 digits = 100 values (00..99) ,  10^2

Floating point numbers (the comma can "float" around the number, using an exponent).
to write down large numbers say 1Million   one can say it = 1 x 10^6  , 2 million = 2x10^6
three billion = 3000 x10^6   or shorted (normalized) 3x10^9
the special thin about floating point is i only need to remember 1 or 2 and the 6 or 3 and  9.
So i can store the decimal examples above in 2 digit numbers if precision is 1 (mantissa) and exponent is 1 digit.      1,6 2,6 3,9  would suffice to write down 1 and 2 million and 3 billion.
Now how can i write down 2.5 million or 6.4 billion?   only by rounding until it fits.

And keep in mind using the integer form i can only write down 100 values (0-99) if used as an integer.
During conversion rounding will occur most of the time if different storage types are used.  and float that happens to be 2.99999999 (3 in float representation) will become 3 if normal rouing rules are followed during conversion to integers.

Fixed point real numbers are basicly an integer with the convention there should be a decimal point n digits from the right.
(because they are mostly used for presenting amounts etc. and are often used to display money ammounts) those are kept in a form that is easy to disply.
(Ascii strings, of BCD strings.) BCD is only the values 0-9 are used of a 4 bit binary number to upper 6 are left out... which makes an easy conversion to printable text and also older CPU's have instruction to actual calculate with BCD strings. (Add, subtract, often also multiply and divide).
marrowyungSenior Technical architecture (Data)Author Commented:
"So i can store the decimal examples above in 2 digit numbers if precision is 1 (mantissa) and exponent is 1 digit.      1,6 2,6 3,9  would suffice to write down 1 and 2 million and 3 billion."

tks. the first time to see this wrote in ',' to present exponent.

"During conversion rounding will occur most of the time if different storage types are used.  and float that happens to be 2.99999999 (3 in float representation) will become 3 if normal rouing rules are followed during conversion to integers."

this is a math rounding and a display rendering issue.

like to DATE change to DATETIME in MaraiDB will have display differently, in MariaDB there will be some 0 pending at the end .

is it the same issue or diff ?

e.g. this is the real example on DATE to DATETIME conversion I tried.

oracle:

Oracle DATE type value
MariaDB:

MariaDB DATETIME value
is that what you meant ?

"(because they are mostly used for presenting amounts etc. and are often used to display money ammounts) those are kept in a form that is easy to disply. "

good !

it seems the value stored is the same, but the display will depends on what the TYPE is , then display differently on the SAME value ?



"During conversion rounding will occur most of the time if different storage types are used.  and float that happens to be 2.99999999 (3 in float representation) will become 3 if normal rouing rules are followed during conversion to integers."

so now seems only this one related to type conversion impact analysis ?

and the answer is , it will be display differently ONLY but store value the same ? and compare program can see them as the same thing ?
marrowyungSenior Technical architecture (Data)Author Commented:
"usually any impact on GUI and report design?"

this is another very important part and it is what the BI developer is asking.

any idea ?
nociSoftware EngineerCommented:
I doubt if there will be differences in design, you may see data being represented differently if no expliciti formatting is done. You can format dates as part of the query (Oracle could do that, Mysql can do that).

If left to default things will show differetly due to different choices in defaults.
(Earlier comment on use of Comma , to represent exponent, is just mean to show two parts of a number could have been any sign, , is often used in coordinate systems).
Actually there are 3 parts in in a Float, (sign,  antisssa, exponent)  where exponents encode the sign differently (Excess  notation)... Check earlier mentioned wikipedia article for more detailed explanation. (IEEE 754).
marrowyungSenior Technical architecture (Data)Author Commented:
so this means nothing has to change but the way to display information ?

when the last time you do this similar thing,any change in UI design and report design to fit new DB ?
marrowyungSenior Technical architecture (Data)Author Commented:
any update on this ?
nociSoftware EngineerCommented:
The program will need to use mysql client lib in stead of OCI lib. .... = change
The program might need to adjust formatting (in query prep) or from results.  (due to different libraries with different defaults etc.).
Types have changed of columns which might need adjustment in declarations.
Other declarations might need some extra code to cope with that....

That is not UI but there is a change to be made in the program behind the UI.
If all field content looks the same has the same size etc. then the UI (forms on screen) don't need to change.

All those things are hard to predict... does the UI change from user perspective..., most probably not.
marrowyungSenior Technical architecture (Data)Author Commented:
"The program will need to use mysql client lib in stead of OCI lib. .... = change"

ok, client library

"Types have changed of columns which might need adjustment in declarations."

ok. in BI report coding ?

"All those things are hard to predict... does the UI change from user perspective..., most probably not."

yeah, may need to setup the SAME BI program on the new DB platform and try, right?
nociSoftware EngineerCommented:
Yep.  
Report queries most probably need adjustment for formatting...
OTOH most of those programs run around formatting so that might make it easier or maybe automatic...
marrowyungSenior Technical architecture (Data)Author Commented:
"OTOH most of those programs run around formatting so that might make it easier or maybe automatic...

what is that mean I am sorry ?
nociSoftware EngineerCommented:
Report generators are specified (in most cases) to format fields on a report in some way, so you might no need to adjust queries to conver to values to something like you got before.. as report generators will doe formatting (ie. conversion) anyway.

Try before taking actions.
marrowyungSenior Technical architecture (Data)Author Commented:
right now as I found the conversion tools Ispirer need to use the Nvarchar() type to convert from oracle varchar2() type because of the chinese characters, and I can't found the size of Nvarchar().

can you share the size of that type of MariadB?
slightwv (䄆 Netminder) Commented:
>>can you share the size of that type of MariadB?

The docs?

You posted the doc links for the numbers above.  Same docs talk about NVARCHAR:
https://mariadb.com/kb/en/library/varchar/

Looks like in MariaDB a VARCHAR and NVARCHAR are the same.

As far as the "size" it is based on the characterset used when created.
marrowyungSenior Technical architecture (Data)Author Commented:
the link said:

The following are equivalent:

VARCHAR(30) CHARACTER SET utf8
NATIONAL VARCHAR(30)
NVARCHAR(30)
NCHAR VARCHAR(30)
NATIONAL CHARACTER VARYING(30)
NATIONAL CHAR VARYING(30)

Open in new window


but equal in what ? CPU loadikng , RAM , or size . I can only assume it is about size.
slightwv (䄆 Netminder) Commented:
equal in "data type".  It appears MariaDB only has the one varchar data type and aliases for ANSI type standards.

So from an underlying structure there is no difference between a VARCHAR(10) and NVARCHAR(10).

Therefore the size is also the same.

It is the character sets that dictate the actual data storage size.
marrowyungSenior Technical architecture (Data)Author Commented:
"It is the character sets that dictate the actual data storage size."

you mean only DIFF type has diff ?

"So from an underlying structure there is no difference between a VARCHAR(10) and NVARCHAR(10)."

there must be different and that's why nvarchar () can store chinese characters but not varchar()
agree?
slightwv (䄆 Netminder) Commented:
>>you mean only DIFF type has diff ?

I do not understand what you at asking.

>>there must be different and that's why nvarchar () can store chinese characters but not varchar()
agree?

Did you not read the MariaDB doc link I posted?  In MariaDB you can assign a character set to varchar.  If is the character set that allows for different characters not the data type itself.

In MariaDB, per the documentation:  There is NO DIFFERENCE between varchar and nvarchar.
marrowyungSenior Technical architecture (Data)Author Commented:
"I do not understand what you at asking."

only char, data and INT, double are diff

ok now, any query you have can check the size of the table after data is here for MySQL/MariadB ? I want to compare one single table size .
slightwv (䄆 Netminder) Commented:
>>only char, data and INT, double are diff

I don't know MariaDB so I cannot say if they are different in storage or not.

I'm not going to re-read the entire thread or your previous questions on this but I don't remember seeing definitive proof the storage is drastically different.

>>ok now, any query you have can check the size of the table after data is here for MySQL/MariadB ?

No.  I don't use MariaDB.  I'm sure there are examples on the web to get storage size for a column similar to VSIZE in Oracle.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.