[#QA-31] How did Oracle compute the selectivity on index ?

<< Back to previous view

[QA-31] How did Oracle compute the selectivity on index ? Created: 15/Sep/07 Updated: 30/Sep/15
Status:	Closed
Project:	Questions & Answers
Fix Version/s:	None

Type:

Oracle - SQL Tuning

Priority:

Major

Reporter:

ubTools Support

Assignee:

ubTools Support

Resolution:

Answered

Votes:

File Attachments:

prod_ora_537_SELECT_PROD_I1_10053.trc

sqlt_s4880_prod_fsthqdr2_I1_main.zip

Product Version:

9.2.0.7.0

Operating System:

HP-UX

Operating System Version:

B.11.11

SQL_TEXT:

CREATE OR REPLACE VIEW IC_ITEM_INV_V
(ITEM_ID, LOT_NO, SUBLOT_NO, LOT_ID, LOT_STATUS,
LOT_CREATED, EXPIRE_DATE, QC_GRADE, WHSE_CODE, LOCATION,
LOCT_ONHAND, LOCT_ONHAND2, COMMIT_QTY, COMMIT_QTY2)
AS
SELECT l.item_id, l.lot_no, l.sublot_no, l.lot_id, s.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, b.whse_code, b.LOCATION,
          b.loct_onhand, b.loct_onhand2, 0, 0
     FROM ic_lots_mst l, ic_loct_inv b, ic_lots_sts s
    WHERE l.item_id = b.item_id
      AND l.inactive_ind = 0
      AND l.lot_id = b.lot_id
      AND b.lot_status = s.lot_status(+)
      AND NVL (s.order_proc_ind, 1) = 1
      AND NVL (s.rejected_ind, 0) = 0
      AND b.loct_onhand > 0
   UNION ALL
   SELECT /*+ INDEX(t IC_TRAN_PNDI1) */ t.item_id, l.lot_no, l.sublot_no, t.lot_id, t.lot_status,
          l.lot_created, l.expire_date, l.qc_grade, t.whse_code, t.LOCATION,
          0, 0, t.trans_qty commit_qty, t.trans_qty2 commit_qty2
     FROM ic_lots_mst l, ic_tran_pnd t, ic_item_mst i
    WHERE i.item_id = l.item_id
      AND i.item_id = t.item_id
      AND l.inactive_ind = 0
      AND t.lot_id = l.lot_id
      AND t.delete_mark = 0
      AND t.completed_ind = 0
      AND t.trans_qty < 0
/

SELECT SUM (loct_onhand), SUM (loct_onhand2), SUM (commit_qty),
         SUM (commit_qty2), SUM (loct_onhand) + SUM (commit_qty), lot_no,
         sublot_no, lot_id, lot_status, lot_created, LOCATION, expire_date,
         qc_grade
    FROM xtdba.ic_item_inv_v_sil x
   WHERE item_id = 5125
     AND whse_code = '350'
     AND loct_onhand >= 0
     AND expire_date >
                    TO_DATE ('06-SEP-2007, 11:59:59', 'DD-MON-YYYY, HH:MI:SS')
     AND lot_id > 0
     AND LOCATION <> 'NONE'
GROUP BY lot_no,
         sublot_no,
         lot_id,
         lot_status,
         lot_created,
         LOCATION,
         expire_date,
         qc_grade
  HAVING SUM (loct_onhand) + SUM (commit_qty) > 0
ORDER BY lot_created

Description

The customer wanted to know how Oracle computes the selectivity on index IC_TRAN_PNDI1. They're not sure if Oracle optimizer computes correct.

Comments

Comment by ubTools Support [ 15/Sep/07 11:11 AM ]

Event 10053 trace file.

Comment by ubTools Support [ 15/Sep/07 11:14 AM ]

SQLTXPLAIN report.

Comment by ubTools Support [ 15/Sep/07 11:15 AM ]

SQLTXPLAIN report.

Comment by ubTools Support [ 15/Sep/07 11:44 AM ]

BASE STATISTICAL INFORMATION

 
***********************
Table stats    Table: IC_TRAN_PND   Alias:  T
  (Using composite stats)
  TOTAL ::  CDN: 34357548  NBLKS:  737250  AVG_ROW_LEN:  143
-- Index stats
  INDEX NAME: IC_TRAN_PNDI1  COL#: 2 7 6 8 
    TOTAL ::  LVLS: 3   #LB: 341316  #DK: 113700  LB/K: 3  DB/K: 248  CLUF: 28283677
...
***********************

Definition of BASE STATISTICAL INFORMATION

CDN: Cardinality, number of rows.
NBLKS: Number of blocks.
AVG_ROW_LEN: Average row length.

COL#: Column numbers in order.
LVLS: Index depth.
#LB: Number of leaf blocks.
#DK: Number of distinct keys.
LB/K: Leaf blocks per key.
DB/K: Data bloks per key.
CLUF: Clustering factor.

SINGLE TABLE ACCESS PATH

Column: DELETE_MAR  Col#: 28     Table: IC_TRAN_PND   Alias:  T
    NDV: 3         NULLS: 0         DENS: 3.3333e-01 LO:  0  HI: 2
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: COMPLETED_  Col#: 24     Table: IC_TRAN_PND   Alias:  T
    NDV: 2         NULLS: 0         DENS: 5.0000e-01 LO:  0  HI: 1
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:  TRANS_QTY  Col#: 16     Table: IC_TRAN_PND   Alias:  T
    NDV: 62267     NULLS: 0         DENS: 1.6060e-05 LO:  -3116050  HI: 150907016871
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:    ITEM_ID  Col#: 2      Table: IC_TRAN_PND   Alias:  T
    NDV: 4527      NULLS: 0         DENS: 2.2090e-04 LO:  3  HI: 9816
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:  WHSE_CODE  Col#: 6      Table: IC_TRAN_PND   Alias:  T
    NDV: 105       NULLS: 0         DENS: 9.5238e-03
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:     LOT_ID  Col#: 7      Table: IC_TRAN_PND   Alias:  T
    NDV: 13443     NULLS: 0         DENS: 7.4388e-05 LO:  0  HI: 46635
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column:   LOCATION  Col#: 8      Table: IC_TRAN_PND   Alias:  T
    NDV: 31        NULLS: 0         DENS: 3.2258e-02
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: IC_TRAN_PND     ORIG CDN: 34357548  ROUNDED CDN: 1  CMPTD CDN: 0
...

Definition of SINGLE TABLE ACCESS PATH

NDV: Number of distinct values.
NULLS: Number of NULLs.
DENS: Density.
LO: Lowest value for numeric columns.
HI: Highest value for numeric columns.
...

Comment by ubTools Support [ 15/Sep/07 11:54 AM ]

According to the execation plan, these are the predicates:

Access Predicates:

T.ITEM_ID=5125
AND T.LOT_ID=L.LOT_ID
AND T.WHSE_CODE='350'

Filter Predicates:

B.ITEM_ID=T.ITEM_ID
AND T.LOT_ID>0
AND T.LOCATION<>'NONE'

Column order of IC_TRAN_PNDI1:

ITEM_ID
LOT_ID
WHSE_CODE
LOCATION

Comment by ubTools Support [ 15/Sep/07 12:12 PM ]

According to the execution plan, T.LOT_ID is joined with L.LOT_ID. That means T.LOT_ID gets values in the join. So, accessing the index consists of the following columns:

ITEM_ID
LOT_ID
WHSE_CODE

That's why the access predicates consist of these columns. T.LOCATION<>'NONE' is not included in access predicates. Because, <> can not be used accessing index.

After accessing index by access predicates, filter operation starts by filter predicates in order to eliminate rows on index without going to table. Additionally, T.LOCATION<>'NONE' is used in filter predicates to filter index keys on index.

Comment by ubTools Support [ 15/Sep/07 12:59 PM ]

Note: Since there is no NULL/histogram in our IC_TRAN_PNDI1 index columns and all predicates are ANDed, we did not cover other situations for selectivity computations.

Selectivity of access predicates:

Column	Operation	Formula	Value
ITEM_ID	=	1/NDV=DENS	2.2090e-04
LOT_ID	=	1/NDV=DENS	7.4388e-05
WHSE_CODE	=	1/NDV=DENS	9.5238e-03

Since the columns are ANDed, combined selectivity means:

= Sel(ITEM_ID)*Sel(LOT_ID)*Sel(WHSE_CODE)
= 2.2090e-04*7.4388e-05*9.5238e-03
= 1.5649e-10

Selectivity of filter predicates:

After accessing the index, filter operation will start. In our case, access predicates will also be used in filter operation to eliminate rows in the index. Because, their values are known, and can be used in filter operation.

But, their selectivity will not be re-computed, since they are already computed to access the index. So, T.LOT_ID>0 in filter predicates doesn't make sense even if its operation is not an equal operation as in access predicates.

Column	Operation	Formula	Value
LOCATION	<>	1-(1/NDV=DENS)	1-3.2258e-02=0.967742

Since the columns are ANDed, combined selectivity means:

= Sel(ITEM_ID)*Sel(LOT_ID)*Sel(WHSE_CODE)*Sel(LOCATION)
= 2.2090e-04*7.4388e-05*9.5238e-03*0.967742
= 1.5144e-10

Comment by ubTools Support [ 15/Sep/07 01:33 PM ]

Interpreting Event 10053 trace file is need to see if optimizer computation and ours match.

Final cost at the bottom:

Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 29  CDN: 2  RSC: 28  RSP: 28  BYTES: 308
  IO-RSC: 28  IO-RSP: 28  CPU-RSC: 0  CPU-RSP: 0

The final cost is 29.

Going backward lines to break down the final cost of 29:

 
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: IC_ITEM_INV_V_SIL   Alias:  X
  TOTAL ::  (NOT ANALYZED)    CDN: 0  NBLKS:  0  AVG_ROW_LEN:  0
_OPTIMIZER_PERCENT_PARALLEL = 0
  BEST_CST: 13.00  PATH: 2  Degree:  1

The cost of IC_ITEM_INV_V_SIL is 13.

 
GENERAL PLANS
***********************
Join order[1]:  IC_ITEM_INV_V_SIL[X]#0
GROUP BY sort
GROUP BY cardinality:  1, TABLE cardinality:  2
HAVING selectivity:  5.0000e-02  -> GROUPS:  1
    SORT resource      Sort statistics
      Sort width:          299 Area size:     1048576 Max Area size:   104857600   Degree: 1
      Blocks to Sort:        1 Row size:          180 Rows:          2
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         30
      Total IO sort cost: 16
      Total CPU sort cost: 0
      Total Temp space used: 0
Best so far: TABLE#: 0  CST:         29  CDN:          2  BYTES:        308
    SORT resource      Sort statistics
      Sort width:          299 Area size:     1048576 Max Area size:   104857600   Degree: 1
      Blocks to Sort:        1 Row size:          180 Rows:          2
      Initial runs:          1 Merge passes:        1 IO Cost / pass:         30
      Total IO sort cost: 16
      Total CPU sort cost: 0
      Total Temp space used: 0
..

The cost of sorting IC_ITEM_INV_V_SIL is 16.

Total cost (29) = Accessing IC_ITEM_INV_V_SIL (13) + Sorting IC_ITEM_INV_V_SIL (16)

Going backward lines to break down the cost of 13:

Join result: cost: 7  cdn: 1  rcz: 98
Best so far: TABLE#: 0  CST:          1  CDN:          1  BYTES:          4
Best so far: TABLE#: 1  CST:          1  CDN:          1  BYTES:         11
Best so far: TABLE#: 3  CST:          3  CDN:          1  BYTES:         65
Best so far: TABLE#: 2  CST:          7  CDN:          1  BYTES:         98
Final - All Rows Plan:
  JOIN ORDER: 2
  CST: 7  CDN: 1  RSC: 7  RSP: 7  BYTES: 98
  IO-RSC: 7  IO-RSP: 7  CPU-RSC: 0  CPU-RSP: 0

JOIN ORDER: 2 is selected with the cost of 7.

Going backward lines to break down the cost of 7:

Join order[2]:  IC_ITEM_MST_B[B]#0  IC_ITEM_MST_TL[T]#1  IC_LOTS_MST[L]#3  IC_TRAN_PND[T]#2
...
Now joining: IC_TRAN_PND[T]#2 *******
NL Join
  Outer table: cost: 3  cdn: 1  rcz: 65  resp:  3
  Access path: index (scan)
      Index: IC_TRAN_PNDI1
  TABLE: IC_TRAN_PND
      RSC_CPU: 0   RSC_IO: 4
  IX_SEL:  1.5650e-10  TB_SEL:  1.5144e-10
    Join:  resc: 7  resp: 7
  Best NL cost: 7  resp: 7

Our index IC_TRAN_PNDI1 appears here. So, here is the stop point for our case.

Here is the selectivity comparison table which includes Oracle-computed selectivity values and manually computed selectivity values.

	Oracle	Manual
IX_SEL(Access predicates)	1.5650e-10	1.5649e-10
TB_SEL(Access+Filter Predicates)	1.5144e-10	1.5144e-10

No computation errors found.

Comment by ubTools Support [ 30/Sep/15 02:32 PM ]

"Cost Based Oracle: Fundamentals" book of Jonathan Lewis was used in the calculations above.

Generated at Thu Oct 16 05:45:19 UTC 2025 using JIRA Standard Edition, Version: 3.12.3-#302.

[QA-31] How did Oracle compute the selectivity on index ? Created: 15/Sep/07 Updated: 30/Sep/15