Query rewrite

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 Received on Fri Jul 15 2005 - 12:55:30 CDT