| Function based index [message #57968] |
Wed, 23 July 2003 03:22  |
Ger Reuvekamp
Messages: 5
Registered: July 2003
|
Junior Member |
|
|
Hi there,
I'm using 8.1.7 and am having trouble getting the CBO to use a function based index.
I have altered my session to set the query rewrite parameters (true and trusted). Does anyone know whether these HAVE to be set system wide in init.ora?
Thanks,
Ger
|
|
|
|
| Re: Function based index [message #57969 is a reply to message #57968] |
Wed, 23 July 2003 04:39  |
Ger Reuvekamp
Messages: 5
Registered: July 2003
|
Junior Member |
|
|
Hmmm ... replying to my own posting ...
Anyways, thought I'd add some info:
As I said in my initial post I issued the following:
ALTER SESSION SET query_rewrite_enabled = TRUE;
ALTER SESSION SET query_rewrite_integrity = TRUSTED;
I have created the index as follows:
CREATE UNIQUE INDEX REFDATA_STA.FX_CLIENTS_TN_CLIENT_ID
ON REFDATA_STA.CLIENTS (TO_NUMBER(CLI_CLIENT_ID))
COMPUTE STATISTICS;
And executed the following:
EXEC dbms_stats.GATHER_TABLE_STATS('refdata_sta','clients');
EXEC dbms_stats.GATHER_INDEX_STATS('refdata_sta','fx_clients_tn_client_id');
But even when hinting Oracle to use the index, it won't:
SELECT /*+ choose index(clients fx_clients_tn_client_id) */ *
FROM clients
WHERE TO_NUMBER(CLI_CLIENT_ID) = 1
The table contains 65000+ rows.
Some other items that may be of interest:
init.ora
optimizer_goal = RULE
query_rewrite_enabled = FALSE
query_rewrite_integrity = enforced
The first I override by using the "choose" hint and the other 2 are overridden by the alter session command.
I have alse granted "QUERY REWRITE" privilege to the schema owner of the table.index as well as to the oracle user that actually creates and tries to use the FBI (don't know which SHOULD have it).
Oracle version used: 8.1.7.3.0 Enterprise Edition
I'm at a total loss here. Any help/suggestions greatly appreciated.
Ger
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
