In general when working with Oracle, you will find that performance is much better if you turn your Custom SQL connection into a VIEW on the database. You can then use Tableau to connect directly to the view, which should show up in the single-table connection list. The reason for this performance difference is that a Custom SQL connection relies on subqueries, which Oracle does a very poor job of optimizing; in contrast, a view is essentially a named, persisted query that informs Oracle of your intent to perform that work repeatedly. Even the simplest VIEW (not a materialized one) can have much better performance than a Custom SQL connection with the same query text.
I hope this helps,
The reason for this performance difference is that a Custom SQL connection relies on subqueries, which Oracle does a very poor job of optimizing;
Robert is this true of other databases? MS SQL for instance? Thanks,
Yes, SQL Server will see a performance gain from this technique, though it's not as dramatic as with Oracle (because Microsoft SQL Server has an excellent query optimizer).
MySQL is like Oracle in having a dramatic performance gain from a VIEW vs. a subquery.
Beyond that, I don't have a comprehensive list.
Thank you for your advice, Robert. I created a dedicated view of the union of the two tables, and it now has tolerable performance (still much slower than accessing the table independently). It seems like there is actually a lot of popular debate on whether creating dedicated views actually speeds up performance in reading through some stack overflow discussions on the topic. In my case, it appears to have certainly helped. I still might end up creating a consolidated table. Not very elegant or good for the data model, but it might be needed eventually.