"Select 1" causes very slow SQL
762287Jan 27 2011 — edited Jan 28 2011Oracle 11g.
I perform the following SQL on 2 tables:
"select 1 ord, actref from table1 where actref in (select actref1 from table2)"
Table1 has around 80,000 records; table 2 has around 25,000 records.
actref is the primary key in table1; actref1 is the primary key in table2.
This SQL takes a very long to execute (i.e. minutes).
However, if I remove the "1 ord", it runs in less than 1 second - which is what I would have expected:
I.e. "select actref from table1 where actref in (select actref1 from table2)" works ok.
If I put the 1 in single quotes then, again, it works ok.
I.e. "select '1' ord, actref from table1 where actref in (select actref1 from table2)" works ok.
[The reason for the "1 ord" is that this statement is unioned with lots of others, and then ordered by "ord" at the end]
Any ideas what Oracle is doing here? Does the "select 1" have any special significance?
Obviously I can just put this in single quotes as a workaround, but I'd like to understand what's happening as this technique is used in lots of places in our system.