Home » Open Source » MySQL » Migrate MySQL Table to Oracle Materialized View
Migrate MySQL Table to Oracle Materialized View [message #214134] Mon, 15 January 2007 01:58 Go to next message
jay187
Messages: 3
Registered: January 2007
Location: Freiburg, Germany
Junior Member
Hello everybody,

I need to migrate a MySQL table to an Oracle MV. Heres what I did:
MySQL Statement:

CREATE TABLE transdb_AbUmVK_Mtl_Ean
SELECT UID, year(Datum) AS Jahr, month(Datum) AS Monat, ean, sum(Menge) AS AbsatzProEan,
sum(VK*Menge) AS UmsatzProEan, avg(VK) AS AvgVKT, count(*) AS BonAnzProEAN
FROM transdb
GROUP BY UID, year(Datum), month(Datum), ean;


Here my Oracle MV statement:

CREATE MATERIALIZED VIEW TRANSDB_ABUMVK_MTL_EAN
AS (
SELECT "UID",
to_number(to_char(datum, 'yyyy')) Jahr,
to_number(to_char(datum,'mm')) Monat,
ean,
sum(Menge) AS AbsatzProEan,
sum(VK*Menge) AS UmsatzProEan,
avg(VK) AS AvgVKT, count(1) AS BonAnzProEAN
FROM bv_import1.transdb
GROUP BY (
"UID",
to_number(to_char(datum, 'yyyy')),
to_number(to_char(datum,'mm')),
ean)
);


I know that UID is a reserved word, but thats not the problem right now. If I execute some statements to verify the similarity of table and MV I get some differences.
Heres what I executed:

select count(1), sum("UID"), sum(absatzproean), sum(umsatzproean), sum(avgvkt),
sum(bonanzproean) from transdb_AbUmVK_Mtl_Ean


The results differ for example in the column sum(bonanzproean)
MySQL 5491932
Oracle 5491766

Can anyone help me? Is the Materialized View correct?

Thanks
Jens
Re: Migrate MySQL Table to Oracle Materialized View [message #214140 is a reply to message #214134] Mon, 15 January 2007 02:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My first guess would be that the data in TRANSDB has changed between the two executions.
Re: Migrate MySQL Table to Oracle Materialized View [message #214571 is a reply to message #214140] Wed, 17 January 2007 02:52 Go to previous messageGo to next message
jay187
Messages: 3
Registered: January 2007
Location: Freiburg, Germany
Junior Member
Hi JRowbottom,

thanks for your reply. But the data isnt touched in any way between the two executions.
Actually there are two data bases: One table in Oracle and one in MySQL. The Oracle data is coming from a cvs dump from MySQL. And is imported vi sqlldr once and never touched again. And if you execute some test-statements on the different tables the results look good.

My guess is, that there are differences in the group by clause between Oracle and MySQL.
e.g. if I change the group by clause from
to_number(to_char(datum, 'YYYY'))


to
to_char(datum,'YYYY')

I get different results.
Re: Migrate MySQL Table to Oracle Materialized View [message #214610 is a reply to message #214571] Wed, 17 January 2007 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are there the same number of rows in the original table and the Oracle import?


Other than loosing the leading zero's on the months, I can't see any other differences that adding the TO_NUMBER would cause - what are you seeing?
Re: Migrate MySQL Table to Oracle Materialized View [message #214662 is a reply to message #214610] Wed, 17 January 2007 07:24 Go to previous messageGo to next message
jay187
Messages: 3
Registered: January 2007
Location: Freiburg, Germany
Junior Member
JRowbottom wrote on Wed, 17 January 2007 11:42
Are there the same number of rows in the original table and the Oracle import?

Yes they are. A
Select count(1) from transdb
shows the same for both MySQL and Oracle


Quote:
Other than loosing the leading zero's on the months, I can't see any other differences that adding the TO_NUMBER would cause - what are you seeing?


The month isnt stored with leading zeros, so there isnt any problem with the conversion from char to number in that case.
Re: Migrate MySQL Table to Oracle Materialized View [message #214671 is a reply to message #214662] Wed, 17 January 2007 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
do you have any dates prior to 01-01-1000?
Re: Migrate MySQL Table to Oracle Materialized View [message #214803 is a reply to message #214662] Thu, 18 January 2007 02:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What differences are you seeing between using
to_number(to_char(datum, 'YYYY'))
and
to_char(datum,'YYYY')
Previous Topic: MySQL Long Text to ORACLE CLOB
Next Topic: oracle dump to Mysql database.
Goto Forum:
  


Current Time: Wed Dec 07 20:23:15 CST 2016

Total time taken to generate the page: 0.12084 seconds