QUERY_REWRITE_ENABLED

In some books and also in Oracle’s own documentation, it says that the QUERY_REWRITE_ENABLED initialisation parameter has to be set to TRUE before the optimiser will be able to use a function based index. I decided to check this for myself.

According to Oracle’s own documentation, the default for this parameter in Oracle 9 was FALSE. This does seem to be correct:

ORACLE 9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Tue Oct 9 16:57:46 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled'

  4  /

VALUE      ISDEFAULT

---------- ---------

false      TRUE

SQL>

Oracle’s own documentation also says that, in later versions, the default value is TRUE. This seems to be correct too:

ORACLE 10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 9 17:04:04 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> col value format a10

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'query_rewrite_enabled';

VALUE      ISDEFAULT

---------- ---------

TRUE       TRUE

SQL>

I did the rest of this test on Oracle 9.2.0.7.0. First I set the parameter to FALSE:

SQL> alter session

  2  set query_rewrite_enabled = false

  3  /

Session altered.

SQL>

Then I created a table with a function based index:

SQL> create table andrew

  2  as select * from dba_objects

  3  /

Table created.

SQL> create index fbi

  2  on andrew(to_char(created,'YYYYMM'))

  3  /

Index created.

SQL>

I checked that Oracle knew it was a function based index:

SQL> select index_type, funcidx_status

  2  from user_indexes

  3  where index_name = 'FBI'

  4  /

INDEX_TYPE             FUNCIDX_STATUS

---------------------- ---------------

FUNCTION-BASED NORMAL  ENABLED

SQL>

I monitored the usage of the index:

SQL> alter index fbi monitoring usage

  2  /

Index altered.

SQL>

Then I ran a query on the table. I did not expect Oracle to use the index, as QUERY_REWRITE_ENABLED was set to FALSE, but it did (you may need to use your browser's zoom button to read the execution plan):

SQL> set autotrace on explain

SQL> select count(*) from andrew

  2  where to_char(created,'YYYYMM') = '201201'

  3  /

  COUNT(*)

----------

       181

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=9)

   1    0   SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=1 Card=35

          1 Bytes=3159)

   

SQL> set autotrace off

SQL>

... and when I checked afterwards, Oracle confirmed that the index had been used:

SQL> select index_name, start_monitoring,

  2  monitoring, used from v$object_usage

  3  /

INDEX_NAME START_MONITORING    MONITORING USED

---------- ------------------- ---------- ----

FBI        10/09/2012 18:27:01 YES        YES

SQL>

So, on the basis of this test, I do not agree with the documentation.