QGIS » QGIS Application

Hello,

the first time you make a connection to an Oracle database (and each time you click on the "connect" button), QGIS tries to list all the tables that are stored in the database. This scan can take a great amount of time depending on what options you have selected in the connection dialog box:

• "Only look in metadata table": QGIS will only scan the tables that are listed in USER_SDO_GEOM_METADATA or ALL_SDO_GEOM_METADATA (Oracle metadata views).
• "Only look for user's table": QGIS will only scan tables that belong to user schema.
• "Also list tables with no geometry": QGIS will scan geographic and non geographic tables.
• "Use estimated table metadata": QGIS will use the informations found in Oracle metadata views. Furthermore, it will scan only the first 100 lines of each table to determine the geometry type of the layer.
• "Only existing geometric types": will look after the geographic tables that are not empty (have a geographic type).

You have to know that Oracle metadata views don't store geometry type. As QGIS needs this information to open the layer, QGIS "scans" the geometric type of all objects (or the first 100 if "Use estimated table metadata" is checked) to find the geometric types of the layer. And it do it on each table that is scanned.

When this first global scan is done, the list of the tables and their datatypes is stored in a local SQLite database (cache) to speed up the display of the tables in the connection dialog box. If you have lots of tables (> 3000) it can take about 10 seconds to QGIS to extract all of the lines of the SQLite cache but not 20 minutes.

If it takes too much time for the first connection:

• Check option "Use estimated table metadata".
• Only list the tables of the user (check option "Only look for user's table").
• If you are confident in the declaration of your geographic tables into Oracle metadata views (should be the case), check connection option "Only look in metadata table".
• Avoid non geographic tables (less tables to scan).

Furthermore, for each layer, you should declare the following metadata:

• Be sure to use a non NULL SRID for each layer.
• Name the extent dimensions as 'X' and 'Y', otherwise, QGIS will not be able to use them and each time you'd like to open the layer, QGIS will launch the extent calculation on the server (which implies to access to each index of each object on the layer).

I think that as long as we scan each table to determine geometric type, the listing of all tables will consume time.

Hi,

I see this is closed, but I'm not only experiencing a long delay when adding a new oracle connection (in spite of following the above suggestions), QGIS also crashes every time, and I can't successfully add a connection at all. This is QGIS 2.18 on macos sierra using osgeo4mac homebrew installer

Thanks