Oracle explain plan:Cardinality returns a huge number but the query returns no records

Question

I have written a complex oracle sql query and the explain plan stats look like this: Cost: 209,201 Bytes:187,944,150 Cardinality: 409,675

Now the DBA tuned the query and the stats look like this: Cost: 42,996 Bytes: 89,874,138 Cardinality: 209,226

My first question is, if the numbers are lower, does it automatically mean better performance? Which number is the most pertient?Cost/Cardinality/Bytes? My second question is: I understand cardinality is the number of rows read. But when i run the query, it returns '0' rows ! My impression was that Cardinality has to be same for two queries that are supposed to return same result sets. This I guess is wrong?

Answer 1

Cost, bytes, cardinality... all are estimations according to inputs like statistics given to the optimizer. So they automatically mean nothing but can give an idea. In Oracle Performance Tuning Guide's words "It is best to use EXPLAIN PLAN to determine an access plan, and then later prove that it is the optimal plan through testing. When evaluating a plan, examine the statement's actual resource consumption."

For 2nd question: Theoretically equivalent queries should return same cardinality. Your tables' statictics may be old.