Nested subquery within the Select clause

[jsze]

Hi all,

Previously I was using Oracle8i and now am using Oracle10g. I encountered a problem when I tried to execute a statement in 10g similar to those I have written in 8i, which works fine:

select col_id, col_a, col_b,
(select col_x
from table_2 t2
where t2.col_id = t1.col_id) as col_x
from table_1 t1

The error returned was 'Missing Expression' and the second/nested Select word was highlighted.

Am I missing anything? Syntax or etc? I just cant find out and it is driving me nuts.

I tried to execute this statement in Access and it works brilliantly. I know that instead of writing like this I can just join the tables in the where clause, but when it comes to tables with tens of thousands of records, this statement has reduced the retrieval time significantly.

I appreciate any input/feedback to my problem.

Thanks a million gazillion
Jsze

[jsze]

Turns out that the other database which the query was intended for was still using Oracle 8.0. The query worked fine in the 10g.

Sorry for such blunder.

[sde]

thanks for the update jsze. welcome to the community =)

[technobard]

Originally posted by jsze
I know that instead of writing like this I can just join the tables in the where clause, but when it comes to tables with tens of thousands of records, this statement has reduced the retrieval time significantly.

Hey, Jsze. I'm glad your problem is resolved. The statement above bothers me though. Have you (or your dba) analyzed the tables? This seems very odd, even if the statistics are missing and the query is defaulting to "rule-based". As long as an index exists on the join column(s) of each table, I wouldn't expect to see a difference in execution plan. Of course if an index doesn't exist on the join column(s), it might make a little more sense to me. It's possible that by writing the query with a nested subquery, you're forcing the driving table and picking the "correct one".

I know this isn't your original issue, but if you have the time, it might be worth resolving this weird behavior.

[jsze]

Hi Technoboard,

I am not much of a database lingo person, but I'll try to answer your question.

If the query was written like this:

select col_id, col_a, col_b, col_x, ... col_n
from table_1 t1, table_2 t2, ...
where t1.col_id = t2.col_id and ...

Even with indexes created, it takes a noticeable longer time to retrieve the rows (for my case).

I looked up for some info online, and found out that the scalar subquery (select within a select) is an improvement in Oracle 8i/9i and above, to enable faster and smarter row retrieval:

http://www.dba-oracle.com/art_9i_scalar_subq.htm

Hope this answers your question.

[technobard]

Thanks for the followup. It sounds like the optimizer isn't doing what it should. Most scalar subqueries can't be rewritten as a single query with a simple join. Typically, as in the link you provided, they allow you to combine different levels of granularity: averages combined with non-average data, for example. When the queries can be rewritten, the execution plan is usually the same (and therefore the execution time). I'm guessing that the statistics in the database are wrong in this case. Those statistics are updated by analyzing each table. No big deal. As long as things are working for you, I'm sure you're not too worried about it. I'm an Oracle DBA by day, so this sort of thing bothers me.

[technobard]

Just a quick followup. I did some experimenting and found similar results to yours, even with updating the stats. One thing you should be aware of, however, is that the scalar subquery is not equivalent to

select col_id, col_a, col_b, col_x, ... col_n
from table_1 t1, table_2 t2, ...
where t1.col_id = t2.col_id and ...

It is equivalent to an outer join and not an equijoin. This can affect your results, depending on the data of course.