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

Analyze table is deprecated. Create optimizer statistics with DBMS_STATS.GATHER.SCHEMA_STATS. http://www.psoug.org/reference/dbms_stats.html

-- 
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