Any database with a cost-based optimizer (which includes basically any enterprise-class database) would generally treat those two queries as identical. But no cost-based optimizer is or can be perfect-- they will all occasionally pick a poor plan.
If you're using a cost-based optimizer and the optimizer has reasonably accurate information about the tables and indexes (this information is generally gathered asynchronously and generally gathered in the evening/ morning when other activity is relatively light), it should be relatively clear (for some values of
bigtable and assuming proper indexes) that the most efficient plan is to do a full table scan of
smalltable and then do index lookups on
bigtable for those rows using the join condition. If the database thinks that
smalltable has 10% of the rows in
bigtable rather than 0.1% of the rows in
bigtable, on the other hand, there are a lot of different plans that have to be considered. As the number of tables, the number of predicates, and the number of access paths the database supports grows, the potential number of query plans grows exponentially. But the time the optimizer has to find the best plan does not grow-- you're generally going to be upset if the database takes a couple minutes to figure out the best plan before it even starts to execute the query. So cost-based optimizers will never be perfect-- they don't have the time to fully explore every path and they rely on statistics that may be stale in order to figure out which plans are optimal. Most of the time, however, they're quite good at figuring out the best way to join two tables when a query has no predicates and a simple join condition.