Function-based indexes and query rewrite

[millerg]

Can anyone explain the following:

-- Create two tables:
CREATE TABLE TEST1(
ID NUMBER,
TIMESTAMP DATE,
FLAG VARCHAR2(1))
;
create table TEST2(
ID NUMBER)
;

-- Analyze the tables
EXEC DBMS_STATS_GATHER_TABLE_STATS (USER,'test1');
EXEC DBMS_STATS_GATHER_TABLE_STATS (USER,'test2');

-- Create a materialized view on these tables:
CREATE MATERIALIZED VIEW testmv
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT test1.timestamp
FROM test1,test2
WHERE test1.id = test2.id;

-- Enable Auto Trace
SET AUTOT TRACE EXP

-- Execute the query.
SELECT test1.timestamp
FROM test1,test2
WHERE test1.id = test2.id;
-- Query rewrite is used

-- What happens if the query is not exactly the same?
SELECT test1.timestamp
FROM test1,test2
WHERE test1.id = test2.id
order by 1;
-- Query rewrite is still used

-- Create a function based index on a column THAT IS NOT IN THE MV or VIEW
CREATE INDEX test1_i
ON test1(nvl(flag,’A’));

-- Execute the query again.
SELECT test1.timestamp
FROM test1,test2
WHERE test1.id = test2.id
;
-- Query rewrite is used.

However, ANY deviation from the exact text, including
case-sensitivity, results in query-rewrite being abandoned.

I have tried creating stats on the index.

Oracle Release: 8.1.7.0.0

Anyone?

[sambavan]

Would be more appropriate in the development rather than in the administration forum. So moving it to development forum...

Sam

[culonbu]

We do not experience the problem in HP 11 8.1.7.1

SELECT
sum(ord_units),
e.YR_cal1_yr
,b.TV_type_variant_descr
,c.SA_sales_acct_descr
,d.RG_compy_reg_descr
FROM fact a,
product b,
customer c,
site d,
ttime e
WHERE a.DT_cal_date = e.DT_cal_date
AND a.PR_prod_no = b.PR_prod_no
AND a.CU_db_cust_no = c.CU_db_cust_no
AND a.DR_db_run_no = d.DR_db_run_no
GROUP BY
e.YR_cal1_yr
,b.TV_type_variant_descr
,c.SA_sales_acct_descr
,d.RG_compy_reg_descr

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89846724 Card=1 Byte
s=162)

1 0 SORT* (GROUP BY) (Cost=89846724 Card=1 Bytes=162) :Q206009
2 1 MERGE JOIN* (Cost=24504 Card=1358644193 Bytes=2201003592 :Q206008
66)

3 2 SORT* (JOIN) (Cost=21781 Card=666149 Bytes=87931668) :Q206008
4 3 HASH JOIN* (Cost=421 Card=666149 Bytes=87931668) :Q206007
5 4 HASH JOIN* (Cost=50 Card=1862 Bytes=158270) :Q206006
6 5 HASH JOIN* (Cost=8 Card=30 Bytes=1890) :Q206005
7 6 VIEW* (Cost=6 Card=37 Bytes=222) :Q206000
8 7 SORT (UNIQUE) (Cost=6 Card=37 Bytes=222)
9 8 TABLE ACCESS (FULL) OF 'TTIME' (Cost=2 Car
d=364 Bytes=2184)

10 6 TABLE ACCESS* (FULL) OF 'DWMV_INV_DAILY_WK_PRO :Q206004
_CUS_PRF' (Cost=1 Card=82 Bytes=4674) <<< QUERY RE-WRITTEN TO USE MVIEWS

**** ANOTHER VARIANCE OF QUERY
SQL> l
1 SELECT
2 SUM(ORD_UNITS), << UPPER CASE
3 e.YR_cal1_yr
4 ,c.SA_sales_acct_descr << swap these 2 cols
5 ,b.TV_type_variant_descr
6 ,d.RG_compy_reg_descr
7 FROM fact a,
8 product b,
9 customer c,
10 site d,
11 ttime e
12 WHERE a.DT_cal_date = e.DT_cal_date
13 AND a.PR_prod_no = b.PR_prod_no
14 AND a.CU_db_cust_no = c.CU_db_cust_no
15 AND a.DR_db_run_no = d.DR_db_run_no
16 GROUP BY
17 e.YR_cal1_yr
18 ,b.TV_type_variant_descr
19 ,c.SA_sales_acct_descr
20* ,d.RG_compy_reg_descr


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89846724 Card=1 Byte
s=162)

1 0 SORT* (GROUP BY) (Cost=89846724 Card=1 Bytes=162) :Q207009
2 1 MERGE JOIN* (Cost=24504 Card=1358644193 Bytes=2201003592 :Q207008
66)

3 2 SORT* (JOIN) (Cost=21781 Card=666149 Bytes=87931668) :Q207008
4 3 HASH JOIN* (Cost=421 Card=666149 Bytes=87931668) :Q207007
5 4 HASH JOIN* (Cost=50 Card=1862 Bytes=158270) :Q207006
6 5 HASH JOIN* (Cost=8 Card=30 Bytes=1890) :Q207005
7 6 VIEW* (Cost=6 Card=37 Bytes=222) :Q207000
8 7 SORT (UNIQUE) (Cost=6 Card=37 Bytes=222)
9 8 TABLE ACCESS (FULL) OF 'TTIME' (Cost=2 Car
d=364 Bytes=2184)

10 6 TABLE ACCESS* (FULL) OF 'DWMV_INV_DAILY_WK_PRO :Q207004
_CUS_PRF' (Cost=1 Card=82 Bytes=4674) <<< QUERY RE-WRITTEN TO USE MVIEWS