change of type from Oracle to MariaDB/MySQL

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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Chris StanyonWebDevCommented:
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 DBACommented:
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.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

slightwv (䄆 Netminder) 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.
Chris StanyonWebDevCommented:
@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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
slightwv (䄆 Netminder) 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 DBACommented:
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.
slightwv (䄆 Netminder) 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.
Chris StanyonWebDevCommented:
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.
Chris StanyonWebDevCommented:
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 DBACommented:
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.
slightwv (䄆 Netminder) 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?
Chris StanyonWebDevCommented:
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 ?
Chris StanyonWebDevCommented:
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.
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
Databases

From novice to tech pro — start learning today.