I thought so also, but when I was running 8.1.7.1 we couldn't get the
function based index to work unless the case matched; we were doing the same
thing with upper on a varchar2 column. Unfortunately, since I am laid off,
I don't have access to an 8.1.7 database to check this behavior. It would
be easy for Cristian to check though.
Jim
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:3c165eb1$0$561$afc38c87_at_news.optusnet.com.au...
> No the functions don't have to match in case. This is function-based
> indexes were dealing with here, not stored outlines. An index built on
> emp(sum(sal)+avg(sal)+32) can even be used by the optimizer if the
eventual
> select is for 32+sum(sal)+avg(sal)>53. It's aware of commutative
> mathematical operations, therefore.
>
> I suspect the problem here is in your first line of response. Since we
are
> dealing with the optimizer, it's up to the optimizer to determine whether
> the use of the function-based index is of merit. That requires statistics
> on the table (and on the index, I'd suggest). It helps if the column
being
> searched for is declared NOT NULL, too -since it has always been tricky
> persuading the optimizer that a function-based index on a nullable column
> references all rows in the table, but they got that right in 9i. And if
the
> thing being selected for represents more than about 5% of the rows in the
> table, the optimizer is unlikely to use an index at all, of whatever sort.
> And if there are only (say) 50 rows in the table to begin with, we're in
for
> an efficient full table scan come what may.
>
> Regards
> HJR
> --
> ----------------------------------------------
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:blqR7.32893$ER5.374350_at_rwcrnsc52...
> > How many rows are in the acquirenti table? (a full scan might be
> efficient.)
> >
> > Also when you defined the function based index you did:
> > ... ( UPPER(... ))
> > when you issed the query you did
> > ... upper(...)
> > I know this sounds silly but try in your select statement
> > ...UPPER(...)
> > I believe that the function and the select have to match even in case.
> > Again sounds silly, but that might be it.
> > Jim
> > "Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message
> > news:3C162538.F77972CB_at_crpa.it...
> > Niall Litchfield wrote:
> >
> > > Function based
> > > indexes are for cases where you are issuing queries like
> > >
> > > select * from acquirenti
> > > where upper(acquirente_rag_soc) like 'VERO%';
> >
> > Sorry, in my second post I forgot to include the UPPER function in the
> > query. But the problem still remains:
> >
> > SQL> explain plan for
> > 2 select * from acquirenti
> > 3 where upper(acquirente_rag_soc) like 'VERO%';
> >
> > Explained.
> >
> > SQL> select * from plan_view;
> >
> > OPERATION OPTIONS OBJECT_NAME
> > POSITION
> > -------------------- -------------------- --------------------
> > ----------
> > SELECT STATEMENT
> > 10
> > TABLE ACCESS FULL ACQUIRENTI
> > 1
> >
> > Best regards, Cristian
> >
> > --
> > Cristian Veronesi ><((((º> http://www.crpa.it
> >
> > There are no good wars, with the following exceptions: The American
> > Revolution, World War II, and the Star Wars Trilogy. (Bart Simpson)
> >
> >
>
>
Received on Tue Dec 11 2001 - 15:22:36 CST
