Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> But arn't function based indexes static? do they get updated as we insert rows?

But arn't function based indexes static? do they get updated as we insert rows?

From: daven <davenport_at_hotmail.com>
Date: 30 Jul 2001 09:45:03 -0500
Message-ID: <3b657245$0$85157$45beb828@newscene.com>

Do FBIs get updated as we insert or update rows into a table. I believe not.

In a system that has lots of transactions, wouldn't this be a big problem?

In article <9jsfvb02c8u_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>In article <3B61BE85.D77078A6_at_attws.com>, "Daniel says...
>>
>>Thomas Kyte wrote:
>>
>>> In article <9jrs1g$16b$1_at_reader-00.news.insnet.cw.net>, "Andy says...
>>> >
>>> >Yes, you could do this - for most developers though this is totally
>>> >impractical.
>>> >
>>> >It's just another thing that ORACLE doesn't cater for...Ohh, sorry, I mean
>>> >doesn't lower itself to the level of all the other RDBMS.
>>> >
>>> >.
>>>
>>> since I can:
>>>
>>> create index on t(upper(field1));
>>>
>>> allowing for
>>>
>>> select * from t where upper(field1) = :x
>>>
>>> to use an index, why is this impractical?
>>> http://osi.oracle.com/~tkyte/article1/index.html
>>>
>>>Additionally, there is interMedia Text which gives infinitely many more
 options
>>>for searching text (stop words, near, and, or, fuzzy, soundex, stem,
 etc....).
>>> It by default indexes in a case insensitive fashion. You can make it case
>>> sensitive if you like.
>>>
>>> http://technet.oracle.com/doc/oracle8i_816/inter.816/a77063/toc.htm
>>>
>>> >
>>> >FB
>>> >
>>> >"Thomas Liang" <liangluosheng_at_china.com> wrote in message
>>> >news:9jr2nl$hmk$1_at_mail.cn99.com...
>>> >> you can write
>>> >> SELECT * FROM table WHERE UPPER(field1)='name'
>>> >>
>>> >> > Hello,
>>> >> >
>>> >> > we want to do some SQL-statement like 'SELECT FROM table WHERE field1 =
>>> >> > "name"' not be affected by case sensitivity as in result we get "name"
>>> >> > or "Name". Our actual installation of Oracle 8 works case sensitive.
>>> >> >
>>> >> > My DBA for our Oracle 8 DBMS told me, that it isn't possible to change
>>> >> > this at the server site to "case insensitive".
>>> >> >
>>> >> > A developer told me, he had read that the server site is configurable
>>> >> > for "case insensitive" or "case sensitive" (what we have at the
 moment).
>>> >> >
>>> >> > My question: Who is right?
>>> >> >
>>> >> > Thanks for help
>>> >> > Bernd.
>>> >> > --
>>> >> > Bernd F. Dollinger
>>> >> > eMail: Bernd.Dollinger_at_dv-werk.de
>>> >>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>>I read this question very differently than you did. Turning off a search I
>>interpreted as being a "switch" that could be flicked back and forth at will.
 A
>>function based index will solve the problem. But you'll need to drop and
 rebuild
>>the index to reverse it.
>>
>>Daniel A. Morgan
>>
>
>Actually I believe the poster was referring to (based on the tone of
>
>>> >It's just another thing that ORACLE doesn't cater for...Ohh, sorry, I mean
>>> >doesn't lower itself to the level of all the other RDBMS.
>
>) the feature in SQL Server whereby a database can be built with case
>insensitive serching or not. For them, its all or nothing at the db level (its
>not a runtime switch one and off -- its an attribute of a database). For us,
>its a choice. In fact you can have your cake and eat it too:
>
>
>create index t_idx on t(upper(x)); /* index for case insensitive searching */
>create index t_idx2 on t(x); /* index for case insensitive searches */
>
>So, if you need case insensitive searches,
>
>1) use intermedia
>2) use function based indexes
>
Received on Mon Jul 30 2001 - 09:45:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US