1 Reply Latest reply on Mar 31, 2015 4:46 AM by onilke

    ESS Performance - Read Oracle Index



      ESS Performance - Read Oracle Index





           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





        • 1. Re: ESS Performance - Read Oracle Index

          I am looking for answers to the same (or similar) question: Why cannot FileMaker make use of Oracle indexes when it can read and update Oracle tables? Did the developers finish the ESS job halfway through, or is there an obstacle to this outside FileMaker/ESS?

          We are trying to use FileMaker as client with Oracle server (and would be happy not having to redo all the work done in Oracle already). But not making use of Oracle indexes leaves us with the option to import the whole thing into FileMaker to make it respond in time. Not what we wished for.

          (The article http://www.filemaker-solutions.be/IMG/pdf/techbrief_ess_en.pdf certainly states that FileMaker ESS functionality has no access to the ESS indexes whatsoever, but gives no explanation for this - only advice of how to avoid time-consuming actions in this environment. The article was written while FileMaker 9 was the current version. Nothing improved since then?)