Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Dynamic SQL & bind variable question (Oracle 10G)
PL/SQL Dynamic SQL & bind variable question [message #345231] Tue, 02 September 2008 12:12 Go to next message
webnerd_lw
Messages: 1
Registered: September 2008
Junior Member
The following pl/sql code runs slow using this syntax. Is there a way to write this that does not require a bunch of if/else logic that will run fairly quickly?

AND (:in_dw_seq_n IS NOT NULL AND rod1.seq_n = :in_dw_seq_n)
AND ((:in_dw_run_id IS NOT NULL) AND (os1.run_id = :in_dw_run_id OR :in_dw_run_id IS NULL))

Thanks in advance for any help you can offer.
Re: PL/SQL Dynamic SQL & bind variable question [message #345234 is a reply to message #345231] Tue, 02 September 2008 12:23 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

The posted lines do NOT look like PL/SQL to me.
the query is likely just doing Full Table scans; which are not fast operations when going against tables with many, many rows.
(For LARGE values of many).
Re: PL/SQL Dynamic SQL & bind variable question [message #345352 is a reply to message #345234] Wed, 03 September 2008 03:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This line:
AND (:in_dw_seq_n IS NOT NULL AND rod1.seq_n = :in_dw_seq_n)
is syntactically the same as
AND rod1.seq_n = :in_dw_seq_n
, as a null value for :In_Dw_Seq_n will make the test fail anyway.

In this line:
AND ((:in_dw_run_id IS NOT NULL) AND (os1.run_id = :in_dw_run_id OR :in_dw_run_id IS NULL)) 
the section
OR :in_dw_run_id IS NULL
will never take effect, as you've got an IS NOT NULL check on the same parameter at the start, which reduces the whole thing to:
AND (:in_dw_run_id IS NOT NULL AND os1.run_id = :in_dw_run_id)
, and as we've seen above, that's the same as
AND os1.run_id = :in_dw_run_id


So, in summary, I think your code can be reduced to
AND rod1.seq_n = :in_dw_seq_n
AND os1.run_id = :in_dw_run_id
Previous Topic: Functions
Next Topic: Validation of column values of records in PL/SQL table
Goto Forum:
  


Current Time: Sun Dec 04 12:58:21 CST 2016

Total time taken to generate the page: 0.10176 seconds