RE: optimizer not using function based indexes
And I respectfully agree that I was wrong :)It happens, I think I already
posted a note to the list apologizing and correcting my error.
This is what happens when I post from memory without double checking
obviously I had done a "flush" of my shared_pool!
Rachel
|+--->
|| |
|| |
|| MGogala@oxhp.|
|| com |
|| |
|| 05/09/2002 |
|| 05:43 PM |
|| Please |
|| respond to |
|| ORACLE-L |
|| |
|+--->
>|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
>|
I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: optimizer not using function based indexes
>
>
>
>
> you don't need the upper(ename) in your where clause... that
> forces oracle to
> NOT use an index
>
>
>
>
> |+->
> || |
> || |
> || Harvinder.Singh@met|
> || ratech.com |
> || |
> || 05/09/2002 04:18 PM|
> || Please respond to |
> || ORACLE-L |
> || |
> |+->
> >|
> ||
> | To: [EMAIL PROTECTED] |
> | cc: (bcc: Rachel Carmichael) |
> | Subject: optimizer not using function|
> | based indexes|
> >|
>
>
>
>
> Hi,
>
> We are trying to use function based indexes in our
> application but it looks like
>
> optimizer is not using them.
> So we created a sample table emp with 3 columns
> empno,ename,sal and populate
> this table with 10 rows and created function based index
> on upper(ename).
> we try to run simple query like below and optimzer is not using index:
> select sal from emp
> where upper(ename)=upper('abc98');
>
> Even we use index hint optimizer is still not using index.
>
> 1) table is analyzed as compute after creating index.
> 2) query rewrite is granted to user
> 3) query_rewrite_enabled=true.
> 4) optimzer mode is choose.
> 5) optimer should use index as there is no way full scan can
> be faster than
> index in this case as i tried to create simple
> index on ename and remove upper function from where clause of query..
>
>
> What can be the possible reason that optimzer is not using index??
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling o
Re: optimizer not using function based indexes
I take it back... the query was correct as originally written. Just
tried similar queries on my database and the FBI was used for:
upper(ename)=upper('abc98')
upper(ename)='ABC98'
It's been a LONG week...
[EMAIL PROTECTED] wrote:
>
> and stats have to have been collected on the index and base table as well
>
> just checked the 9i docs... I was wrong you do need the upper on the column, my
> bad.. but you need the stats, the compatibility set (as Beth says)
>
> |+-->
> || |
> || |
> || Beth.Seefelt@tet|
> || leyusa.com |
> || |
> || 05/09/2002 05:24|
> || PM |
> || Please respond |
> || to ORACLE-L |
> || |
> |+-->
> >|
> ||
> | To: [EMAIL PROTECTED] |
> | cc: (bcc: Rachel Carmichael) |
> | Subject: RE: optimizer not using function|
> | based indexes|
> >|
>
> Another requirement is that your compatibility init.ora parameter must
> be set to 8.1.7 or higher, I believe, but check the doco.
>
> If that doesn't fix it, then trying using a hint to force the use of
> that index. If it doesn't work with the hint, then you know you have an
> environment problem that is preventing the use of function-based
> indexes. If it does work with the hint, then you know that function
> based is working but you have to figure out why the optimizer is not
> choosing it.
>
> HTH,
>
> Beth
>
> -Original Message-
> Sent: Thursday, May 09, 2002 4:19 PM
> To: Multiple recipients of list ORACLE-L
>
> Hi,
>
> We are trying to use function based indexes in our application but it
> looks like
> optimizer is not using them.
> So we created a sample table emp with 3 columns empno,ename,sal and
> populate
> this table with 10 rows and created function based index on
> upper(ename).
> we try to run simple query like below and optimzer is not using index:
> select sal from emp
> where upper(ename)=upper('abc98');
>
> Even we use index hint optimizer is still not using index.
>
> 1) table is analyzed as compute after creating index.
> 2) query rewrite is granted to user
> 3) query_rewrite_enabled=true.
> 4) optimzer mode is choose.
> 5) optimer should use index as there is no way full scan can be faster
> than index in this case as i tried to create simple
> index on ename and remove upper function from where clause of query..
>
> What can be the possible reason that optimzer is not using index??
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Seefelt, Beth
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
RE: optimizer not using function based indexes
and stats have to have been collected on the index and base table as well
just checked the 9i docs... I was wrong you do need the upper on the column, my
bad.. but you need the stats, the compatibility set (as Beth says)
|+-->
|| |
|| |
|| Beth.Seefelt@tet|
|| leyusa.com |
|| |
|| 05/09/2002 05:24|
|| PM |
|| Please respond |
|| to ORACLE-L |
|| |
|+-->
>|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: RE: optimizer not using function|
| based indexes|
>|
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: optimizer not using function based indexes
I think the point Rachel made is the query should instead be:
where ename=upper('abc98');
Harvinder Singh wrote:
>
> that's why we are using function based indexes...
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
>
> you don't need the upper(ename) in your where clause... that forces oracle to
> NOT use an index
>
> |+->
> || |
> || |
> || Harvinder.Singh@met|
> || ratech.com |
> || |
> || 05/09/2002 04:18 PM|
> || Please respond to |
> || ORACLE-L |
> || |
> |+->
> >|
> ||
> | To: [EMAIL PROTECTED] |
> | cc: (bcc: Rachel Carmichael) |
> | Subject: optimizer not using function|
> | based indexes|
> >|
>
> Hi,
>
> We are trying to use function based indexes in our application but it looks like
>
> optimizer is not using them.
> So we created a sample table emp with 3 columns empno,ename,sal and populate
> this table with 10 rows and created function based index on upper(ename).
> we try to run simple query like below and optimzer is not using index:
> select sal from emp
> where upper(ename)=upper('abc98');
>
> Even we use index hint optimizer is still not using index.
>
> 1) table is analyzed as compute after creating index.
> 2) query rewrite is granted to user
> 3) query_rewrite_enabled=true.
> 4) optimzer mode is choose.
> 5) optimer should use index as there is no way full scan can be faster than
> index in this case as i tried to create simple
> index on ename and remove upper function from where clause of query..
>
> What can be the possible reason that optimzer is not using index??
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Suzy Vordos
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: optimizer not using function based indexes
I respectfully disagree. In order to use a function based index
you do need the upper. If optimizer doesn't pick it up straigt
away, he should give a hand with a hint (/*+ INDEX(alias,index) */)
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, May 09, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: optimizer not using function based indexes
>
>
>
>
> you don't need the upper(ename) in your where clause... that
> forces oracle to
> NOT use an index
>
>
>
>
> |+->
> || |
> || |
> || Harvinder.Singh@met|
> || ratech.com |
> || |
> || 05/09/2002 04:18 PM|
> || Please respond to |
> || ORACLE-L |
> || |
> |+->
> >|
> ||
> | To: [EMAIL PROTECTED] |
> | cc: (bcc: Rachel Carmichael) |
> | Subject: optimizer not using function|
> | based indexes|
> >|
>
>
>
>
> Hi,
>
> We are trying to use function based indexes in our
> application but it looks like
>
> optimizer is not using them.
> So we created a sample table emp with 3 columns
> empno,ename,sal and populate
> this table with 10 rows and created function based index
> on upper(ename).
> we try to run simple query like below and optimzer is not using index:
> select sal from emp
> where upper(ename)=upper('abc98');
>
> Even we use index hint optimizer is still not using index.
>
> 1) table is analyzed as compute after creating index.
> 2) query rewrite is granted to user
> 3) query_rewrite_enabled=true.
> 4) optimzer mode is choose.
> 5) optimer should use index as there is no way full scan can
> be faster than
> index in this case as i tried to create simple
> index on ename and remove upper function from where clause of query..
>
>
> What can be the possible reason that optimzer is not using index??
>
> Thanks
> --Harvinder
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Harvinder Singh
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Gogala, Mladen
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: optimizer not using function based indexes
I thought that's what told the optimizer to use the FBI? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] > Sent: Thursday, May 09, 2002 3:49 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: optimizer not using function based indexes > > > > > you don't need the upper(ename) in your where clause... that > forces oracle to > NOT use an index -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: optimizer not using function based indexes
Another requirement is that your compatibility init.ora parameter must
be set to 8.1.7 or higher, I believe, but check the doco.
If that doesn't fix it, then trying using a hint to force the use of
that index. If it doesn't work with the hint, then you know you have an
environment problem that is preventing the use of function-based
indexes. If it does work with the hint, then you know that function
based is working but you have to figure out why the optimizer is not
choosing it.
HTH,
Beth
-Original Message-
Sent: Thursday, May 09, 2002 4:19 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are trying to use function based indexes in our application but it
looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and
populate
this table with 10 rows and created function based index on
upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster
than index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seefelt, Beth
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: optimizer not using function based indexes
that's why we are using function based indexes...
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+->
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+->
>|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function |
| based indexes|
>|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: optimizer not using function based indexes
you don't need the upper(ename) in your where clause... that forces oracle to
NOT use an index
|+->
|| |
|| |
|| Harvinder.Singh@met|
|| ratech.com |
|| |
|| 05/09/2002 04:18 PM|
|| Please respond to |
|| ORACLE-L |
|| |
|+->
>|
||
| To: [EMAIL PROTECTED] |
| cc: (bcc: Rachel Carmichael) |
| Subject: optimizer not using function |
| based indexes|
>|
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than
index in this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
optimizer not using function based indexes
Hi,
We are trying to use function based indexes in our application but it looks like
optimizer is not using them.
So we created a sample table emp with 3 columns empno,ename,sal and populate
this table with 10 rows and created function based index on upper(ename).
we try to run simple query like below and optimzer is not using index:
select sal from emp
where upper(ename)=upper('abc98');
Even we use index hint optimizer is still not using index.
1) table is analyzed as compute after creating index.
2) query rewrite is granted to user
3) query_rewrite_enabled=true.
4) optimzer mode is choose.
5) optimer should use index as there is no way full scan can be faster than index in
this case as i tried to create simple
index on ename and remove upper function from where clause of query..
What can be the possible reason that optimzer is not using index??
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder Singh
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
