change of type from Oracle to MariaDB/MySQL

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

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.

Chris Stanyon,

OP is ?

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?

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

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

hi,

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

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

Select allOpen 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?

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 ?

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?

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?

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.

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

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.

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?

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.

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

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

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.

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

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

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 ?

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

tks.

tks all.