Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query rewrite
On 15 Jul 2005 10:55:30 -0700, "Ryan S" <rshevchi_at_vt.edu> wrote:
>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
The distincts are redundant of course, as INTERSECT is a set operation
and sets can't have duplicate tuples.
One would wish people would brush up their relational calculus before
starting to use a relational dbms.
-- Sybrand Bakker, Senior Oracle DBAReceived on Fri Jul 15 2005 - 13:03:03 CDT