Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query rewrite

Re: Query rewrite

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 15 Jul 2005 20:03:03 +0200
Message-ID: <5gufd1hsqjbii6jkuou2jp81s9ks4danqj@4ax.com>


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 DBA
Received on Fri Jul 15 2005 - 13:03:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US