Oracle-L: RE: SOME SOLUTIONS!

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Wed, 07 Aug 2002 18:23:23 -0800
Message-ID: <F001.004AE745.20020807182323@fatcity.com>

Guys,

By changing objects to noparallel, creating bitmapped index containing all columns that was local and prefixed. Moving to 8.1.7.4 I was able to improve performance from minutes to many seconds to seconds for the following (not using 'in' or 'or' in predicate helped too): select
/*+ INDEX(mv_birthstat,ndx_mvyr) */
brth_mthr_res_newco_code County,count(*) from mv_birthstat
where cert_chld_brth_year between '1995' and '1999'

and brth_mthr_res_state_code='10'
and brth_mthr_age between 15 and 41
group by brth_mthr_res_newco_code;

Then adding the following the query takes 15 seconds again - any ideas for speeding it up? Please don't say hire a new DBA. I have really been working on this hard with only annoying input from Oracle's technical support and a lot of good reference manuals (Jonathon Lewis, 101 Perf. Tuning, Oracle SQL - Guy Harrison) for my companions. Great I know but lonely.

select
/*+ INDEX(mv_birthstat,ndx_mvyr) */
brth_mthr_res_newco_code County
sum(decode(greatest(cert_chld_birth_year,1995), least(cert_chld_birth_year,1997), 1, 0)) / 3 Num1, sum(decode(greatest(cert_chld_birth_year,1996), least(cert_chld_birth_year,1998), 1, 0)) / 3 Num2, sum(decode(greatest(cert_chld_birth_year,1997), least(cert_chld_birth_year,1999), 1, 0)) / 3 Num3 from mv_birthstat
where cert_chld_brth_year between '1995' and '1999'

and brth_mthr_res_state_code='10'
and brth_mthr_age between 15 and 41
group by brth_mthr_res_newco_code;

I am going to try the sums in a outer query.

-----Original Message-----
Sent: Wednesday, August 07, 2002 4:45 PM To: Stankus, Paula G; 'ORACLE-L_at_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 - 21:23:23 CDT