Bug report #9633

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

Added by Jonathan Moules over 10 years ago. Updated over 10 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 over 10 years ago

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

History

#1 Updated by Jürgen Fischer over 10 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 over 10 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 over 10 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 over 10 years ago

  • Status changed from Open to Closed

#5 Updated by Jürgen Fischer over 10 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 over 10 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 over 10 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 over 10 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 over 10 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 over 10 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