marrowyung
asked on
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?
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?
ASKER
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/
"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/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please help to answer this if you can:
https://www.experts-exchange.com/questions/29135660/impact-on-MariaDB-GUI-and-report-design-AFTER-changing-from-Oracle.html
https://www.experts-exchange.com/questions/29135660/impact-on-MariaDB-GUI-and-report-design-AFTER-changing-from-Oracle.html
ASKER
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 ?
"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 ?
ASKER
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?
"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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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 ?
"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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
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 ?
ASKER
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 ?
"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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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:
MariaDB:
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 ?
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:
MariaDB:
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 ?
ASKER
"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 ?
this is another very important part and it is what the BI developer is asking.
any idea ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
when the last time you do this similar thing,any change in UI design and report design to fit new DB ?
ASKER
any update on this ?
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.
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.
ASKER
"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?
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?
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...
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...
ASKER
"OTOH most of those programs run around formatting so that might make it easier or maybe automatic...
what is that mean I am sorry ?
what is that mean I am sorry ?
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.
Try before taking actions.
ASKER
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?
can you share the size of that type of MariadB?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the link said:
The following are equivalent:
but equal in what ? CPU loadikng , RAM , or size . I can only assume it is about size.
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)but equal in what ? CPU loadikng , RAM , or size . I can only assume it is about size.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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?
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?
>>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.
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.
ASKER
"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 .
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 .
>>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.
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.
ASKER
tks all.
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.