Not only does it not use the index, it also does an upper on all
values in that column. Which on a sizable schema could amount to some
lag time, although I'd guess it would not be noticed in most cases for
this.
On Mon, 7 Mar 2005 15:26:35 -0500, Dave Carabetta <[EMAIL PROTECTED]> wrote:
> On Mon, 7 Mar 2005 14:54:10 -0500, Qasim Rasheed <[EMAIL PROTECTED]> wrote:
> > You can use something like this as Dave as already pointed out
> >
> > select *
> > from user_tables
> > where upper(table_name) = 'devicestate'
> >
>
> The only reason I'd advise against that (besides the fact that it
> should be LOWER()) is because Oracle will not use the index on that
> column if you use a Oracle function. In a small schema, it's probably
> not a big deal, but I'd err on the side of ColdFusion and put a
> UCase() around the table name being passed in so that Oracle properly
> uses any defined indexes:
>
> SELECT *
> FROM user_tables
> WHERE table_name = '#UCase("devicestate")#'
>
> Of course, I'd also suggest using cfqueryparam, but I didn't want to
> muddy the example above with extra CF stuff.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:197748
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
