BITMAP index cost 10053 trace

From: Barr, Stephen <Stephen.Barr_at_BSkyB.com>
Date: Tue, 24 May 2005 09:33:35 +0100
Message-ID: <4127F81F6CAFC245A18BC49054EFB06301D6E0C6@ssslexchusr6.sssl.bskyb.com>

I'm trying to resolve how the CBO has come up with the cost of 9131 in the 10053 extract below.  

If the formula is blevel + FF*leaf_blocks + FF*clustering_factor then the cost comes out as 11 - how does it then jump to 9131? I'm assuming the cost calculationfor bitmap indexes is very different?    

---

BASE STATISTICAL INFORMATION

---

Table stats Table: AF_TEST Alias: AF_TEST

  TOTAL :: CDN: 962324 NBLKS: 21041 AVG_ROW_LEN: 100

• Index stats

  INDEX NAME: BI_AF_TEST COL#: 3     TOTAL :: LVLS: 1 #LB: 14 #DK: 4 LB/K: 3 DB/K: 7 CLUF: 28 _OPTIMIZER_PERCENT_PARALLEL = 0

---

SINGLE TABLE ACCESS PATH Column: C Col#: 3 Table: AF_TEST Alias: AF_TEST

    NDV: 4 NULLS: 0 DENS: 2.5000e-01

    NO HISTOGRAM: #BKT: 1 #VAL: 2   TABLE: AF_TEST ORIG CDN: 962324 ROUNDED CDN: 240581 CMPTD CDN: 240581

  Access path: tsc Resc: 3195 Resp: 3195

  Access path: index (equal)

      Index: BI_AF_TEST

  TABLE: AF_TEST       RSC_CPU: 0 RSC_IO: 4   IX_SEL: 2.5000e-01 TB_SEL: 2.5000e-01

• Bitmap access path accepted ********

Cost: 9131 Cost_io: 9131 Cost_cpu: 0.000000 Selectivity: 0.250000

Not believed to be index-only.

  BEST_CST: 9130.56 PATH: 20 Degree: 1

---

Information in this email may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 24 2005 - 04:40:11 CDT