Link to home
Start Free TrialLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

Identify records which do NOT qualify for a view

Having a hard time deciding how best to do this.  See the attached statement, it is the definition of an Oracle view.  There are a lot of records not included in the view, that are coming as a surprise, because they were expected to have been returned in the view result-set.  I researched two of them, found they were not in the view because a couple qualifications had not been met, and now I am being asked to write a report that will list ALL records that are not included in the view.  I don't believe this is a one statement deal, and I was hoping for a little EE advice on the best way to handle this as quickly, and easily as possible.  This is because it is very urgent.  Any direction is really appreciated.
View.sql
ASKER CERTIFIED SOLUTION
Avatar of johnsone
johnsone
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dbaSQL

ASKER

Wow!  I was not aware of the minus function.  Very cool.  Thank you both!  Give me some time to mess with this, I will update as soon as I can.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Technically it is a set operator.  It isn't a function...

http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#SQLRF52341
Avatar of dbaSQL

ASKER

Well, I think it's a pretty good start.  I am going to need to build onto this, most likely, so my primary concern right now would just be the performance of the MINUS operator, which is new to me.  I ran an EP with the statement below, but I am still learning these explain plans.  Do either of you have the time to review, and advise on the attached


SPOOL C:\Users\me\EXPLAIN_view_report.txt

SET linesize 300

EXPLAIN PLAN FOR
SELECT IMITM
FROM CRPDTA.f4101
MINUS
SELECT Item_Number
FROM CRPDTA.ITEMVIEW;

/
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())
--select * from table(dbms_xplan.display)
/
exit
EXPLAIN_view_report.txt
>>the best way to handle this as quickly, and easily as possible

I took "quickest" to mean fastest to implement.

If you want it to be the fastest to execute, I'm not sure how much help we can be.

The set operators are known to be pretty efficient.  From my experience, they are typically the fastest way to do what they do.

Not in every situation but in most.

Sorry, but that plan really doesn't mean anything to me.  I don't know your system.

Seems like indexes are being used.  Look at the "TABLE ACCESS FULL" and see if those make sense.  If you think they should be using an index, you need to dig deeper to see why it isn't.
Avatar of dbaSQL

ASKER

You are correct, slightwv, I did mean the fastest to implement.  I will review the ep output further, but I will go ahead and close this inquiry.  I am very grateful for the input from both you and johnsone.  Very big thanks!
MINUS is going to give you the best performance.

The only way you could probably improve on it is to run your view without all the outer joins.  Not sure if that would alter the rows returned, but it could potentially make it run faster.

However as far as fastest to implement, you aren't going to beat set operators.

In the cases of extremely large data sets, it can be faster to dump them into text files and use OS tools to find the differences.  Takes longer to implement, but time to complete can be faster.  Especially if you start doing the dumping and sorting operations in parallel.
Avatar of dbaSQL

ASKER

Thank you, johnsone.  I really appreciate the additional input.