Prev: Materialized Views and Paritions in fact tables
Next: Oracle 10g RAC on IBM DS 3400 ?
From: Robert Klemme on 31 Dec 2009 06:24
On 12/31/2009 09:58 AM, bob123 wrote:
> OK thanks all ...
> The problem is that i can't rewrite the code
> it's a third party software ...
> any clue ?

Difficult... You can't easily use stored outlines because those would
be optimal for a single of the four cases. You may get away with
outlines in four different categories but then you somehow need to
switch between them before the statement is executed. I guess
everything you attempt will soon get messy. Is there no chance to get
the vendor of the application to change it? Maybe you can file it as
bug and get a resolution via your support contract.

Good luck!

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
From: Mladen Gogala on 31 Dec 2009 08:31
On Tue, 29 Dec 2009 21:19:22 +0100, bob123 wrote:

>>select * from bxat.no5
>>WHERE (no0_session_id = :wp18 OR :wp18 IS NULL) AND (tbname = :wp19 OR
>>:wp19 IS NULL)

I would advise against checking whether an external variable is NULL in
the SQL. I am aware that Hibernate sometimes does that, but that can be
reworked.



--
http://mgogala.byethost5.com
From: Mladen Gogala on 31 Dec 2009 08:33
On Wed, 30 Dec 2009 07:36:19 -0800, Mark D Powell wrote:

> Being that :wp18 and :wp19 are program bind variables then depending on
> which variables have a value they are actually 4 different conditions:
> return all rows when neither variable has a value, return matching rows
> for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is
> null, and return only rows that match both wp18 and wp19 when both have
> a value.

Plus the fact that the job checking whether an external variable is null
really belongs to the programming interface and not the database.



--
http://mgogala.byethost5.com
From: Mark D Powell on 31 Dec 2009 11:30
On Dec 31, 8:33 am, Mladen Gogala <gogala.mla...(a)gmail.com> wrote:
> On Wed, 30 Dec 2009 07:36:19 -0800, Mark D Powell wrote:
> > Being that :wp18 and :wp19 are program bind variables then depending on
> > which variables have a value they are actually 4 different conditions:
> > return all rows when neither variable has a value, return matching rows
> > for wp18 when wp19 is null,, return matching rows for wp19 when wp18 is
> > null, and return only rows that match both wp18 and wp19 when both have
> > a value.
>
> Plus the fact that the job checking whether an external variable is null
> really belongs to the programming interface and not the database.
>
> --http://mgogala.byethost5.com

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.

I suggest complaining to the vendor about the manner in which the SQL
is written and its effect on performance. I have some limited
experience with DB2 UDB and a liittle more experience with SQL Server
so I believe it is safe to say that having the program issue simplier
SQL for each desired result set based on program variables having
values or not would be more effiicient in all three systems than
having to have that check made in the SQL. I think this last is
pretty much in agreement with Mgogla's previous post in that some
actions belong in the program and some in the database.

HTH -- Mark D Powell --


From: Malcolm Dew-Jones on 31 Dec 2009 13:24
bob123 (bob123(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)

1. Try adding the not null explicitly to each section, I seem to recall
that helps the optimizer recognize what to do.

select * from bxat.no5
WHERE
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
---------------------
AND
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)
---------------------

then you could try using union. Someone suggested that though an
illustration of it didn't seem to use it correctly

select * from bxat.no5
WHERE
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
union
select * from bxat.no5
WHERE
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)

if the union helps but the main part of the query is complicated then put
it in a with clause (my example may have the syntax wrong)

with
( select * from bxat.no5
where complicated stuff in common
) as the_query
select * from the_query where
( :wp18 IS not NULL and no0_session_id = :wp18 OR :wp18 IS NULL)
union
select * from the_query where
( :wp19 IS not NUL and tbname = :wp19 OR :wp19 IS NULL)


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 ?