Re: Query rewrite

"Ryan S" <rshevchi_at_vt.edu> wrote in message news:1121450129.965428.227250_at_g43g2000cwa.googlegroups.com...
>I have an intersect query that takes about 48 seconds to return 93
> rows. This is on 9.2.0.4 on Solaris.
>
> Select distinct e_id, p_id
> from some_view
> where category = 'S'
> and upper(varchar_value) like '%SOURCE%'
> INTERSECT
> Select distinct e_id, p_id
> from flat_view
> where category = 'S'
> and e_name = 'TITLE'.
>
> 93 rows.
>
> Select distinct e_id, p_id
> from some_view
> where category = 'S'
> and upper(varchar_value) like '%S%'
> and e_name = 'TITLE'.
>
> Returns 92 rows.
>
> This is for a DSS system -- I can't change the use of wildcards or the
> "upper" on varchar_value(there is a function based index).
> e_id, p_id, category, and varchar_value are from 4 different tables.
>
> Anyway to rewrite this to get all 93 rows quickly?
>
> Thanks,
> Ryan
>

Hi,

Did you check the query execution plan ? Any full table scans instead of index lookups ? Received on Fri Jul 15 2005 - 13:45:18 CDT