ESS Performance - Read Oracle Index

[laurentades]

All,

 

I am building a solution which requires ESS connection to Oracle 11g from FM12.

 

Got the ActualTech Oracle ODBC Oracle Driver. Setup went fine.

 

Managed to do imports from Oracle using the Import Script Step.

Can display ESS Shadow tables from Oracle in the FM Relationship graphs. However:

* when doing so, FM tells me that the External Table "does not provide a pre-defined unique key" and asks me to choose a field or field combination to identify the same

* All relationships i perform involving an ESS table display the 'T' cardinality symbol implying that the match field has no index information.

 

The subsequent problem is obvious : performance as we are reading hundred of thousands of records. It looks like the oracle index information cannot be read whilst it does exist.

 

I have checked with the Oracle DBA : the Indexes do exist on the table (not view) / fields we are accessing via ESS and the Oracle user used for ODBC connectivity does have adequate permissions to read both all_indexes and dba_indexes tables in Oracle. This has been confirmed using an straight SQL query in Oracle SQL developer.

 

Hence my question : does any one have an answer to the following questions :

* Is the issue related to Oracle ? ODBC technology itself ? Actual Tech ODBC Driver for Oracle ? ESS Technology in FM ? Setup of any of these layers ?

* Anything the Oracle DBA should be doing further so that FM can actually read the existing Index information ?

* Anything I am obviously forgetting ?

 

Any hint welcome

 

Thanks

 

Laurent