Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Making use of an Index with the rtrim function in the where clause
Functional indexes are part of Oracle8i, which was released for Solaris all of 2
days ago. At least we can now talk of what you can do, rather than what you
will be able to do! I think you can get 8i from technet, but I haven't
confirmed that.
HTH. Pete
Prabhakar wrote:
> Thanks for the information Thomas and pete.
> But I have one more question like which version of Oracle 8 has
> got this feature.
> I think I have 8.04 and I am not able to create the index.
> I use the command.....
> create index <index_name> on <table_name>(rtrim(<column_name>))
> But it gives me a missing right parenthesis error.
>
> Can you further add to your suggestions.....
>
> In article <36DC3E22.661EADEA_at_us.oracle.com>,
> Peter Sharman <psharman_at_us.oracle.com> wrote:
> > As Thomas says, this is available in 8i. The approach I've used prior to
> > 8i is to create a "hidden" column, populate the hidden column with the
> > function value (rtrim(colname)) via a trigger, index the hidden column and
> > report on that. Messy, but it gets you there.
> >
> > HTH.
> >
> > Pete
> >
> > Prabhakar wrote:
> >
> > > Hi!
> > > I am trying to use a column inside the Rtrim function in sql query's
> > > where clause,I have an index for that column,I read from sql tuning
> > > books that Oracle will not use the index if the column is embedded
> > > within a function call like upper,is null etc.
> > > How do I force Oracle to make use of that Index?...
> > >
> > > Please do give me your valuable suggesstions on this.
> > >
> > > Thanks in advance.
> > >
> > > -----------== Posted via Deja News, The Discussion Network ==----------
> > > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
> >
> > --
> >
> > Regards
> >
> > Pete
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Peter Sharman Email: psharman_at_us.oracle.com
> > WISE Course Development Manager Phone: +1.650.607.0109 (int'l)
> > Worldwide Internal Services Education (650)607 0109 (local)
> > San Francisco
> >
> > "Controlling application developers is like herding cats."
> > Kevin Loney, ORACLE DBA Handbook
> > "Oh no it's not! It's much harder than that!"
> > Bruce Pihlamae, long term ORACLE DBA
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> >
> >
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA