Oracle Enterprise Manager
Oracle SQL Explain Plan 13 Февраль 2008 г. 18:50:40 GET 

Target:

    DWHA

 

Version: Oracle 9.2.0.6.0

 

Database: DWHA

 

Schema: DWH

 

Date: 13.02.2008 0:00:00


SQL Statement:

   
SELECT to_date('02.02.2008', 'DD.MM.YYYY') AS dt, cc.id_contracts, b.id_balance, 
       s.saldo, c.num_contracts
    FROM fct_prcst_cred p, det_account a, det_balance b, fct_acount_saltran s, 
         det_contracts c, con_ass_contr_acc cc, det_ord_liab o, det_tacc t, 
         det_source sr
    WHERE a.id_account = s.id_account
      AND a.id_balance = b.id_balance
      AND a.id_account = cc.id_account
      AND c.id_contracts = cc.id_contracts
      AND cc.id_tacc = t.id_tacc
      AND c.id_source = sr.id_source
      AND c.id_contracts = p.id_contracts
      AND p.id_ord_liab = o.id_ord_liab
      AND p.id_tacc = 61280
      AND to_date('02.02.2008', 'DD.MM.YYYY') BETWEEN p.dt_open AND p.dt_close
      AND to_date('02.02.2008', 'DD.MM.YYYY') BETWEEN s.dt_open AND s.dt_close
      AND to_date('02.02.2008', 'DD.MM.YYYY') BETWEEN cc.dt_open AND 
          cc.dt_close
      AND sr.code_source = 'Loans'
      AND NOT c.code_contracts LIKE '%gar%'
      AND substr(b.code_balance, 1, 1) IN ('1', '2')
      AND t.code_tacc = '1'
      AND a.is_gk != '1'
      AND s.saldo <> 0

Optimizer Mode Used:

   COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

   17 925

Execution Steps:

Step # Step Name
20 SELECT STATEMENT
19 HASH JOIN
17 HASH JOIN
1 DWH.FCT_PRCST_CRED TABLE ACCESS [FULL]
16 HASH JOIN
3 DWH.DET_SOURCE TABLE ACCESS [BY INDEX ROWID]
2 DWH.AK_SOURCE INDEX [RANGE SCAN]
15 HASH JOIN
4 DWH.DET_CONTRACTS TABLE ACCESS [FULL]
14 HASH JOIN
12 HASH JOIN
5 DWH.DET_BALANCE TABLE ACCESS [FULL]
11 HASH JOIN
6 DWH.DET_ACCOUNT TABLE ACCESS [FULL]
10 HASH JOIN
8 DWH.DET_TACC TABLE ACCESS [BY INDEX ROWID]
7 DWH.AK_TYPE_ACCOUNT INDEX [RANGE SCAN]
9 DWH.CON_ASS_CONTR_ACC TABLE ACCESS [FULL]
13 DWH.FCT_ACOUNT_SALTRAN TABLE ACCESS [FULL]
18 DWH.PK_DET_ORD_LIAB INDEX [FAST FULL SCAN]

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
  1    This plan step retrieves all rows from table FCT_PRCST_CRED. 180 22 519 725,71
  2    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index AK_SOURCE. 1 1 --
  3    This plan step retrieves rows from table DET_SOURCE through ROWID(s) returned by an index. 2 1 0,013
  4    This plan step retrieves all rows from table DET_CONTRACTS. 813 11 478 358,688
  5    This plan step retrieves all rows from table DET_BALANCE. 6 37 0,397
  6    This plan step retrieves all rows from table DET_ACCOUNT. 4 850 666 630 9 114,082
  7    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index AK_TYPE_ACCOUNT. 1 1 --
  8    This plan step retrieves rows from table DET_TACC through ROWID(s) returned by an index. 2 1 0,008
  9    This plan step retrieves all rows from table CON_ASS_CONTR_ACC. 8 787 9 363 850 301 764,697
  10    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 8 962 374 554 14 996,791
  11    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 14 724 374 554 20 117,646
  12    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 14 741 31 721 2 044,518
  13    This plan step retrieves all rows from table FCT_ACOUNT_SALTRAN. 2 081 13 178 257 347 473,573
  14    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 16 822 627 074 56 951,057
  15    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 17 635 69 813 8 522,095
  16    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 17 637 13 963 1 881,732
  17    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 17 817 14 957 2 497,702
  18    This plan step retrieves all of the ROWIDs of B*-tree index PK_DET_ORD_LIAB by sequentially scanning the leaf nodes. 108 463 460 2 715,586
  19    This plan step accepts two sets of rows, each from a different table. A hash table is built using the rows returned by the first child. Each row returned by the second child is then used to probe the hash table to find row pairs which satisfy a condition specified in the query's WHERE clause. Note: The Oracle cost-based optimizer will build the hash table using what it thinks is the smaller of the two tables. It uses the statistics to determine which is smaller, so out of date statistics could cause the optimizer to make the wrong choice. 17 925 14 957 2 585,341
  20    This plan step designates this statement as a SELECT statement. 17 925 -- --