Oracle Enterprise Manager
Oracle SQL Explain Plan 13 Февраль 2008 г. 18:48:42 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 o.nl_ord_liab = '1'
      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
      AND b.code_balance = '1803'

Optimizer Mode Used:

   COST ALL ROWS (optimizer: CHOOSE)

Total Cost:

   467

Execution Steps:

Step # Step Name
27 SELECT STATEMENT
26 DWH.FCT_ACOUNT_SALTRAN TABLE ACCESS [BY INDEX ROWID]
25 NESTED LOOPS
23 NESTED LOOPS
20 NESTED LOOPS
17 HASH JOIN
14 DWH.CON_ASS_CONTR_ACC TABLE ACCESS [BY INDEX ROWID]
13 NESTED LOOPS
11 NESTED LOOPS
8 NESTED LOOPS
5 NESTED LOOPS
2 DWH.DET_ORD_LIAB TABLE ACCESS [BY INDEX ROWID]
1 DWH.DET_ORD_LIAB_NL INDEX [RANGE SCAN]
4 DWH.FCT_PRCST_CRED TABLE ACCESS [BY INDEX ROWID]
3 DWH.RELATION_27026_FK INDEX [RANGE SCAN]
7 DWH.DET_CONTRACTS TABLE ACCESS [BY INDEX ROWID]
6 DWH.PK_DET_CONTRACTS INDEX [UNIQUE SCAN]
10 DWH.DET_SOURCE TABLE ACCESS [BY INDEX ROWID]
9 DWH.PK_DET_SOURCE INDEX [UNIQUE SCAN]
12 DWH.RELATION_11430_FK INDEX [RANGE SCAN]
16 DWH.DET_TACC TABLE ACCESS [BY INDEX ROWID]
15 DWH.AK_TYPE_ACCOUNT INDEX [RANGE SCAN]
19 DWH.DET_ACCOUNT TABLE ACCESS [BY INDEX ROWID]
18 DWH.PK_DET_ACCOUNT INDEX [UNIQUE SCAN]
22 DWH.DET_BALANCE TABLE ACCESS [BY INDEX ROWID]
21 DWH.PK_DET_BALANCE INDEX [UNIQUE SCAN]
24 DWH.AK_ACCOUNT_SALTRAN INDEX [RANGE SCAN]

Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
  1    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index DET_ORD_LIAB_NL. 3 3 --
  2    This plan step retrieves rows from table DET_ORD_LIAB through ROWID(s) returned by an index. 6 3 0,073
  3    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index RELATION_27026_FK. 1 2 --
  4    This plan step retrieves rows from table FCT_PRCST_CRED through ROWID(s) returned by an index. 2 1 0,032
  5    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 12 3 0,17
  6    This plan step retrieves a single ROWID from the B*-tree index PK_DET_CONTRACTS. -- 1 --
  7    This plan step retrieves rows from table DET_CONTRACTS through ROWID(s) returned by an index. 1 1 0,031
  8    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 15 2 0,176
  9    This plan step retrieves a single ROWID from the B*-tree index PK_DET_SOURCE. -- 1 --
  10    This plan step retrieves rows from table DET_SOURCE through ROWID(s) returned by an index. 1 1 0,013
  11    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 17 1 0,101
  12    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index RELATION_11430_FK. 2 91 --
  13    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 408 27 3,586
  14    This plan step retrieves rows from table CON_ASS_CONTR_ACC through ROWID(s) returned by an index. 391 91 2,933
  15    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index AK_TYPE_ACCOUNT. 1 1 --
  16    This plan step retrieves rows from table DET_TACC through ROWID(s) returned by an index. 2 1 0,008
  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. 411 1 0,141
  18    This plan step retrieves a single ROWID from the B*-tree index PK_DET_ACCOUNT. 1 1 --
  19    This plan step retrieves rows from table DET_ACCOUNT through ROWID(s) returned by an index. 2 1 0,014
  20    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 413 1 0,154
  21    This plan step retrieves a single ROWID from the B*-tree index PK_DET_BALANCE. -- 1 --
  22    This plan step retrieves rows from table DET_BALANCE through ROWID(s) returned by an index. 1 1 0,011
  23    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 414 1 0,165
  24    This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index AK_ACCOUNT_SALTRAN. 2 50 --
  25    This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 467 1 0,191
  26    This plan step retrieves rows from table FCT_ACOUNT_SALTRAN through ROWID(s) returned by an index. 53 20 0,527
  27    This plan step designates this statement as a SELECT statement. 467 -- --