Different query results with an additional SELECT column

I have 2 SQL queries with the same joins and where conditions. The only difference is the second SQL contains an additional field in the select statement. Query 1 returns NO rows, while query 2 returns many rows. The explain plans of these 2 queries are also different. Please help with an explanation if you can. Is there something in Oracle that requires certain fields to be in the select statement in order for certain joins to generate results?

Interesting. Most interesting. I'm sorry, I really cannot understand why query 1 should return no rows while query 2 returns many. Perhaps by the time you see this answer, the data will have changed, and you will get consistent results. Perhaps there was a problem with the indexes.

With regard to the difference in EXPLAIN PLANs, this is a bit tricky to understand, too. Ordinarily, the addition of a column to the SELECT list can change the execution plan if this column is not involved in joining tables. If rows can be tested for inclusion by examining index columns without retrieving actual row data, the execution plan will be different than if the row has to be retrieved in order to test a column that is not in an index. But in your example, the extra column (al5.assay_subtype) is involved in the join, and has the same predicate in both queries --

AND AL5.ASSAY_SUBTYPE = 'Binding'

The only comment I can offer is that if you're going to test for a specific value of a column, there's not much point in adding it to the SELECT list -- might just as well add the literal character string instead.

The answer to your final question, "Is there something in Oracle that requires certain fields to be in the select statement in order for certain joins to generate results?" is "No."

Ask your DBA to try going back and regenerating your indexes. If you still get the disparity, consider asking Oracle Corporation for additional help.

Start the conversation

0 comments

Register

I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.

Please check the box if you want to proceed.

I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.