Prev: Materialized Views and Paritions in fact tables
Next: Oracle 10g RAC on IBM DS 3400 ?
From: Mladen Gogala on 31 Dec 2009 23:55
On Thu, 31 Dec 2009 08:30:45 -0800, Mark D Powell wrote:


> Since the OP has now informed us the SQL is part of a package and cannot
> be changed I think the OP's options are limited.

In other words, the OP is screwed. Happy New Year.



--
http://mgogala.byethost5.com
From: Mladen Gogala on 3 Jan 2010 15:06
On Thu, 31 Dec 2009 08:30:45 -0800, Mark D Powell wrote:

> Since the OP has now informed us the SQL is part of a package and cannot
> be changed I think the OP's options are limited. Stored outlines might
> be of use but I think it is unlikely since one possible condition in the
> SQL as posted requires a full table scan that I do not think there is
> any way around without changing the SQL.

In 10G there is DBMS_ADVANCED_REWRITE package which can be used to
rewrite the bad SQL even if there is no access to the source. The
procedure in the package is "DECLARE_REWRITE_EUIVALENCE". A brief
example is available here:

http://www.blogskies.com/2009/07/dbmsadvancedrewrite.html

Somewhat more detailed article is here:

http://www.oracle-base.com/articles/10g/dbms_advanced_rewrite.php




--
http://mgogala.freehostia.com
From: Mladen Gogala on 4 Jan 2010 10:59
On Mon, 04 Jan 2010 02:52:53 -0800, Randolf Geist wrote:


> See Metalink (sorry, MOS) Note 392214.1

I'm aware of that paper, but that stinking pile of ... stuff ain't
working... again.

>
> Dion Cho has also two interesting articles about this package:
>
> http://dioncho.wordpress.com/2009/03/06/optimizing-unoptimizeable-sql-
dbms_advanced_rewrite/
>
> http://dioncho.wordpress.com/2009/10/13/dbms_advanced_rewrite-and-dml/
>
> Regards,
> Randolf

I wasn't aware of these two excellent articles. Thanks again.


--
http://mgogala.byethost5.com
From: Gerard H. Pille on 5 Jan 2010 07:32
On 29 dec 2009, 22:39, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
> On Dec 29, 12:19 pm, "bob123" <bob...(a)gmail.com> wrote:
>
> > Hi,
>
> > I have a lot of queries like below:>select * from bxat.no5
> > >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
> > >AND (tbname = :wp19 OR :wp19 IS NULL)
>
> > so an access full on no5
>
> > How can I speed up this query ?
> > Thanks in advance
> > (Oracle 9.2.0.6)
>
> Bob,
>
> If the issue is that the optimizer is choking on the ORs you might,
> barring adding indexes, want to consider changing your query from the
> present set of ORs to a set of simpler UNIONs, e.g.,
>
> select *
> from bxat.no5
> WHERE no0_session_id = :wp18
> UNION
> select *
> from bxat.no5
> WHERE
> wp18 IS NULL
> UNION
> .
> .
> .
>
> --Jeff


That should be a "UNION ALL".

a. performance (saves an expensive SORT)
B. to get all rows, even if there are duplicates
From: jefftyzzer on 5 Jan 2010 22:13
On Jan 5, 4:32 am, "Gerard H. Pille" <ghpi...(a)hotmail.com> wrote:
> On 29 dec 2009, 22:39, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
>
>
>
> > On Dec 29, 12:19 pm, "bob123" <bob...(a)gmail.com> wrote:
>
> > > Hi,
>
> > > I have a lot of queries like below:>select * from bxat.no5
> > > >WHERE (no0_session_id = :wp18 OR :wp18 IS NULL)
> > > >AND (tbname = :wp19 OR :wp19 IS NULL)
>
> > > so an access full on no5
>
> > > How can I speed up this query ?
> > > Thanks in advance
> > > (Oracle 9.2.0.6)
>
> > Bob,
>
> > If the issue is that the optimizer is choking on the ORs you might,
> > barring adding indexes, want to consider changing your query from the
> > present set of ORs to a set of simpler UNIONs, e.g.,
>
> > select *
> > from bxat.no5
> > WHERE no0_session_id = :wp18
> > UNION
> > select *
> > from bxat.no5
> > WHERE
> > wp18 IS NULL
> > UNION
> > .
> > .
> > .
>
> > --Jeff
>
> That should be a "UNION ALL".
>
> a. performance  (saves an expensive SORT)
> B. to get all rows, even if there are duplicates

With respect, while I readily agree that UNION ALL is likely faster
given that it obviates the need for a duplicate-eliminating sort, I
think the UNION is necessary precisely because it eliminates
duplicates. My goal was to suggest an alternate syntax that was
semantically equivalent to the OP's original query, which wouldn't
have returned duplicate rows given that it made only a single pass
through the "bxat.no5" table (whether the "bxat.no5" table contains
duplicates is a different matter altogether).

Regards,

--Jeff
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Materialized Views and Paritions in fact tables
Next: Oracle 10g RAC on IBM DS 3400 ?