Re: SQL Help

On Jul 11, 4:07 pm, "Chris L." <diversos_at_uol.com.ar> wrote:

>
> SELECT * FROM task WHERE task_id IN
> (
> SELECT task_id FROM actv_code WHERE short_name LIKE '%FIN'
> AND code='Lead Craft'
> MINUS
> SELECT task_id FROM actv_code WHERE code='Outage Code'
> );
>
> Though I'd do something about that "short_name ends with FIN" filter,

One thing that can be done for quick lookups on the tail end of a column
is to put a function-basd index on REVERSE(col), e.g.,

    CREATE INDEX i_actv_code on actv_code (upper(reverse(short_name)))

then use

    WHERE upper(reverse(short_name)) LIKE reverse('%FIN')

I use this to deal with things like searching for "credit card ends with ..."

Here's an example of it in action; note how Oracle evaluates the constant
expression and only then uses it in the filter predicate:

    http://otb.alma.com/otb.fcgi?func=sqlexplain&server=orcl&user=SH&qid=65

Hope that helps,
John Hinsdale Received on Fri Jul 13 2007 - 11:26:02 CDT