Re: Can a bitmap index be used for index-only access?
Date: Sat, 08 Nov 2008 21:52:12 -0600
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..
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?
> 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.