I use function based indexes all the time. They get updated on inserts and
updates without me doing anything in addition. They work the same as
regular indexes work. It is true that for each insert the function has to
be evaluated (for the index entry creation) and the function is called on
each update that involves the column or columns that the function uses. Of
course, if the updated columns are not part of the function based index then
the function does not get called.
Jim
"daven" <davenport_at_hotmail.com> wrote in message
news:3b657245$0$85157$45beb828_at_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_at_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 - 18:00:55 CDT
