dbaSQL
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
View.sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Technically it is a set operator. It isn't a function...
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#SQLRF52341
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries004.htm#SQLRF52341
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_r eport.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
SPOOL C:\Users\me\EXPLAIN_view_r
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.
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.
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.
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.
ASKER
Thank you, johnsone. I really appreciate the additional input.
ASKER