https://issues.qgis.org/https://issues.qgis.org/favicon.ico2013-10-15T02:37:03ZQGIS Issue TrackingQGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=456042013-10-15T02:37:03ZJukka Rahkonenjukka.rahkonen@mmmtike.fi
<ul></ul><p>Sounds good, knowing the exact extents of the layer is not really often so important. Actually the extents which are stored into metadata may be more important with Oracle because it is not possible to store new features outside the extents.</p>
<p>One detail, it would be better to use ALL_SDO_GEOM_METADATA, especially in corporate environments QGIS users do not necessarily own the tables they are using.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=456052013-10-15T02:50:10ZJonathan Moules
<ul></ul><p>Good point on "ALL_SDO_GEOM_METADATA".</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461712013-11-05T07:47:02ZJonathan Moules
<ul></ul><p>I believe it's a result of this, but if I have a QGIS project with multiple Oracle layers in it. When I load the project QGIS hangs for several minutes while waiting to establish all the connection/metadata stuff - even though none of the layers are enabled.<br />Unfortunately this basically renders QGIS un-useable with larger database Oracle sets. We're having to use our WMS as an interim solution.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461722013-11-05T08:26:22ZJürgen Fischerjef@norbit.de
<ul><li><strong>Tracker</strong> changed from <i>Bug report</i> to <i>Feature request</i></li></ul> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461732013-11-05T08:32:26ZJürgen Fischerjef@norbit.de
<ul><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>fixed/implemented</i></li></ul><p>implemented in <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/45fea7b461ab3a61b0d0d034b0f696c85d315499" title="oracle provider: optionally use extents from metadata table (implements #8850)">45fea7b46</a></p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461832013-11-06T02:01:21ZJonathan Moules
<ul></ul><p>Many thanks Jürgen - I'll test it in the next stable. :-)</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461842013-11-06T02:04:44ZJürgen Fischerjef@norbit.de
<ul></ul><p>Jonathan Moules wrote:</p>
<blockquote>
<p>Many thanks Jürgen - I'll test it in the next stable. :-)</p>
</blockquote>
<p>So there's no need for the weeklies anymore?</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461852013-11-06T02:21:44ZJonathan Moules
<ul></ul><p>Hmmm. I didn't know they were still going on. I can't find them on the new website - <a class="external" href="http://www.qgis.org/en/site/forusers/alldownloads.html">http://www.qgis.org/en/site/forusers/alldownloads.html</a> doesn't have them.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461862013-11-06T02:29:09ZJürgen Fischerjef@norbit.de
<ul></ul><p>Jonathan Moules wrote:</p>
<blockquote>
<p>Hmmm. I didn't know they were still going on. I can't find them on the new website - <a class="external" href="http://www.qgis.org/en/site/forusers/alldownloads.html">http://www.qgis.org/en/site/forusers/alldownloads.html</a> doesn't have them.</p>
</blockquote>
<p>No change there: <a class="external" href="http://qgis.org/downloads/weekly/">http://qgis.org/downloads/weekly/</a></p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=461872013-11-06T02:30:38ZJonathan Moules
<ul></ul><p>Many thanks. I'll try and test it next week then.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=489622014-02-21T05:58:52ZJonathan Moules
<ul></ul><p>I don't think this has actually been implemented; or at least, it doesn't seem be working (or maybe I'm missing something).<br />If I add a Oracle layer to QGIS, the amount of time it takes to actually add it to QGIS (rendering = off) is around the same amount of time that it takes to perform a SDO_TUNE.EXTENT_OF query on the layer.</p>
<p>Looking at the linked code:<br /><pre>
if ( !mSpatialIndex.isNull() && ( mUseEstimatedMetadata || mSqlWhereClause.isEmpty() ) )
2021 {
2022 sql = QString( "SELECT SDO_TUNE.EXTENT_OF(%1,%2) FROM dual" )
2023 .arg( quotedValue( QString( "%1.%2" ).arg( mOwnerName ).arg( mTableName ) ) )
2024 .arg( quotedValue( mGeometryColumn ) );
2025
2026 ok = exec( qry, sql );
2027 }
</pre><br />Surely "mUseEstimatedMetadata" should be a "not"? But I'm rubbish at C/++ and can't follow it in depth.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=489642014-02-21T06:53:59ZJürgen Fischerjef@norbit.de
<ul></ul><p>Jonathan Moules wrote:</p>
<blockquote>
<p>I don't think this has actually been implemented; or at least, it doesn't seem be working (or maybe I'm missing something).<br />If I add a Oracle layer to QGIS, the amount of time it takes to actually add it to QGIS (rendering = off) is around the same amount of time that it takes to perform a SDO_TUNE.EXTENT_OF query on the layer.</p>
</blockquote>
<p>Well, you were suggesting that SDO_TUNE.EXTENT_OF would be slower that retrieving the data directly from the view. Maybe it's not as bad as thought.</p>
<blockquote>
<p>Looking at the linked code:<br /><code>if ( !mSpatialIndex.isNull() && ( mUseEstimatedMetadata || mSqlWhereClause.isEmpty() ) )</code></p>
</blockquote>
<p>That means if there is a spatialindex (prerequisite for <code>SDO_TUNE.EXTENT_OF</code>) and there's no filter clause (because then the extents of the subset could be smaller than the full extent) or "estimated metadata" is on (in which case that difference is ignored) the extents are retrieved by <code>SDO_TUNE.EXTENT_OF</code>.</p>
<p>But if "estimated metadata" is on, it'll retrieve the extents from the view, if successful return them in line 2013 of <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/45fea7b461ab3a61b0d0d034b0f696c85d315499" title="oracle provider: optionally use extents from metadata table (implements #8850)">45fea7b46</a> and this piece of code will not be reached.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=489662014-02-21T08:14:35ZJonathan Moules
<ul></ul><p>I see what's happening here. There's some confusion about what the two metadata checkboxes do. My perception is that they should be like this:</p>
<p>"only look in meta data table" - I believe this checkbox should speed up loading of any single layer. Basically everything comes out of ALL_SDO_GEOM_METADATA except geometry type (which can't). This includes the MBR.</p>
<p>"Use estimated table metadata" - This should improve table listing speed when the user presses "connect". I don't believe it should have anything to do with SDO_TUNE.EXTENTS_OF (as QGIS doesn't need to know the bounding box when listing layers, and doesn't currently).</p>
<p>The problem with the current implementation is</p>
<blockquote>
<p>or "estimated metadata" is on (in which case that difference is ignored) the extents are retrieved by SDO_TUNE.EXTENT_OF.</p>
</blockquote>
<p>Which means that if I have "estimated metadata" checked (I do) then adding layers is super slow because it's doing SDO_TUNE. But if I un-check it then listing the tables becomes incredibly slow because it's getting data for every table!</p>
<p>I hope that's clearer. It's a little confused in my mind.<br />The TL;DR version is: I believe absolutely everything should come out of the ALL_SDO_GEOM_METADATA wherever possible.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=489692014-02-21T10:06:12ZJürgen Fischerjef@norbit.de
<ul></ul><p>Jonathan Moules wrote:</p>
<blockquote>
<p>"only look in meta data table" - I believe this checkbox should speed up loading of any single layer. Basically everything comes out of ALL_SDO_GEOM_METADATA except geometry type (which can't). This includes the MBR.</p>
</blockquote>
<p>No, it restricts the scan to the geometry columns listed in the meta data table. If that's not checked, it also scans all columns with geometry type (eg. views).</p>
<blockquote>
<p>"Use estimated table metadata" - This should improve table listing speed when the user presses "connect". I don't believe it should have anything to do with SDO_TUNE.EXTENTS_OF (as QGIS doesn't need to know the bounding box when listing layers, and doesn't currently).</p>
</blockquote>
<p>That accepts unprecise information about that data (ie. scans just a few rows instead of all to determine the geometry type, extents from metadata, instead of scanning all rows and ignoring that applied filters usually make the estimations about full extent and full row count non-applicable for a layer). So that's the <em>speed tops accuracy</em> switch.</p>
<blockquote>
<p>The problem with the current implementation is or "estimated metadata" is on (in which case that difference is ignored) the extents are retrieved by SDO_TUNE.EXTENT_OF.<br />Which means that if I have "estimated metadata" checked (I do) then adding layers is super slow because it's doing SDO_TUNE. But if I un-check it then listing the tables becomes incredibly slow because it's getting data for every table!</p>
</blockquote>
<p>First of all, extent doesn't matter when listing the tables and therefore doesn't matter for the table scan.</p>
<p>Second, as I already tried to lay out above, on layer insertion (that is after the scan is already finished), if you have checked "estimated metadata" the extent is read from the metadata table (as you requested, that's what <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/45fea7b461ab3a61b0d0d034b0f696c85d315499" title="oracle provider: optionally use extents from metadata table (implements #8850)">45fea7b46</a> does). Only if that fails for some reason <code>SDO_TUNE.EXTENT_OF</code> is used - and that only if there is no filter (because <code>SDO_TUNE.EXTENT_OF</code> would only consider the full extent w/o a filter - and that's inaccurate if there is one) <strong>or</strong> "estimated metadata" is on (ie. wrong extent, but <em>speed tops accuracy</em>).</p>
<p>Still if you don't notice an improvement when inserting layers after <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/45fea7b461ab3a61b0d0d034b0f696c85d315499" title="oracle provider: optionally use extents from metadata table (implements #8850)">45fea7b46</a>, <code>SDO_TUNE.EXTENT_OF</code> can't be that bad.</p>
<blockquote>
<p>The TL;DR version is: I believe absolutely everything should come out of the ALL_SDO_GEOM_METADATA wherever possible.</p>
</blockquote>
<p>That's what we do - for the listing and now later to determine the extent. But we need the geometry type, that it doesn't have and we want to be able to use tables and <strong>views</strong> that are not listed.</p>
<p>An approach to solve this would be caching what we need (in the database for all or locally per user) or consider the search settings in the scan and not just to filter it's results. I think we discussed both already - not sure if there's a tickets.</p> QGIS Application - Feature request #8850: Oracle not looking in USER_SDO_GEOM_METADATA for extentshttps://issues.qgis.org/issues/8850?journal_id=490452014-02-24T03:28:03ZJonathan Moules
<ul></ul><p>Hi Jürgen,</p>
<p>Thanks for taking the time to write a detailed reply. I appreciate I'm not being as clear as I should be.</p>
<p>Following what you've said I've now investigated with our DBA's what gets run when I click "add" data. Note that the "render" button is disabled.<br />The following is run:</p>
<pre><code>select * from osmm.osm_mm_topographic_area where rownum = 0</code></pre>
<p>Which is wrong because render is off; it shouldn't be selecting anything.</p>
<p>Also the below (which seems to be correct):</p>
<pre><code>select DIMINFO, SRID from ALL_SDO_GEOM_METADATA WHERE owner = SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(:B2 ) AND table_name = SYS.DBMS_ASSERT.SIMPLE_SQL_NAME(:B1 ) AND column_name = upper(SYS.DBMS_ASSERT.QUALIFIED_SQL_NAME(COLUMN_NAME))</code></pre>
<p>So the problem is that Oracle is selecting all the data when the layer is added even though QGIS isn't doing anything with it. I'll open a new ticket.</p>
<p>Thanks again for your patience!<br />Jonathan</p>