Oracle query with inconsistent results

Question

I have a very strange problem, I have a complex view that returns incorrect data when I request a specific column.
Here is an example:

select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'; 

this returns a single result: "Testerson, Testy", "N"

however, if I use the query:

 select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy' and has_garnishment = 'Y'; 

this returns a single result: "Testerson, Testy", "Y"

The second request should return a subset of the first request, but it returns a different response.

When I use the query:

 select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy' and has_garnishment = 'N'; 

I do not get results

I disassembled the view and decided that this section of the view definition is a problem, and the problem exists even if I take the sql definition and run it as a direct query (note, I deleted all the select condition except for parts of interest for clarity, everything is required in the full query joined tables):

 SELECT e.fullname empname , NVL2(ded.has_garn, 'Y', 'N') has_garnishment FROM timecard tc , orderdetail od , orderassign oa , employee e , employee3 e3 , customer10 c10 , order_misc om, (SELECT COUNT(*) has_garn, v_ssn FROM deductions WHERE yymmdd_stop = 0 OR (LENGTH(yymmdd_stop) = 7 AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') > sysdate) GROUP BY v_ssn ) ded WHERE oa.lrn(+) = tc.lrn_order AND om.lrn(+) = od.lrn AND od.orderno = oa.orderno AND e.ssn = tc.ssn AND c10.custno = tc.custno AND e.lrn = e3.lrn AND e.ssn = ded.v_ssn(+) 

One note on the definition of the 'ded' subquery. The v_ssn field is a virtual field in the residue table.

I am not a DBA software developer, but recently we have lost our DBA and the new one is still accelerating, so I am trying to debug this problem. In doing so, please explain things a little more carefully than you would for an oracle expert. A.

thanks

Answer 1

It turns out the problem was a conflicting index. The column had an old index from which the virtual column v_ssn was built. I reset this index and the query began to behave as expected. I'm still worried about how this index affects the query, but at least my immediate problem has been resolved.

Thanks for your help!

Answer 2

Firstly, you have the connection "AND od.orderno = oa.orderno", which denies the external connection to "oa.lrn (+) = tc.lrn_order". IE, if tc.lrn_order does not find a match in oa, the outer join says it will return a string anyway, but this will have a zero order that could not match od

Secondly, the definition of a virtual column may be relevant. If deleting the index fixed the problem, then this suggests that the previous plan used the index.

Third, the preliminary subquery seems a bit awkward. At first sight,

 SELECT COUNT(*) FROM tab WHERE COL=:val 

seems very similar to

 SELECT cnt FROM (SELECT COL, COUNT(*) FROM tab GROUP BY COL) WHERE COL=:val 

but where: val does not exist in the "tab", the first will return the number 0, and the second will not return any rows (and with an external join, will return NULL).

If COL has a useful index, then option 1 can be attractive. I suspect that somewhere Oracle is rewriting the last to the first, and if instead of NVL2 you just specified the has_garn value, you would see 0, not zero.

An alternative to the error might be the following:

If v_ssn is based on func (col), then Oracle may (incorrectly) assume that func (COL) should be null if COL is null. If most of the COL was null, she could suggest that using the COL index to search for non-empty strings would give it a smaller and more efficient group of strings to process.