Re: function based indexes
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Aug 2004 10:11:43 +0100
On Thu, 12 Aug 2004 19:21:06 -0600, Tim Gorman <tim@xxxxxxxxxxxxx> 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@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@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@inet>CONN TACACS@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@INET
Enter password:
Connected.
TACACS 13-AUG-2004 10:01@INET>CREATE TABLE EMP(ENO,ENAME)
2 AS SELECT OBJECT_ID,OBJECT_NAME FROM ALL_OBJECTS;
Table created.
TACACS 13-AUG-2004 10:03@INET>CREATE INDEX IDX_UPPER_ENAME
2 ON EMP(UPPER(ENAME));
Index created.
TACACS 13-AUG-2004 10:03@INET>EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,'EMP',CASCADE => TRUE);
PL/SQL procedure successfully completed.
TACACS 13-AUG-2004 10:04@INET>EXPLAIN PLAN
2 FOR SELECT ENO FROM EMP
3 WHERE UPPER(ENAME) = 'SEG$';
Explained.
TACACS 13-AUG-2004 10:04@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@INET>
And to show that TACACS is not a DBA -- I'm not showing you the dba accounts
TACACS 13-AUG-2004 10:08@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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
