Bug report #9633
Adding a Oracle table selects all data; even when Render is off
|Affected QGIS version:||master||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||18205|
"Only look in meta data table" - checked
"Use estimated table metadata" - checked
When I click "add" for a Oracle table, QGIS performs a select:
- select * from osmm.osm_mm_topographic_area where rownum = 0
The problem is that it performs this select even if "render" is off. This causes QGIS to hang until the query is finished (for millions of rows this can be a minute or more).
I suspect this behaviour also carries over to loading a QGS file which has Oracle layers in it but disabled (loading one of those takes forever).
#6 Updated by Jonathan Moules about 6 years ago
Jürgen Fischer wrote:
select * from osmm.osm_mm_topographic_area where rownum = 0
Just retrieves the field list, but no actual data. Does
select * from osmm.osm_mm_topographic_area where 1=0perform any better?
select * from osmm.os_mm_topographic_area where rownum = 0
takes about 70 seconds.
select * from osmm.os_mm_topographic_area where 1=0
Takes 0.012 seconds! - Not a typo, that's 100th's of a second, so it performs much MUCH better!
select * from osmm.os_mm_topographic_area where rownum=2
Takes... The same amount of time as rownum = 0 (about 70 seconds)
So for us at least
1=0 is easily the best.
(tested against a general table).
#8 Updated by Jürgen Fischer about 6 years ago
Jonathan Moules wrote:
Anything with "rownum = n" results in data being returned.
Whereas the 1=0 doesn't get any data returned, just the table structure it appears. I'm assuming that's what's intended.
rownum starts with 1, so
rownum=0 returns no rows. But apparently Oracle isn't clever enough to figure that out before hand, while it does for