[sqr-users] Thinking about trying something - looking for ideas

[john.tucker@oracle.com]

I have a rather different question today.  I am thinking about writing an SQR 
that would look for orphan data anywhere in a PeopleSoft database, using 
PeopleTools tables to figure out what to look at rather than using a static 
list of tables.  I have an idea or two about how to go about this... but I 
wondered if anyone out here had already written something similar and/or had 
some suggestions for things to do/avoid.

For example, there will be a lot of dynamic SQL getting processed in this SQR.  
Does that usually stop indexes from being used?  If so, is there any way I can 
force indexes to be used?

Also, would it be better to say "select [whatever] from [child] where not 
exists ([whatever] in [parent])" or would it be better to write two select 
statements, pulling all rows from the child table and trying to match them with 
rows in the parent table in a separate select statement?  Either way, I already 
know this thing is going to take forever to run.

I don't want you fine folks to do my work for me, but I was pretty sure someone 
out there would have suggestions on how I can make this a better, faster tool 
than the way I would have written it without asking.

-John T.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users