Oracle-L: RE: bitmapped indexes, partition elimination, perform!!!

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Wed, 07 Aug 2002 14:16:33 -0800
Message-ID: <F001.004AE2A1.20020807141633@fatcity.com>

Mother's state 80% are in Florida. However, this is a 'local' prefixed bitmap index. I would expect to use year to eliminate partion. then w/in year by state - second col. in query.

-----Original Message-----
Sent: Wednesday, August 07, 2002 4:35 PM To: Stankus, Paula G; 'ORACLE-L_at_fatcity.com'

BTW, SQL> select blocks from dba_tables
2 where table_name = 'MV_BIRTHSTAT';

BLOCKS

236542

SQL> select clustering_factor from user_indexes 2 where table_name='MV_BIRTHSTAT';

CLUSTERING_FACTOR

             1657
              170

-----Original Message-----
Sent: Wednesday, August 07, 2002 4:28 PM To: Stankus, Paula G; 'ORACLE-L_at_fatcity.com'

Help - weird performance problem!!!

| SELECT STATEMENT | | 1 | 5 | 9331 | |
|
| SORT AGGREGATE | | 1 | 5 | | |
|
| SORT AGGREGATE | | 1 | 5 | | |
|
| INLIST ITERATOR | | | | | |
|
| PARTITION RANGE ITERAT| | | | |KEY(I)
|KEY(I) |
| BITMAP CONVERSION COU| | | | | |
|
| BITMAP INDEX RANGE S|NDX_MVYRS | | | |KEY(I)
|KEY(I) |
Cost higher but less than a second.

Ignore the cost – higher cost was sign. Faster!!!!!Does Key(*) mean it is doing part. Elimin. I think so but can you see the inlist?

Very fast with all years involved. Then added predicate:

select
/*+ INDEX(mv_birthstat,ndx_mvyrstatecoage) */ count(*) from mv_birthstat
where cert_chld_brth_year in('1995','1996','1997','1998','1999')

and brth_MTHR_res_STATE_CODE = '10';

snails crawl by just adding the additional column!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Paula_Stankus_at_doh.state.fl.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 07 2002 - 17:16:33 CDT