-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|