Re: Query-rewrite-* params: still required to use FBI's?

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Tue, 3 Mar 2009 15:58:03 +0200
Message-ID: <6e49b6d00903030558q1b836a80l14140c28b978cd4f_at_mail.gmail.com>

As noone has answered I'l try to add my 2 cents. AFAIK it is not true anymore. And question is - where this info comes from "Oracle has always asked"?
At least I've tried with 10.2.0.1 and FBI works without these 2 params (see below). Yes if memory doesn't fail me in 8i it was necessary to set at least query_rewrite_enabled, but even for 9 it wasn't true anymore. So we can quite safely assume that both query_rewrite_enabled and query_rewrite_integrity returned only to materialized views where they truly belong :)

Gints Plivna
http://www.gplivna.eu

SQL> create table a (a number);
Table created.
SQL> create index a_idx on a (a + 1);
Index created.
SQL> insert into a values (1);
1 row created.
SQL> insert into a values (2);
1 row created.
SQL> exec dbms_stats.gather_table_stats(user, 'a', cascade=>true); PL/SQL procedure successfully completed. SQL> set autot on
SQL> alter session set query_rewrite_enabled = false; Session altered.
SQL> select * from a where a+1 = 2;

         A

---

         1
Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'A' (TABLE) (Cost=2 Card=

          1 Bytes=3)
   2    1     INDEX (RANGE SCAN) OF 'A_IDX' (INDEX) (Cost=1 Card=1)

Statistics

---

          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        511  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> alter session set query_rewrite_integrity = stale_tolerated; Session altered.
SQL> select * from a where a+1=3;

         A

---

         2
Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=3)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'A' (TABLE) (Cost=2 Card=

          1 Bytes=3)

   2 1 INDEX (RANGE SCAN) OF 'A_IDX' (INDEX) (Cost=1 Card=1) Statistics

---

          1  recursive calls
          2  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        419  bytes sent via SQL*Net to client
        511  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2009/3/2 FMHabash <fmhabash_at_gmail.com>:
> Oracle has always asked these 2 parameters be set to used function-based indexes (FBI's)...
> - Query-rewrite-enabled defaults to true.
> - query-rewrite-integrity defaults to enforced, requires 'trusted'.
>
> Does the later param need to be changed to 'trusted' for the CBO to used FBI. If yes, why is it defaulted to 'enforced' in the first place.
>
> I have reviewed db reference, admin, and concepts guides for 10g and there is  no mention of theses parameters in the FBI sections. Also, the ML docs addressing these 2 params are mostly old.
>
> Thank you
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 03 2009 - 07:58:03 CST