Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query rewrite
It all depends on what your view is doing.
It may be accessing 44 tables...
-- Syltrem OpenVMS 7.3-1 + Oracle 9.2.0.5 http://pages.infinit.net/syltrem (OpenVMS related web site, en français) ---zulu is not in my email address--- "Ryan S" <rshevchi_at_vt.edu> a écrit dans le message de news:1121450129.965428.227250_at_g43g2000cwa.googlegroups.com...Received on Fri Jul 15 2005 - 13:14:59 CDT
> 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
>
![]() |
![]() |