Oracle-L: RE: SOME SOLUTIONS bitmap, SQL tuning

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Tue, 13 Aug 2002 07:38:32 -0800
Message-ID: <F001.004B2DD3.20020813073832@fatcity.com>

I finally got the following from the same index - regardless of exactly which columns were used in the index (except birthyear and mother's state always used). Had to use index hint because w/o bitmap merge would run and would take minutes or more - even with single bmp local indexes or bmp local indexes with birthyear + 1 other column. Regardless of size of bitmap_merge_... paramater related to pool for bitmapped merge indexes. Here are the speeds with the conc. index I ended up with on 5M row and 1.5 row table in inline view:

-for 3 year rolling average queries run between 3 - 12 secs depending on how
selective the where clause is - the more columns the better from your stored proc - not just any column of course.
-for discrete - 3 to 12 secs. depending on how selective the where clause is

-for 5 year rate - 3 secs - hmm I wonder?
-for single years about 3 secs or better like 5 year rate just less years.

Again, concatenated bitmap local prefixed index with histograms on uneven data distribution column performed better then just local prefix btree or simple bitmapped index in merge.

--

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 Tue Aug 13 2002 - 10:38:32 CDT