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
