Re: function based indexes

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 13 Aug 2004 10:11:43 +0100
Message-ID: <7765c897040813021120b49a9e@mail.gmail.com>

On Thu, 12 Aug 2004 19:21:06 -0600, Tim Gorman <tim_at_sagelogix.com> wrote:

> The hoops to be jumped through to enable FBI are:
>
> 1. COMPATIBLE >= 8.1.0
> 2. OPTIMIZER_MODE <> rule
> 3. QUERY_REWRITE_ENABLED = true
> 4. QUERY REWRITE or GLOBAL QUERY REWRITE permission granted
> 5. CBO statistics gathered on the index

This is version specific and no longer true.

SYS 13-AUG-2004 09:59_at_inet>select grantee from dba_sys_privs 2 where privilege = 'QUERY REWRITE';

GRANTEE

DBA
SYS
XDB
MDSYS
WKSYS
CTXSYS 6 rows selected.

SYS 13-AUG-2004 10:00_at_inet>select name,value 2 from v$parameter
3 where name in ('compatible','optimizer_mode','query_rewrite_enabled');

NAME                           VALUE
------------------------------ ------------------------------
compatible                     9.2.0.0.0
optimizer_mode                 CHOOSE
query_rewrite_enabled          FALSE

3 rows selected.

SYS 13-AUG-2004 10:00_at_inet>CONN TACACS_at_INET Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE. 13-AUG-2004 10:01@>CONN TACACS_at_INET
Enter password:
Connected.
TACACS 13-AUG-2004 10:01_at_INET>CREATE TABLE EMP(ENO,ENAME) 2 AS SELECT OBJECT_ID,OBJECT_NAME FROM ALL_OBJECTS; Table created.

TACACS 13-AUG-2004 10:03_at_INET>CREATE INDEX IDX_UPPER_ENAME 2 ON EMP(UPPER(ENAME)); Index created.

TACACS 13-AUG-2004 10:03_at_INET>EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,'EMP',CASCADE => TRUE); PL/SQL procedure successfully completed.

TACACS 13-AUG-2004 10:04_at_INET>EXPLAIN PLAN 2 FOR SELECT ENO FROM EMP
3 WHERE UPPER(ENAME) = 'SEG$'; Explained.

TACACS 13-AUG-2004 10:04_at_INET>@SHOW_PLAN_9I PLAN_TABLE_OUTPUT

| Id  | Operation                   |  Name            | Rows  | Bytes | Cost (%

CPU)|

|   0 | SELECT STATEMENT            |                  |     2 |    62 |     4

PLAN_TABLE_OUTPUT

(25)|

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 62 | 4 (25)|

|* 2 | INDEX RANGE SCAN | IDX_UPPER_ENAME | 2 | | 2 (50)|

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2 - access(UPPER("EMP"."ENAME")='SEG$')

13 rows selected.

TACACS 13-AUG-2004 10:05_at_INET> And to show that TACACS is not a DBA -- I'm not showing you the dba accounts

TACACS 13-AUG-2004 10:08_at_INET>SELECT TABLE_NAME FROM DBA_TABLES; SELECT TABLE_NAME FROM DBA_TABLES

ERROR at line 1:
ORA-00942: table or view does not exist

This is 9204 std edition. I suspect, but haven't tested that compatible has to be set to 9.2 for this to work.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Aug 13 2004 - 04:07:23 CDT