Oracle SQL for EPM Tips and Tricks S01EP10
Hey guys how are you?
Today a quick tip that I think is very useful. From time to time the business ask us to validate if a table has data or not before we load it. It’s fare, specially if you use a truncate and insert approach.
The problem is, sometimes, the table/view they are asking for has millions of rows, and there’s no other safe way to validate if a table has data or not than querying it.
I just fixed a case where an interface had a validation that basically counts 3 different tables that together had 40 million rows per period. This validations were taking around 1000 sec to happens.
The data load that happens before that took 1200 sec. Then, basically the validation process were taking as much time as the load process.
After some changes, the query now is validating the 3 tables in 0.3 seconds. Way better than before. Basically I just used 3 things:
The hint /*+ FIRST_ROWS(1) */ that makes oracle prepare the best plan to query just one row (in my case since I used 1 as parameter.
The filter ROWNUM = 1 to make sure oracle just return 1 row, if we don’t use that, the hint can make everything very slow because oracle will be planning for just one row, but without filtering it’ll bring more (using the best plan possible for 1 row).
And UNION ALL instead of UNION, because there’s a huge difference between them. when you use UNION, oracle matches the sets of data to make sure you have unique rows after that. UNION ALL in other case, just bring everything each set return without any extra process to validate anything. UNION ALL is always faster than UNION.
In the end I have an query like this:
As you can see, the query is very simple and for this example I just had the name of the table there, then we know the table is not empty for that period. We can do other approach like summing then all together and validate if the results is = 3 for example or any other logic we need can be implemented on top of this query.
I hope this is helpful for you guys and see you in the next post.
DEV EPM 
February 26, 2020 at 3:24 pm
Personally, I’d use a few EXISTS clauses ANDed together. I think it might read better.
select ‘Proceed’
from dual
where exists (
select ‘found a row’
from dual
where 1 = 1
)
and exists (
select ‘found a row’
from dual
where 1 = 1
)
and exists (
select ‘found a row’
from dual
where 1 = 1
);
If any of those don’t return a row (change 1=1 to 1=2), then you get null, otherwise you get proceed. No UNION ALLs or rownum = 1 needed at all.