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.

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?

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.

"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/

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.

https://www.experts-exchange.com/questions/29135660/impact-on-MariaDB-GUI-and-report-design-AFTER-changing-from-Oracle.html

"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 ?

"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?

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

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 "

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 ?

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

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 ?

"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 ?

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,1

(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).

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 ?

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

any idea ?

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).

when the last time you do this similar thing,any change in UI design and report design to fit new DB ?

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.

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?

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...

what is that mean I am sorry ?

Try before taking actions.

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.

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.

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.

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?

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

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 .

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.

Oracle Database

From novice to tech pro — start learning today.

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.