Hi,
I´m optimizing a query. After rewrite it, I look the explain plan, that you can see above.
Simplifing, the query is:
select *
from counterparty,
swap ,
future_contract ,
exch_comm_replica
(select --columns
from leg_revaluation,
deal_leg,
deal_master,
deal_structure,
business_entity,
leg_revaluation
where -- join and filter conditions
union all
select --columns
from position_revaluation,
exchange_position ,
account,
business_entity,
leg_revaluation
where --join and filter conditions)
Usually, in the explain plan output, the cost is accumulated, been greater and greater in the first levels. So, in the first line of the explain plan I have the total cost of the query. But, for my surprise, in this particular query the "final' cost is very low (59), but the costs for the inner selects are high.
How can I know the total cost?
Using Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6405 | 6817K| 59 |
|* 1 | HASH JOIN | | 6405 | 6817K| 59 |
| 2 | TABLE ACCESS FULL | COUNTERPARTY | 77 | 1232 | 2 |
|* 3 | HASH JOIN | | 6405 | 6717K| 54 |
| 4 | TABLE ACCESS FULL | EXCH_COMM_REPLICA | 21 | 420 | 2 |
|* 5 | HASH JOIN | | 6405 | 6592K| 49 |
|* 6 | HASH JOIN | | 1820 | 47320 | 8 |
| 7 | TABLE ACCESS FULL | FUTURE_CONTRACT | 1117 | 11170 | 3 |
| 8 | TABLE ACCESS FULL | SWAP | 1820 | 29120 | 4 |
| 9 | VIEW | | 6405 | 6430K| 15 |
| 10 | UNION-ALL | | | | |
|* 11 | HASH JOIN OUTER | | 301 | 96320 | 1965 |
| 12 | NESTED LOOPS ANTI | | 301 | 89397 | 1961 |
|* 13 | HASH JOIN | | 382 | 109K| 1961 |
| 14 | TABLE ACCESS FULL | BUSINESS_ENTITY | 8 | 120 | 2 |
|* 15 | HASH JOIN | | 382 | 104K| 1958 |
|* 16 | HASH JOIN | | 382 | 94736 | 1939 |
|* 17 | TABLE ACCESS FULL | LEG_REVALUATION | 383 | 77366 | 1919 |
| 18 | SORT AGGREGATE | | 1 | 7 | |
| 19 | INDEX FULL SCAN (MIN/MAX) | XAK1LEG_REVALUATION | 388K| 2653K| 3 |
| 20 | SORT AGGREGATE | | 1 | 7 | |
| 21 | INDEX FULL SCAN (MIN/MAX) | XAK1LEG_REVALUATION | 388K| 2653K| 3 |
| 22 | MERGE JOIN CARTESIAN | | 6694 | 300K| 17 |
| 23 | VIEW | | 1 | 9 | 3 |
| 24 | SORT AGGREGATE | | 1 | 7 | |
| 25 | INDEX FULL SCAN (MIN/MAX)| XAK1LEG_REVALUATION | 388K| 2653K| 3 |
|* 26 | TABLE ACCESS FULL | DEAL_LEG | 6694 | 241K| 14 |
| 27 | TABLE ACCESS FULL | DEAL_MASTER | 6392 | 193K| 15 |
|* 28 | INDEX RANGE SCAN | XIF1ACCOUNT | 3 | 9 | |
| 29 | TABLE ACCESS FULL | DEAL_STRUCTURE | 18 | 414 | 2 |
|* 30 | HASH JOIN | | 466 | 105K| 1847 |
| 31 | TABLE ACCESS FULL | BUSINESS_ENTITY | 8 | 120 | 2 |
|* 32 | HASH JOIN | | 466 | 98K| 1844 |
| 33 | TABLE ACCESS FULL | ACCOUNT | 13 | 221 | 2 |
| 34 | NESTED LOOPS | | 466 | 92734 | 1841 |
| 35 | NESTED LOOPS | | 466 | 71764 | 1375 |
| 36 | VIEW | | 1 | 9 | 3 |
| 37 | SORT AGGREGATE | | 1 | 7 | |
| 38 | INDEX FULL SCAN (MIN/MAX) | XAK1LEG_REVALUATION | 388K| 2653K| 3 |
|* 39 | TABLE ACCESS FULL | POSITION_REVALUATION | 466 | 67570 | 1372 |
| 40 | TABLE ACCESS BY INDEX ROWID | EXCHANGE_POSITION | 1 | 45 | 1 |
|* 41 | INDEX UNIQUE SCAN | XPKEXCHANGE_POSITION | 1 | | |
------------------------------------------------------------------------------------------------
Thanks in advance,
Miguel