here is another way
select num_rows from user_tables where table_name ='xxx'
Does that rely on gathered stats? Returned value could be incorrect, I guess...
Mmmm... so is "SELECT 1 FROM TABLE WHERE ROWNUM=1;" the best way to do it?
Still thinking... why a pl/sql guru like Steven Feuerstein recommend a 'weird query' involving dual table, if the cost is greater? :S
I wouldn't say that there's a difference between the two.
There's almost no effort in the first query either.
A Cost of 4 versus a Cost of 2 doesn't really mean "2 times the effort" in this case.
BTW, both optimizations weren't available in earlier versions (say 8i and below ?)
Hemant K Chitale
Edited by: Hemant K Chitale on Apr 5, 2011 3:28 PM
Steven's dual trick is the fastest (and safe) way. You can see difference using "big" tables. ROWNUM condition will take much longer.
As already mentioned, you cannot just base it on cost, as the cost is not specifically a good indicator of the amount of work that will need to be done, it's just an internal figure calculated for that query and isn't really that comparible across different queries.
If you're intending to see if there is data in a table because you want to know whether to query it or not, you are actually better to just try and query the data and capture the NO_DATA_FOUND exception and handle that. In that case it requires no effort or cost up front.
I would be reluctant to mention names without a pointer to the actual source.
- one could truncate a table in a flash, your EXISTS or ROWNUM query could be just as incorrect as querying USER_TABLES
In the end I'd just stick to:
In short: I wouldn't bother to check.