We have a Live connection built against an Oracle database for use in a dashboard. When you click on "View Data" and select the "Full Data" tab, the query can run for well over 10-15 minutes to return the data. Irregardless of the number of rows we see in the dashboard. We turned on Performance Recording and captured the SQL tableau generates and ran it in Toad. We noticed that Tableau adds "where rownum <= 75000". This correlates with the 75,000 rows text box in the "View Data" dialog box. When we run the SQL with the "where rownum <= 75000" it performs the same way as in Tableau Desktop or Tableau Server. Very slow. When we remove the "where rownum <= 75000" in Toad, the query returns in less than a second.
I get that this "where rownum <= 750000" is put in place so your users can't download millions of rows, i.e. a "safety" feature. But it's killing us on even one row of data. I talked with support and they mentioned this is not something we can turn on or off. It's part of the software design. Which is fine. However, I'm stumped as to why that is causing the query to perform so poorly and perform perfectly without it. Anybody else run in to something like this? Any work arounds?
Also, we built an extract on this live connection and it performs well. My thoughts are to have this extract scheduled to refresh every so often to refresh the data throughout the day. But it would be great to use the live connection.
We are on Tableau Server 10.5 and using Tableau Desktop 10.5 against an Oracle 12 database. Everything is on prem.
Screen Shots are here.....
Right click and select View Data
Click on the Full Data Tab
The 75000 here is inserted in the SQL as "where rownum <= 75000"