Tuesday, October 09, 2012

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. Once I had finished this test, I had a look on the Internet and found the URL below on OTN. It seems that an Oracle ACE called Justin Cave agrees with me so at least I am in good company!

No comments: