Thanks for the question, Anilkumar .
Asked: January 27, 2004 - 11:49 am UTC
Last updated: January 27, 2004 - 11:49 am UTC
Version: 8.1.6
Viewed 1000+ times
You Asked
Tom,
We are using 8i (8.1.6) enterprise edition as well as 9i (standard edition). I have created materialized views for summary columns & with the session settings for query_rewrite_enabled=true & query_rewrite_integrity=enforced, views are being accessed nicely when query to base tables are submitted by users for any summerised columns. Materialized views are also being accessed when a query contains a bind variable (&xx) for summary column.
But some of the sites and some of the magazines say that
'When a query is submitted to a base table with a bind variable embeded in it for any of summerised columns, then Oracle will not use 'Materialized View' & perform a full table scan as SQL parser will not rewrite the query used with a bind variable.'
'For the access of Materialized view, query with a bind variable should include hint such as ' /*+rewrite */ and then only SQL parser will rewrite query to access Materialized view.'
I do not find this being true in my case. Can you explain what is the meaning of the above. How far is it true ?
Anil Deshmukh
and Tom said...
they are obviously wrong. You have shown them wrong. You know them to be wrong. You need to ask them why they are wrong I guess...
simple proof that the statements are wrong:
big_table@ORA920PC> grant query rewrite to big_table;
Grant succeeded.
big_table@ORA920PC> alter session set query_rewrite_enabled=true;
Session altered.
big_table@ORA920PC> alter session set query_rewrite_integrity=enforced;
Session altered.
big_table@ORA920PC>
big_table@ORA920PC> create materialized view big_table_mv
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select status, count(*)
7 from big_table
8 group by status
9 /
Materialized view created.
big_table@ORA920PC> analyze table big_table_mv compute statistics;
Table analyzed.
big_table@ORA920PC>
big_table@ORA920PC> variable x varchar2(20);
big_table@ORA920PC> exec :x := 'VALID';
PL/SQL procedure successfully completed.
big_table@ORA920PC>
big_table@ORA920PC> set autotrace on
big_table@ORA920PC> select status, count(*)
2 from big_table
3 where status = :x
4 group by status;
STATUS COUNT(*)
------- ----------
VALID 3461488
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=11)
1 0 TABLE ACCESS (FULL) OF 'BIG_TABLE_MV' (Cost=2 Card=1 Bytes=11)
Statistics
----------------------------------------------------------
40 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
442 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
big_table@ORA920PC> set autotrace off
Is this answer out of date? If it is, please let us know via a Comment