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

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