change of type from Oracle to MariaDB/MySQL

marrowyung
marrowyung used Ask the Experts™
on
Hi,

as we found data type after convert from Oracle to MariaDB there are diff, I'd like to know:

1) does MySQL/MariaDB has DATE only type ? Why MariaDB use datetime? MariaDB only has datatime?
2)  Why varchar2(75) become TEXT
3) why varchar2(100) become TEXT

any information about what is the space occupied by both type ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
MySQL and MariaDB both have a DATE type. It takes 3 bytes. The DATETIME takes 8 bytes.

MySQL doesn't have a VarChar2 type, so it needs to convert it. I think some conversion scripts use TEXT if your VARCHAR2 column is more than 255 characters. In older versions of MySQL (<5.0.3), VARCHAR held a maximum of 255 characters, so it would have failed to convert- hence the use of TEXT.

The storage for TEXT and VARCHAR is roughly based on the data that is stored. You can see full details here -> https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

You can read about the DataType here -> https://dev.mysql.com/doc/refman/8.0/en/data-types.html
johnsoneSenior Oracle DBA
Commented:
When converting from Oracle, you would want a DATETIME datatype.  The Oracle DATE datatype includes time.  If you convert it to DATE, you would lose data.
marrowyungSenior Technical architecture (Data)

Author

Commented:
johnsone,

very good explanation. tks.

so the DATE oracle type is very diff from DATE MariaDB type ?

it has no size?

and I tried to alter the DATE type of datetime type in MariaDB, it change nothing, what is the correct syntax to alter the column the DATE column ?

Chris,

"MySQL and MariaDB both have a DATE type. It takes 3 bytes. The DATETIME takes 8 bytes."

tks. but is there any one to one field type SIZE comparsion between oracle and MariaDB? we want to know the increase in sizes after migrate to MariaDB.

"MySQL doesn't have a VarChar2 type, so it needs to convert it. I think some conversion scripts use TEXT if your VARCHAR2 column is more than 255 characters. I"


exactly TEXT type but I heard that TEXT type can't be fully index, right?  then how can we solve it in MariaDB ?

"https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html"

any comparsion with Oracle on the same type.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>so the DATE oracle type is very diff from DATE MariaDB type ?  

Johnsone explained this:  The Oracle DATE datatype includes time.

DATE in MySQL/Maria doesn't automatically include a TIME component.  With Oracle it ALWAYS does.  You cannot not have it.

>> it has no size?

The docs are your friend:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

>>"MySQL doesn't have a VarChar2 type, so it needs to convert it. I think some conversion scripts use TEXT if your VARCHAR2 column is more than 255 characters. I"

I'm not sure where that came from.

The MariaDB docs say it can be up to 65,535:
https://mariadb.com/kb/en/library/varchar/

A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,535

Even the MySQL docs state it:
https://dev.mysql.com/doc/refman/8.0/en/char.html
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
@slightwv - the point about converting VarChar2 to Text based on size was because older versions of MySQL did limit the size of VarChar to 255. If the OP used a conversion script that was written a while ago, that script wouldn't safely be able to convert VarChar2 to VarChar because of the aforementioned limitation on size - the safe bet was to convert it to text. I don't know how the OP is doing the transfer between Oracle and MySQL, but this is one explanation as to why the conversion may be doing what it's doing.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Chris Stanyon,

OP is ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
do any of you know what is the total size of a column takes ? or just calculate based on that type ?

usually once converted to MariaDB, will size larger?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>OP is ?

I believe it is Original Poster.  That is you since you asked the question.

>>do any of you know what is the total size of a column takes ? or just calculate based on that type ?

Some data types are fixed size.  Some variable.  You have overhead for things like the rowid in Oracle.

>>usually once converted to MariaDB, will size larger?

I really don't like database size questions.  There is always overhead that is next to impossible to calculate.  There are also space allocations depending on what size you are asking about.

You need to look at the data types and overhead of the database vendor to come up with a close table estimate.

Then you have index sizes and how those are implemented.  

You'll need to understand the vendors architecture to get much of this information.  In Oracle for example, there is a difference between allocated size and used size.

After you get all that, you'll need the unseen sizes for things like how the product handles undo/redo, data dictionary, etc...
marrowyungSenior Technical architecture (Data)

Author

Commented:
"I really don't like database size questions.  There is always overhead that is next to impossible to calculate.  There are also space allocations depending on what size you are asking about.

You need to look at the data types and overhead of the database vendor to come up with a close table estimate."

this means to me that  I have to try migrate to target DB and then read the size in the disk by eyes, agree?
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

do you all know why this query can't change my type to date?

ALTER TABLE `<schema>.<table name>` MODIFY CREATE_DT DATE;

Open in new window


it ran ok but nothing changed.

chris:

"MySQL and MariaDB both have a DATE type. It takes 3 bytes. The DATETIME takes 8 bytes.
"

because of this ?

"the point is, varchar(100) can change to varchat (50) and TEXT can change to varchar (n), so datetime can't change to DATE so it has no effect at all ?"

our convert make VARCHAR2(100 CHAR) to TEXT, it is not more than 255, why still convert to that type ?

and actually what is the diff between VARCHAR2 and VARCHAR ? then VARCHAR2(100) and VARCHAR(100), VARCHAR2(n) is for multi language ? like display chinese?
marrowyungSenior Technical architecture (Data)

Author

Commented:
Chris Stanyon,

"MySQL doesn't have a VarChar2 type, so it needs to convert it. I think some conversion scripts use TEXT if your VARCHAR2 column is more than 255 characters."

I use tools like https://www.convert-in.com/oracle-to-mysql.htm to test convert it and it gives this result and I am suspect that diff tools give diff resutlt.

but what conversion scripts  you are referring to ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
slightwv (䄆 Netminder),

"I'm not sure where that came from.

The MariaDB docs say it can be up to 65,535:
https://mariadb.com/kb/en/library/varchar/"

so you mean oracle varchar2() can be convert to varchar() in mariaDB anyway?
marrowyungSenior Technical architecture (Data)

Author

Commented:
there are some interesting converted type :

1) VARCHAR2(1 CHAR) converted to varchar(4)
2) VARCHAR2(25 CHAR) converted to varchar(100)
3) NUMBER(10,0) to int(11)
4) NUMBER(3,0) to smallint(6)
5)NUMBER(1,0) to tinyint(4)
6) VARCHAR2(100 CHAR) to TEXT.

do you all think this convert make sense?
johnsoneSenior Oracle DBA

Commented:
Oracle uses a VARCHAR2 datatype because they do not comply with the ANSI standard for the VARCHAR datatype.  The biggest distinction is how NULL and an empty string are treated.

Oracle supports using the VARCHAR datatype, but currently treats it as VARCHAR2.  In fact the documentation clearly states to not use VARCHAR as Oracle seems to have future plans for it.

Looking at a list of datatypes for MariaDB, here, they do not support the VARCHAR2 datatype.  While VARCHAR and VARCHAR2 store the same type of data and you should be able to put data from Oracle into VARCHAR in MariaDB, there is a good chance that you may need to change some code to account for the differences in the datatype.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>so you mean oracle varchar2() can be convert to varchar() in mariaDB anyway?

Yes.

>>do you all think this convert make sense?

I would not use TEXT unless you had to.  I believe TEXT is like Oracle's CLOB.

>>VARCHAR2(n) is for multi language ? like display chinese?

the "n" there is describing a "n"umber.  This isn't the NVARCHAR2 replacement in Oracle.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
When I talk about conversion scripts, I'm talking in general terms.

In order to convert from Oracle to MySQL, a conversion script of some type will be used. The fact that you're using a program to do it, likely means that the script is built into the program. In that program, do you not have the option to specify the target data type and choose VarChar. It may just be that VarChar2 defaults to TEXT, and it's up to you to choose the datatype you actually want.

Nowadays, you should be able to safely convert an Oracle VarChar2 to the a MariaDB/MySQL VarChar type without problems. In the past you couldn't do this because VarChar only held upto 255. That's not the case anymore.
marrowyungSenior Technical architecture (Data)

Author

Commented:
johnsone ,

"In fact the documentation clearly states to not use VARCHAR as Oracle seems to have future plans for it."

yeah, we all try to avoid varchar() and try to use varchar2()

"While VARCHAR and VARCHAR2 store the same type of data and you should be able to put data from Oracle into VARCHAR in MariaDB, there is a good chance that you may need to change some code to account for the differences in the datatype.
"

agree.

so here comes  much detail point:

1) VARCHAR2(1 CHAR) converted to varchar(4)
2) VARCHAR2(25 CHAR) converted to varchar(100)
6) VARCHAR2(100 CHAR) to TEXT.

why VARCHAR2(1 CHAR)  = varchar(4) ?

why VARCHAR2(25 CHAR) = varchar(100) ?

why VARCHAR2(100 CHAR) = TEXT ?

slightwv,

"I would not use TEXT unless you had to."
any problem with TEXT type ?

"  This isn't the NVARCHAR2 replacement in Oracle."

so you mean nvarchar2 is for other language?

Chris Stanyon,

" It may just be that VarChar2 defaults to TEXT, and it's up to you to choose the datatype you actually want.

yeah ! I am testing Ispirer and they say we can change a type of what we want but no error will flow out ! like changing varchat() to int().

" In the past you couldn't do this because VarChar only held upto 255. That's not the case anymore.

you mean in mariaDB ? as it can hold 65535 characters now?
marrowyungSenior Technical architecture (Data)

Author

Commented:
and other key part is:

1) why NUMBER(10,0) = int(11) ?
2) why NUMBER(3,0)  = smallint(6)
3) why NUMBER(1,0) = tinyint(4)

I mean the number assign to new type in MariaDB.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
In older versions of MySQL and MariaDB, a VarChar column could only hold 255 characters. It can now hold 65535, so it's safe to convert VarChar2 to VarChar

The second argument to a NUMBER datatype is decimal places. In your example above, they're all set to 0 - meaning 0 decimal places (otherwise known as Integers). MySQL has different sized integer datatypes available, so a NUMBER(10,0) goes into an INT column with size of 11 (so that it can fit your 10,0 in. Number(3,0) can safely be converted to SMALLINT and NUMBER(1,0) can safely be converted to TINYINT
marrowyungSenior Technical architecture (Data)

Author

Commented:
" It can now hold 65535, so it's safe to convert VarChar2 to VarChar
"

So it is about 65535 characters?

"they're all set to 0 - meaning 0 decimal places (otherwise known as Integers)"

so NUMBER(10,0) = nnnnnnnnnn.0 ? where n is the digit after decimal point ? or 10 mean number 1-10 ?

" so a NUMBER(10,0) goes into an INT column
"
INT has nothing AFTER decimal point and has 10 digital after decimal point ?

so why it is the INT. INT type -2147483648 to 2147483647, why it can fit that number (10,0)?


"Number(3,0) can safely be converted to SMALLINT "

Smallint is from -32767 to 32767, how a number field of 3 x digit BEFORE the decimal and zero decimal place type can fit it ?

"NUMBER(1,0) can safely be converted to TINYINT"

TINYINT is range from -128 to 127, why NUMBER(1,0) can fit it?
johnsoneSenior Oracle DBA

Commented:
You are using a tool to do the conversion.  The tool has rules.  It is meant to do the best it can.  We cannot explain why a tool does something you don't want it to.  Stop blaming the databases for the shortcomings of a tool that is not associated with either one.  Unless you plan on ditching the tool and writing your own, there really isn't much of a point in these arguments.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>so you mean nvarchar2 is for other language?

It will allow multi-byte characters even if the default database  character set will not.

I'll let you read the docs.  This is the 11.1 docs but it is the first one I found and should still be the same:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i14946

>>why VARCHAR2(1 CHAR)  = varchar(4) ?

My guess is the tool thinks your database is a 4 byte character set.  Probably UTF16:
https://mariadb.com/kb/en/library/supported-character-sets-and-collations/

So 1 CHAR = 4 Bytes.

>>why VARCHAR2(100 CHAR) = TEXT ?

As Chris has suggested, maybe the tool is thinking old MySQL before the maximum was > 255 characters?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
I think you're missing the point on datatypes, and I would suggest you start reading up on MySQL. The documentation will explain it all in great detail!

So it is about 65535 characters?

Yes!

so NUMBER(10,0) = nnnnnnnnnn.0

No! The 0 means there is no decimal place, none, zero. It's a whole number, otherwise known as an INTERGER! A NUMBER(10,0) would look like 1736274635. A NUMBER(3,0) would look like 837, and an NUMBER(1,0) would look like 5. No decimal places!

In MySQL, there are various types that hold INTEGERS. Which type you use will depend on the range of numbers that you want to store. An INTEGER datatype can hold signed numbers with values between -2147483648 -> 2147483647.

If you have an Oracle column of Number(3,0), then it can hold INTEGERS in the range of -999 -> 999 (I think! - 3 digits / no decimal places). In MySQL, the best datatype for that is SMALLINT, because that can hold values from -32768 -> -32767 so it's more than enough to safely convert your NUMBER(3,0) without losting data.

And a NUMBER(1,0) can holder values from -9 -> 9. TINYINT can store values between -255 and 255 so again, it's obvious that data from Oracle NUMBER(1,0) will easily fit in a MySQL column that can hold -255 to 255.

I would really suggest you do some reading up on datatypes - https://dev.mysql.com/doc/refman/8.0/en/data-types.html
marrowyungSenior Technical architecture (Data)

Author

Commented:
Chris Stanyon,

"No! The 0 means there is no decimal place, none, zero. It's a whole number, otherwise known as an INTERGER! A NUMBER(10,0) would look like 1736274635. A NUMBER(3,0) would look like 837, and an NUMBER(1,0) would look like 5. No decimal places!
"

tks

"TINYINT can store values between -255 and 255 so again,"

from here: https://mariadb.com/kb/en/library/tinyint/

it should be The signed range is -128 to 127 , right ? or you are talking about mysQL ?
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Sorry. Typo in my previous message. TINYINT is -127 -> 127 signed values - it's 0 -> 255 if it's unsigned. THis applies to MySQL and MariaDB
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial