OraFAQ Forum: MySQL » Migrate MySQL Table to Oracle Materialized View

Home » Open Source » MySQL » Migrate MySQL Table to Oracle Materialized View

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Migrate MySQL Table to Oracle Materialized View [message #214134]

Mon, 15 January 2007 01:58

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

Report message to a moderator

Re: Migrate MySQL Table to Oracle Materialized View [message #214140 is a reply to message #214134]

Mon, 15 January 2007 02:06

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.

Report message to a moderator

Re: Migrate MySQL Table to Oracle Materialized View [message #214571 is a reply to message #214140]

Wed, 17 January 2007 02:52

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_char(datum,'YYYY')

I get different results.

Report message to a moderator

Re: Migrate MySQL Table to Oracle Materialized View [message #214610 is a reply to message #214571]

Wed, 17 January 2007 04:42

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?

Report message to a moderator