Bug report #9633

Adding a Oracle table selects all data; even when Render is off

Added by Jonathan Moules about 6 years ago. Updated about 6 years ago.

Status:Closed
Priority:Normal
Assignee:Jürgen Fischer
Category:Data Provider/Oracle
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

Description

"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).

Master 5192e8d

Associated revisions

Revision 115f9807
Added by Jürgen Fischer about 6 years ago

oracle provider: replace rownum=0 with 1=0 to retrieve columns (fixes #9633)

History

#1 Updated by Jürgen Fischer about 6 years ago

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=0 perform any better?

#2 Updated by Nathan Woodrow about 6 years ago

Does oracle have system tables or functions that you can get information about a table from. In MS SQL we use sp_columns which returns the columns for a table.

#3 Updated by Jürgen Fischer about 6 years ago

Nathan Woodrow wrote:

Does oracle have system tables or functions that you can get information about a table from. In MS SQL we use sp_columns which returns the columns for a table.

That doesn't work for generic queries or views.

#4 Updated by Jürgen Fischer about 6 years ago

  • Status changed from Open to Closed

#5 Updated by Jürgen Fischer about 6 years ago

Jürgen Fischer wrote:

Fixed in changeset 115f9807f10f168b4abd0c0dba5b82d15a426027.

Apparently 1=0 works better as rownum=0. Please verify.

#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=0 perform 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).

#7 Updated by Jonathan Moules about 6 years ago

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.

#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 1=0.

#9 Updated by Jürgen Fischer about 6 years ago

Jonathan Moules wrote:

select * from osmm.os_mm_topographic_area where rownum=2

Sorry, that was a typo, that I corrected to rownum=0 just after I wrote it.

#10 Updated by Jonathan Moules about 6 years ago

Jürgen Fischer wrote:

rownum starts with 1, so rownum=0 returns no rows.

True, I mis-stated.
But yes, 1=0 should resolve this issue and hopefully make loading Oracle layers nice and fast. Thanks!

Also available in: Atom PDF