Hello friends,

I apply all instructions from : HOW TO CREATE DIMENSION AND TEST QUERY REWRITE
and every thing works fine as expected.

NOW i add another table tno and second dimension tno_dim
to db and these are results i get from 8.1.6 and 8.1.7


SQL*Plus: Release 8.1.6.0.0 - Production on Thu Oct 11 09:19:04 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> CREATE TABLE TNO (
2 TNO VARCHAR2 (10),
3 PAR_TNO VARCHAR2 (10)
4 )
5 TABLESPACE USERS
6 ;

Table created.

SQL> insert into tno
2 (tno,par_tno)
3 values('AAAAAAAAAA','BBBBBBBB')
4 ;

1 row created.

SQL> commit;

Commit complete.

SQL> create dimension Tno_dim <<< second dimension tno_dim created
2 level tno is tno.tno
3 level par_tno is tno.par_tno
4 hierarchy tno_rollup (
5 tno child of
6 par_tno )
7 ;

Dimension created.


SQL> execute dbms_olap.validate_dimension('tno_dim','dwdba',FALSE,TRUE);

PL/SQL procedure successfully completed.

SQL> select * from mview$_exceptions;

no rows selected

SQL> set autotrace on
SQL> select t.quarter, sum(data) <<< 1 dimension time
2 from test,time t
3 where tdate=t.sdate
4 group by t.quarter
5 ;

QUARTER SUM(DATA)
---------- ----------
1 198686973
2 198912465
3 200589479
4 198393748


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=4 Bytes=120)
1 0 SORT (GROUP BY) (Cost=10 Card=4 Bytes=120)
2 1 HASH JOIN (Cost=7 Card=56 Bytes=1680)
3 2 VIEW (Cost=5 Card=34 Bytes=136)
4 3 SORT (UNIQUE) (Cost=5 Card=34 Bytes=136)
5 4 TABLE ACCESS (FULL) OF 'TIME' (Cost=1 Card=366 Byt
es=1464)

6 2 TABLE ACCESS (FULL) OF 'MV_TEST' (Cost=1 Card=164 Byte *** QUERY RE-WITTEN ***
s=4264)


1 select t.quarter, par_tno,sum(data)
2 from test test,time t <<< 2 dimensions time and tno
3 ,tno tno
4 where tdate=t.sdate
5 and test.tno=tno.tno
6* group by t.quarter,par_tno
SQL> /

QUARTER PAR_TNO SUM(DATA)
---------- ---------- ----------
1 BBBBBBBB 198686973
2 BBBBBBBB 198912465
3 BBBBBBBB 200589479
4 BBBBBBBB 198393748


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1334 Card=4 Bytes=20
8)

1 0 SORT (GROUP BY) (Cost=1334 Card=4 Bytes=208)
2 1 HASH JOIN (Cost=23 Card=304417 Bytes=15829684)
3 2 TABLE ACCESS (FULL) OF 'TIME' (Cost=1 Card=366 Bytes=3
294)

4 2 HASH JOIN (Cost=21 Card=83174 Bytes=3576482)
5 4 TABLE ACCESS (FULL) OF 'TNO' (Cost=1 Card=164 Bytes=
2296)

6 4 TABLE ACCESS (FULL) OF 'TEST' (Cost=19 Card=50716 By *** QUERY DOES NOT RE-WRITE ***
tes=1470764)

*******************************************************************


SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 11 09:29:03 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.1.0 - 64bit Production

SQL>

SQL> set autotrace on
SQL> select t.quarter, par_tno,sum(data)
2 from test test,time t <<< 2 dimensions time and tno
3 ,tno tno
4 where tdate=t.sdate
5 and test.tno=tno.tno
6 group by t.quarter,par_tno
7 ;

QUARTER PAR_TNO SUM(DATA)
---------- ---------- ----------
1 BBBBBBBB 198686973
2 BBBBBBBB 198912465
3 BBBBBBBB 200589479
4 BBBBBBBB 198393748


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=4 Bytes=204)
1 0 SORT (GROUP BY) (Cost=12 Card=4 Bytes=204)
2 1 HASH JOIN (Cost=9 Card=23 Bytes=1173)
3 2 HASH JOIN (Cost=7 Card=28 Bytes=1036)
4 3 VIEW (Cost=5 Card=34 Bytes=136)
5 4 SORT (UNIQUE) (Cost=5 Card=34 Bytes=136)
6 5 TABLE ACCESS (FULL) OF 'TIME' (Cost=1 Card=366 B
ytes=1464)

7 3 TABLE ACCESS (FULL) OF 'MV_TEST' (Cost=1 Card=82 Byt ** QUERY RE-WRITTEN IN REL 8.1.7 ****
es=2706)

8 2 TABLE ACCESS (FULL) OF 'TNO' (Cost=1 Card=82 Bytes=114
8)