Oracle 8i and function-based indexes 
Author Message
 Oracle 8i and function-based indexes

Greetings,

Has anyone been able to get the Oracle 8i (8.1.6) optimizer to use (as
verified in the explain plan) a function-based index *without*
explicitly specifying a hint in the SELECT clause of the SQL
statement?  Perhaps I'm missing some additional setup parameters - any
help is much appreciated.

Background info:
-8i (8.1.6) on NT4.0 server
-set query_rewrite_enabled to TRUE
-using cost-based optimization
-have *not* ANALYZEd table with function-based index (problem here???)
-successfully created index

Steve Santandera

Sent via Deja.com http://www.***.com/
Before you buy.



Sat, 19 Apr 2003 00:58:15 GMT
 Oracle 8i and function-based indexes

Quote:

> Greetings,

> Has anyone been able to get the Oracle 8i (8.1.6) optimizer to use (as
> verified in the explain plan) a function-based index *without*
> explicitly specifying a hint in the SELECT clause of the SQL
> statement?  Perhaps I'm missing some additional setup parameters - any
> help is much appreciated.

> Background info:
> -8i (8.1.6) on NT4.0 server
> -set query_rewrite_enabled to TRUE
> -using cost-based optimization
> -have *not* ANALYZEd table with function-based index (problem here???)
> -successfully created index

> Steve Santandera

> Sent via Deja.com http://www.deja.com/
> Before you buy.

func based indexes are only active under cost optimiser....

No analyze = no function index.

HTH
--
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Early to bed and early to rise,
 makes a man healthy, wealthy and wise." - some dead guy



Fri, 18 Apr 2003 21:09:48 GMT
 Oracle 8i and function-based indexes
I don't have 8i yet, but my understanding is that you MUST analyze the
table in order for this to work.

Good luck.

Tom Sullivan


Quote:

> Greetings,

> Has anyone been able to get the Oracle 8i (8.1.6) optimizer to use (as
> verified in the explain plan) a function-based index *without*
> explicitly specifying a hint in the SELECT clause of the SQL
> statement?  Perhaps I'm missing some additional setup parameters - any
> help is much appreciated.

> Background info:
> -8i (8.1.6) on NT4.0 server
> -set query_rewrite_enabled to TRUE
> -using cost-based optimization
> -have *not* ANALYZEd table with function-based index (problem here???)
> -successfully created index

> Steve Santandera

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Tom Sullivan
ADI Consulting, Inc.

--
Tom Sullivan
ADI Consulting, Inc.

Sent via Deja.com http://www.deja.com/
Before you buy.



Sat, 19 Apr 2003 04:30:58 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Function-based indexes in Oracle 8i

2. Function based indexes and index fast full scan

3. ORA-01031 with Create Index for a Function Based Index

4. Function Based Index in Oracle 8

5. Error while Creating function based index in Oracle 8

6. Oracle user defined Function based index and Ora-4020

7. oracle function based index not being utilized

8. Function Based indexes in SQL Server

9. function-based index

10. Function-Based index

11. Function based index on Standard Edition

12. Function Based Indexes and ODBC - Help!?


 
Powered by phpBB® Forum Software