DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=mime-version:in-reply-to:references:date:message-id:subject:from:to         :cc:content-type:content-transfer-encoding;        b=Gozf2SdcQ/terk2VU3flvR+78VZploGwBRXjiP32P4zMj8AK1OaaulSZ3D1Od1DMXi         nxPgGUsEjcG14adBTMwZy7lpPFrb9gqEXt1Ge+Q8CsNepgz2AOAnGXv13sxN1TozI0j3         oFo6hSosqUP7Qo/zCJq/9eNKY7x8GpUtEfmq8=
MIME-Version: 1.0
In-Reply-To: <49ac51e9.190c660a.6024.2c7a@mx.google.com>
References: <49ac51e9.190c660a.6024.2c7a@mx.google.com>
Date: Tue, 3 Mar 2009 15:58:03 +0200
Message-ID: <6e49b6d00903030558q1b836a80l14140c28b978cd4f@mail.gmail.com>
Subject: Re: Query-rewrite-* params: still required to use FBI's?
From: Gints Plivna <gints.plivna@gmail.com>
To: fmhabash@gmail.com
Cc: Oracle-L Group <oracle-l@freelists.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: gints.plivna@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@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