Skip to main content
  • EN
Hi,

 

I'm using a workbench to launch a SQL query on a Oracle Database with many JOIN.

 

I use SQLExecutor to do it.

 

When I query a specific record, the result is very fast.

 

But when I need to extract all the data from my query (more than 5000000 records)

 

My computor goes slow and FME failed to execut it.

 

I receive this : Insufficient memory available -- error code was 2

 

 

I need to extract all the point from my table ROL_UNITE_P and join 5 tables to add data.

 

Should I use more than one SQL Executor?

 

Should I use something else?

 

 

Thanks!

 

Francis

 

 

This is my SQL query :

 

SELECT rol.ID_PROVINC, rol.CODE_MUN, rol.RL0102A, rol.MAT18, adr.RL0101A, adr.RL0101B, adr.RL0101C, adr.RL0101D, adr.RL0101E, adr.RL0101F, adr.RL0101G, adr.RL0101H, adr.RL0101I, lot.RL0103A, prp.NOM_PROP, prp.PRN_PROP, prp.NO_COPROP, prp.ADR_POST, prp.MUN, prp.ADR_POST_COMPL_C, prp.ADR_POST_COMPL_P, prp.COD_POST, prp.DAT_INSC_ROLE, cd_gen.DESC_ AS CODE_GEN, cd_lien.DESC_ AS CODE_LIEN FROM rgora1a_res.ROL_UNITE_P rol LEFT JOIN rgora1a_res.B05V_ADR_UNITE_EVALN adr ON rol.ID_PROVINC=adr.ID_PROVINC LEFT JOIN rgora1a_res.B05PRP prp ON rol.ID_PROVINC=prp.ID_PROVINC                                                LEFT JOIN rgora1a_res.B05V_LOT_CADST lot ON rol.ID_PROVINC=lot.ID_PROVINC                                        LEFT JOIN rgora1a_res.SIGAT_CODE_GENERIQUE_ADRESSE cd_gen ON adr.RL0101E=cd_gen.CODE                             LEFT JOIN rgora1a_res.SIGAT_CODE_LIEN_ADRESSE cd_lien ON adr.RL0101F=cd_lien.CODE
Hi,

 

 

There are a couple of options you can try:
  • let oracle do the work, by creating a view
  • use the IQ transformer
regarding the error you can have a look at your temp settings, see Dave's answer here.

 

 

hope this helps.
I second Itay's suggestion, for these volumes it would probably be best to create a view in Oracle and then access it with the Oracle reader.

 

 

Alternatively, read the point features using the Oracle reader and let FME add the attributes later using either Joiners, FeatureMergers or an InlineQuerier (I assume that's what Itay meant by "IQ transformer").

 

 

But the solution using an Oracle view is definitely the best from a performance and memory standpoint, although it assumes that you have the rights to create such a view.

 

 

David
Thanks for your help.

 

I created a view with SQLDevelopper.

 

Then I created a workbench and then had a Oracle Reader.

 

When I launch the script, FME seems to build the SQL Query of the VIEW I created in SQL :

 

ORACLE Reader: Executing SQL Statement `SELECT "RGORA1A_RES"."PRP_LOT_SIGAT_2012"."ID_PROVINC", t......]

 

I presum it will work but it is long....

 

I will let know here my result.

 

Any oder idea is welcom!

 

Thanks again!
Hi again!

 

And now the result :

 

I now use a view in Oracle.  I made that view with SQLDevelopper.

 

And the View is made out of my Query (with 4-5 tables join).

 

It takes around 10 minutes with SQL Developper to return the result View with 6462990 records.

 

Then with FME I use a Oracle Reader.  I read the Oraclce View with a WHERE CLAUSE to query by sub region (about 17 regions).  Because All at one time crash FME.  With FME I build LIST and copy some attributs.

 

I ran the script 17 times with 17 different region with a batch file (dos).

 

3 hrs laters... All my 6462990 objets where read and my result FileGeodatabase was clean.

 

Thanks for your help again!
I don't have so much big query to ask, but I am not getting the answer.

 

I am simply using the replace function - 

 

REPLACE ( <road>,<' '>,<''>)

 

 

 

Simply remove the space from teh road name.

 

Ex= Road name - Express Highway

 

Expected Result  - ExpressHighway

 

 

Can anyone help me out.

 

I do not find the ask question button on the FME site, so just commenting. If anyone can also provide  me the line  also (:

Reply


Community Stats

30,108
Posts
114,280
Replies
37,742
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.