RE:partition table, local bitmapped index, inline view and BUG??!

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Mon, 05 Aug 2002 10:53:35 -0800
Message-ID: <F001.004AB806.20020805105335@fatcity.com>

Guys,

I have created a partition table using syntax like:

create materialized view mv_birthstat
PARTITION BY RANGE (year)
(
PARTITION mv_birthstat_1931 VALUES LESS THAN ('1931') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1932 VALUES LESS THAN ('1932') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1933 VALUES LESS THAN ('1933') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1934 VALUES LESS THAN ('1934') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1935 VALUES LESS THAN ('1935') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1936 VALUES LESS THAN ('1936') TABLESPACE MV_BIRTHSTAT_TO
1960,
PARTITION mv_birthstat_1937 VALUES LESS THAN ('1937') TABLESPACE MV_BIRTHSTAT_TO
1960
.........
etc.

As you notice I didn't use a different tablespace for each partition because we are using striping at I/O level and I have a ltd. number of volumes to work with so there wouldn't be bang for the buck. Also, in previous tests it appeared anyway that as long as the partitions were distinctly named the optimizer was using partition pruning/elimination.

I have local prefixed index:

containing

rem made local and not bitmapped due to reported bug. drop index &1;
CREATE BITMAP INDEX mv_test on mv_birthstat(year,state,co,age)

       LOCAL TABLESPACE X_LARGE PARALLEL(DEGREE 8); analyze index &1 compute statistics;
undefine 1;

Again, don't use separate. tablespaces in create index statement.

Have a view on top of this materialized view:

I noted that without the following hint in the view: CREATE VIEW v_birthstat
AS
select /*+ index_combine(mv_birthstat) no_expand(mv_birthstat) */ ......

It does not use the bitmapped local index or partition elimination and it takes a very very long time to run - with view just 7 secs. 50M rows - could be better but I can live with this.

Then with the following queries:

REM why partition range all vs. partition elimination? rem is range all actually faster?
rem best performance with index on yr,state,co,age and parallel degree set rem problem is hitting bug for some queries do to parallel and bitmap usage????
rem and partitioning.
rem index_combine hint!!!!
rem alter session set sort_area_size=222222222; rem alter session set hash_area_size=222222222; set timing on;
explain plan set statement_id='RON' for
select
MTHRESCHDCOUNTY County,
 sum(decode(greatest(BirthYear,1995), least(BirthYear,1997), 1, 0)) / 3 Num1,
 sum(decode(greatest(BirthYear,1996), least(BirthYear,1998), 1, 0)) / 3 Num2,
 sum(decode(greatest(BirthYear,1997), least(BirthYear,1999), 1, 0)) / 3 Num3 from V_BIRTHSTAT
where (BIRTHYEAR = 1995 or birthyear = 1996

       or birthyear =1997 or birthyear=1998 or birthyear= 1999 )
      and MTHRESSTATECODE = '10'
      and MTHAGE >= 15 and MTHAGE <=44

group by mthreschdcounty;

-Get explain plan and show no partition iterator or elimin. but does use bitmapped index and runs in 7 secs.

-Against 1.5M row table built the same way with view on top of partition table instead of mater. view and same hints and no state code perform. is less than a second.
with similar query.

Together using an inline view:
select b.county,Num1,Num2,Num3,Pop1,Pop2,Pop3 from
(select MTHRESCHDCOUNTY County,
 sum(decode(greatest(BirthYear,1995), least(BirthYear,1997), 1, 0)) / 3 Num1,
 sum(decode(greatest(BirthYear,1996), least(BirthYear,1998), 1, 0)) / 3 Num2,
 sum(decode(greatest(BirthYear,1997), least(BirthYear,1999), 1, 0)) / 3 Num3 from V_BIRTHSTAT
where BIRTHYEAR between 1995 and 1999

      and MTHAGE between 15 and 44
      and MTHRESSTATECODE = '10'

group by MTHRESCHDCOUNTY) b,
(Select TO_NUMBER(County_Code) County,
 sum(decode(greatest(REPT_YEAR,1995), least(REPT_YEAR,1997), POP_TOTAL, 0)) / 3
POP1,
 sum(decode(greatest(REPT_YEAR,1996), least(REPT_YEAR,1998), POP_TOTAL, 0)) / 3
POP2,
 sum(decode(greatest(REPT_YEAR,1997), least(REPT_YEAR,1999), POP_TOTAL, 0)) / 3
POP3
>From V_Population where REPT_YEAR between 1995 and 1999

      and SEX_CODE = '2'
      and RACE_CODE = 'T'
      and Age between 15 and 44

group by TO_NUMBER(County_Code)) p
where b.County = p.County
order by b.County

This query dies when parallelism set on table/index when use noparallel syntax then query runs but takes 151/2 seconds. and the explain plan still shows no partition elim.

Why is part. elimin. not chosen? 5 years out of 73?????all pretty evently distributed???
~
~

-- 
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 Mon Aug 05 2002 - 13:53:35 CDT