As always - "it depends"
Comparing costs across queries can be quite tricky. You have to presume/assume the estimated card= values are absolutely dead on (major contributor to the cost). You have to assume that the cpu costing was perfect. Within a set of plans for the same query - we can compare the costs because these things are sort of uniform - the same - for those plans. But change the syntax and the optimizer might well estimate a card= value radically different - causing the cost to be influenced (negatively or postively). If the rewrite resulted in a better set of estimated cardinalities - the plan cost might well go up (to reflect more rows perhaps) - but the plan would probably be better (because the better the estimated card=values, the better the plan in general)
Consider this example:
ops$tkyte%ORA11GR2> create table t
2 as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
3 decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
4 from all_objects a
5 where rownum <= 50000
6 /
Table created.
ops$tkyte%ORA11GR2> create index t_idx on t(flag1,flag2);
Index created.
ops$tkyte%ORA11GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=>'for all indexed columns size 254' );
5 end;
6 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where flag1='N' and flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12499 | 1232K| 208 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 12499 | 1232K| 208 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FLAG2"='N' AND "FLAG1"='N')
ops$tkyte%ORA11GR2> select /*+ index(t t_idx) */ * from t where flag1='N' and flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12499 | 1232K| 397 (0)| 00:00:05 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 12499 | 1232K| 397 (0)| 00:00:05 |
|* 2 | INDEX RANGE SCAN | T_IDX | 12499 | | 27 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')
Now, you know and I know that the second plan there is better - we know that because WE KNOW that flag1=n and flag2=n never happens - but the optimizer doesn't (yet). The optimizer knows that flag1=n is 50% of the data, flag2=n is 50% of the data and it assumes (using high school statistics) that flag1=n and flag2=n = 50%*50% = 25% of the data (12,499 is about 25% of 50,000)
So, let's fix the card= value and re-evaluate
ops$tkyte%ORA11GR2> select /*+ dynamic_sampling(t 3) */ * from t where flag1='N' and flag2='N';
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 404 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 404 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FLAG1"='N' AND "FLAG2"='N')
Now the cost is much lower and we can see this is the right plan. But, in the first run through, the full scan would have been the "right plan" given the cost compared to the index range scan.
It still comes back to "are the estimated cardinalities close to reality"
If they are not, the cost is going to be thrown off.
If they are - for all of the queries you compare - then they are probably comparable (assuming the cpu costing is good as well)