ranit B wrote:Since the requirements is to compare all columns and rows in the tables full table scans will be the fastest way to do that. Indexes will not help here and the resulting code will be much more complex.
Try using NOT EXISTS in place of MINUS.
From docs -- http://docs.oracle.com/html/A86647_01/vmqtune.htm
MINUS returns the set of rows from one query that is not present in the set of rows returned by a second query. Rewriting queries using NOT EXISTS or NOT IN can enable them to take advantage of indexes, reducing the number of full table scans a clause may require.
974253 wrote:Well it will only take six times longer. Which I still think will be faster than trying to create one single code block that will handle all tables and process the output. What do you intend to do with the results from the queries from all these different tables when they do not match? If the goal of this is validation it would seem that introducing a high level of unreliable automation in the process would be counter productive.
I can create manualy minus queries for 200 tables. but ...its just a phase1 count ..overall i'll have to write queries for more than 1200 tables...in my testing cycle.. :(
974253 wrote:As I said, I don't think it will save time, and I tend to consider unreliable validation as effective as no validation at all or worse, but it's your project so whatever works for you.
Actually it is a sudden requirement ..earlier validation was not in our scope..but now.. it is ...we have very short time window...
so in that we need to write minus query manually. and then execeute manually...and it'd take more time ...thats why I was planning to make all minus queries through procedure...it'll save time