Re: How can I do create an index on expression ?

On Tue, 23 Jan 2001 19:58:15 +1100, "Howard J. Rogers" <howardjr_at_www.com> wrote:

>Invest in Oracle 8.1.5 or above, and read the manuals regarding
>function-based indexes. Short story is:
>
>CREATE INDEX blah ON emp (UPPER(ename));
>
>If you want to get mathematical, something like
>
>CREATE INDEX blah2 on emp (sal*0.1);
>
>Regards
>HJR
There are 2 gotchas I've found when building function-based indexes on an arbitrary machine:

"You must have the initialization parameters COMPATIBLE set to 8.1.0.0.0 or higher, QUERY_REWRITE_ENABLED=TRUE, and QUERY_REWRITE_INTEGRITY=TRUSTED." [ Can be a pain if you're not the DBA on that machine. ]

"You must analyze the table or index before the index is used. "

[ I usually use DBMS_STATS.GATHER_SCHEMA_STATS just to be on the safe side. ]

These are excerpts from the "Restrictions" section in Application Developer's Guide - Fundamentals. Function-based index doc is a bit spread out, so as a primer I would check out:

http://tahiti.oracle.com/pls/tahiti/tahiti.drilldown?word=function-based+index&book=&preference=&vbook=1

John

>
>
>"luba_at_usb" <luba_at_usb.ua> wrote in message
>news:94ju9e$5q0$1_at_news.ukrsotsbank.com...
>> How can I do create an index on upper(name) ?
>>
>> How can I do create an index on expression ?
>>
>>
>>
>>
>> Roy Brokvam <roy.brokvm_at_conax.com> ÓÏÏÂÝÉÌ × ÎÏ×ÏÓÔÑÈ
>> ÓÌÅÄÕÀÝÅÅ:Hf_a6.327$2i6.5014_at_news1.oke.nextra.no...
>> > Hi,
>> >
>> > select name from user where upper(name) = 'JOHN'
>> >
>> > If you need quick access via an index, create an index on upper(name)
>> > instead of name.
 

>> >
>> > --
>> > Roy Brokvam
>> >
>> > Hitchhikers against spam! Correct my last name to e-mail me.
>> >
>> >
>> > pluto wrote in message <94hbb7$5fbm$1_at_stargate1.inet.it>...
>> > >How i can make Oracle 8i case insensitive?
>> > >
>> > >Ex.:
>> > >select name from user where name = 'john'
>> > >
>> > >equal to
>> > >
>> > >select name from user where name = 'JOHN'
>> > >
>> > >Thancks
>> > >
>> > >
>> > >
>> >
>> >
>>
>>
>
>
Received on Thu Jan 25 2001 - 01:24:25 CST