Re: Can a bitmap index be used for index-only access?

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: Steve.Tolkin@xxxxxxx
  • Date: Sat, 08 Nov 2008 21:52:12 -0600

Steve

 Bitmap index 'index only access' is supported.
Query 'select b from t where a='a1' ' can be satisfied by index range scan on that bitmap index, with out any need for table block access. Query 'select a from t where b='a1' ' also can be satisfied without any table block access. But in this case, full index must be scanned and CBO choices include Fast full scan/Index Full Scan. [ I don't think, skip scan is possible with bitmap index, at least, I have not seen it ].
Same is true for B-tree index also, just index access is enough for both cases of these statements. Access path choice is an optimizer's decision and so inn your b-tree case, it is possible that another access method is cheaper.. 

Cheers
Riyaj
blog: http://orainternals.wordpress.com

Tolkin, Steve wrote:
Can a bitmap index be used for index-only access? 
Suppose there is a single bitmap index defined on two columns A and B in
table T. Can that bitmap index alone be used for certain queries, without needing 
to read any blocks from the base table?
For example these two queries: Select B from T where A ='a1' 
Select A from T where B ='b2'

(An ordinary B-tree index on columns A and B, in that order, supports
index only access on the first query above, but not the second.) 
Where, if anywhere, is this described in the Oracle documentation?

Thanks,
Steve
--
Steve Tolkin Vice President PWI Strategy and Architecture Fidelity Investments 
400 Puritan Way M3B
Marlborough MA 01752

Notice:  All e-mail sent to or from Fidelity Investments is subject to
retention, monitoring and/or review by Fidelity personnel.
Please note that Fidelity is unable to accept orders left over voicemail
or email regarding any account.

The information in this e-mail and in any attachments is intended solely
for the attention and use of the named addressee(s) and may contain
information that is considered privileged, proprietary, confidential,
and/or exempt from disclosure under applicable law.  If you are not the
intended recipient of this email or if you have otherwise received this
email in error, please immediately notify me by replying to this message
or by telephone (you may call me collect).   Any use, dissemination,
distribution or copying of this e-mail is strictly prohibited without
authorization from Fidelity Investments.

Clearing, custody or other brokerage services may be provided by
Fidelity Brokerage Services LLC or National Financial Services LLC,
members NYSE, SIPC.
--
//www.freelists.org/webpage/oracle-l





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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2008