Re: [SQL] Refactored queries needing validation of syntactic equivalence

[Richard Huxton]

Mike Adams wrote:
Hello! I'm a long time lurker who has become responsible for maintaining 
/ updating utility queries at work.  I've reworked two queries (as text 
attachment as they are wide lines) to enhance the planner's chance of 
speeding up the queries (Oracle8i's).


Well, I can't say it's standard procedure to look at Oracle queries, but 
 if you don't tell anyone I won't :-)


I'm looking for someone to eyeball them and let me know if I've folded 
the sub-selects up correctly (I'm the ONLY sql speaking person at work 
so having a coworker do so is unfortunately not possible).


Also unfortunately, there currently aren't any issues in the database 
that these queries are designed to find.  All I can say for sure is (as 
you can see below each query) my refactored queries *at the least* 
return *no* data faster than the legacy queries...


Test data time then. No alternative to testing these things.

Thank you in advance and I wish the application at work used postgresql 
as it's backend!


OK, you've substituted and EXISTS check against a sub-query with a 
self-join. The key question of course is whether your join can return 
more than one match from "rcv_mo m" for each row in "rcv_mo o". I can't 
say without knowing more about your schema, and even then I'd want to 
test it.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[SQL] Accessing field of OLD in trigger

[Daniel Drotos]

Hi,

I'm working on a row level plpgsql trigger running after delete, using 
a 8.0.3 server. It gets a parameter which is a field name of the OLD 
record. How can that field be accessed?


I'd like to do something like:

for recvar in 'select OLD.'||quote_ident(TG_ARGV[0])...

Daniel

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly