Skip to Main Content

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Tom Kyte

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
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library