Re: Bitmap or B-Tree indexes...?

Aftermath Fan wrote:
> Sorry, I'm a moron. Actually, I'm not a moron, I'm just a junior DBA
> trying to learn.
>
> It's 10.2.0.2 on Linux, with a clone on Windows x64. At the moment,
> it's running with compatible = 10.2.0.1.0, though that could be changed
> if there was a need (side note: is there really a security need to
> prevent ordinary users from 'show parameter compatible'? Presumably,
> wiser minds have concluded there is.)
>
> The data is loaded, indexed, analyzed, and then never updated again for
> the foreseeable future (it's a reference set of data - there's another
> set of tables where all the work is done).
>
> I will have to look at DBMS_XPLAN. Running with set autotrace on exp
> reveals:
>
> | Id | Operation | Name | Rows
> | Bytes
> | Cost (%CPU)| Time | Pstart| Pstop |
>
> --------------------------------------------------------------------------------
> ------------------------------------------
>
> | 0 | SELECT STATEMENT | |
> 1 | 14
> | 4 (0)| 00:00:01 | | |
>
> | 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| MAINLIST |
> 1 | 14
> | 4 (0)| 00:00:01 | 1 | 1 |
>
> |* 2 | INDEX UNIQUE SCAN | MAINLIST_id1_id2_PK |
> 1 |
> | 3 (0)| 00:00:01 | | |
>
> --------------------------------------------------------------------------------
> ------------------------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("id1"=1 AND "id2"=1488844)
>
> This is after creating a bitmap index on id1, id2. I notice Oracle is
> nice enough to rewrite the query if someone calls it with id2, id1 (so
> I don't need a separate index for that). On a 3Ghz server with 2GB of
> RAM and 1GB SGA, having done an ANALYZE TABLE MAINLIST COMPUTE
> STATISTICS, the response is almost instant.
>
> I can experiment here, but in general...am I likely to improve the cost
> with a regular index? Another common query is to select avg(),
> count(), stddev(), median(), etc. by id1 and id2 (yes, I am exploring
> pre-computing all these stats in another table).
>
> Thanks!
>
>
>
>
> DA Morgan wrote:
> > Aftermath Fan wrote:
> > > Hi,
> > >
> > > I have a table with about 120 million rows. I'd like to index two
> > > columns. Column id1 has about 15,000 distinct values; column id2 has
> > > about 600,000. Most of the queries are:
> > >
> > > select <something> from <table> where id1 = ?
> > > select <something> from <table> where id2 = ?
> > > select <something> from <table> where id1 = ? and id2 = ?
> > >
> > > I'm thinking for id1, the column with 15,000 values, a bitmap index
> > > would be appropriate given its low relative cardinality. And for id2
> > > and (id1,id2) a B-tree index.
> > >
> > > Just looking for a sanity check...thanks.
> >
> > I'm thinking an explain plan created with DBMS_XPLAN would be the first
> > step. Well that and a version number. ;-)
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org

I think the idea is that parameter information in general could be exploited if publicly available.

The point of bitmap indexes is *not* simply that they handle low cardinality columns, it's that they can be combined efficiently when querying *multiple* low-cardinality columns. You would create separate bitmap indexes on ID1, ID2 and ID3, then for queries like "id3=42 AND id2=1488844" the database can derive the rowids of matching rows by performing BITAND operations on the relevant bitmaps. So if the queries were always of the form

SELECT ... WHERE id1 = 123;
SELECT ... WHERE id2 = 456;
SELECT ... WHERE id3 = 789;

then I would not expect any significant advantage with bitmap indexes (although since the data is static I guess it won't hurt). However if the different columns are queried in arbitrary combinations as in your final example then yes this could be a good use for multiple bitmap indexes.

btw,
> am I likely to improve the cost

You mean improve the performance ;) Received on Sun Jan 14 2007 - 03:45:40 CST