Re: Oracle to MYSQL database link Out-Of-Memory

[Bogdan Degtyariov]

Hi Roshan,

Executing the sub-query like (select * from mt.data_charging2018@bikwik) is going to extract all 3.5 billion rows from the table. The Connector/ODBC driver will try to cache them all, which in turn will cause the out-of-memory error.

I think your intention was not reading all rows, but getting metadata from the table. The easiest way is to limit the number of returned rows, which can be done by adding a LIMIT clause.

Something like this will return only one row, but the result set will contain the metadata for the entire table:
(select * from mt.data_charging2018@bikwik LIMIT 1)

There are other ways such as disabling row caching, doing pre-fetch of a small number of rows, using forward-only cursors. However, as I already mentioned, the easiest would be introducing the LIMIT clause.

The only thing, which can be concluded by fetching all rows from the table is the number of rows. MySQL has an easy way of doing it using COUNT() function:

select COUNT(*) from mt.data_charging2018@bikwik;

I hope it helps to overcome the problem.