An (IN)famous case of runaway query aka there is more than one way to do the same thing

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

I had to find out some records whose primary key was not referenced in a secondary table and for some reason i mindlessly executed the following (typical) query:

select *
from qq_messages
where messageid not in
(
select messageid
from qq_message_recipients
);

Unfortunately each table contained several hundreds of thousands of rows, so I had to stop the query after wasting 5 minutes with the CPU constantly at 98%.

Who knows why we always come up with the worst queries first.

In these cases you can bet there is a better way of designing your SQL, especially after looking at the astronomical cost reported by the optimizer.


Indeed i rewrote the statement using a NOT EXISTS clause and i immediately got a better looking plan:

select *
from qq_messages a
where not exists
(
select 1
from qq_message_recipients b
where b.messageid = a.messageid
);

This query returned 7 rows in 0.3 seconds.

But is there any other way to get this result?
Oh yes.
If you like set algebra, then here is a query that does exactly the same job in a different fashion in about 1.6 seconds, that is 5 times slower than the best one, but considerably faster than the worst scenario:

select * from qq_messages
minus
select * from qq_messages a
where a.messageid in
(
 select b.messageid
   from qq_message_recipients b
);

Again, there can be some improvement by replacing IN with EXISTS:

select * from qq_messages
minus
select * from qq_messages a
where exists (select 1 from qq_message_recipients b
              where b.messageid = a.messageid);

This query took 1.2 seconds. Notice the cost of 13620 in contrast with 13651. Very similar figures for a query that runs 25% faster.

The scenario above should turn out to be quite useful for those who are not (yet) mastering Oracle SQL, because it teaches at least three important lessons:

• the NOT IN clause is not the solution for all situations, no matter if it is the most natural;
• the explain plan gives you a quick qualitative estimation of how good your SQL is;
• there is usually an alternate solution for the same problem, so, unless you are really satisfied with your first shot, you'd better off looking at alternatives.

While the number returned as the total cost is not to be taken as a forecast of the time required to execute the query, it can certainly be considered as an indicator of the resources required to carry out the statement. This however doesn't mean that two queries with similar costs will take the same time to execute, as we have seen.
Cost misinterpretation is an old standing issue in the community of developers.

It's easy to think that a low number means fast and a high number means slow, whatever fast and slow mean in your specific case, so the temptation of skipping a real test basing purely on the cost indication of the queries is always around the corner. The problem is also in that a database is not a static thing, so the total number of records in each table may vary over time, even by large numbers, and the execution plan will change as well, if the statistics are up to date, so don't forget to think about how/when it's the best time to refresh the stats, that is as important as developing "good" procedures.

Even after carefully evaluating different alternatives, your job isn't finished yet. A further step, in case results are difficult to interpret may be to run TKPROF and find out which SQL is the most resource intensive. And after that you have to try it out in the real world, with concurrent users, if that applies, and see if your program survives user acceptance test. If not, it could mean that you found an optimal solution for a suboptimal architecture, which means redesigning parts of your database objects.

But that is definetely another story.

ORA-00907: missing right parenthesis

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

There are at least a couple of situations where you may come across this syntax error message (see update at the bottom):

1. a trivial mistake, something that you could easily avoid by using SQLDeveloper's editor, that comes with a cool matching parentheses visual checking feature;
2. as a result of an elusive forbidden syntax form that is not clearly documented in the official books (through version 11.1 at time of writing).

Let's forget the former case and go straight to the latter case.

Don't get my example wrong, i know this is not the best way of doing this, but I'll talk about that later on:

select object_name, object_type
from user_objects
where object_type in (
select column_value
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1);

ORA-00907: missing right parenthesis

Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:

select object_name, object_type
from user_objects
where object_type in ('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE');

is perfectly equivalent to:

select object_name, object_type
from user_objects
where object_type in ('FUNCTION,TABLE,SYNONYM,PROCEDURE,VIEW');

Oracle may or may not process the rows in the same order, probably it will depend on which blocks it finds in the buffer cache, which can vary between the first execution and the second execution, so don't rely on swapping items in the IN clause if you want to change the order in which they are processed.

Let's go back to the original query: in the example above i used the custom function csv_to_table to convey the idea of a query with a parametric IN clause, that is a clause that is not made up of literal values but that could accept a string parameter (a comma separated string list) that could be set somewhere else.

So, if the purpose of this query was to process the rows in the USER_OBJECTS view in the specified object type order, then we would have to rewrite the query completely:

select a.object_name, a.object_type
from user_objects a, (
select column_value object_type, rownum as n
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by n) b
where a.object_type = b.object_type
order by b.n;

Note also that there are at least two ways of solving the syntax problem without touching the ORDER BY clause:

creating a view as follows:

create or replace my_object_types_v as
select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1;

and then issue:

SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

or alternatively use the WITH clause:

WITH my_object_types_v as (select column_value as object_type
from table(csv_to_table('SYNONYM,PROCEDURE,FUNCTION,VIEW,TABLE'))
order by 1)
SELECT object_name, object_type
from user_objects
where object_type in (select object_type from my_object_types_v);

but as already remarked, neither of the two will force Oracle to process the rows of USER_OBJECTS in the given order.

Updated february 29, 2008:
This error is also returned when calling a user-defined (PL/SQL) function with named parameters inside a SQL statement:

SELECT my_function(p_input_value => 0) AS my_fn
FROM DUAL;

ORA-00907: missing right parenthesis

Named parameters are only allowed in PL/SQL programs (but not in SQL statements inside PL/SQL programs), therefore the solution is to pass parameters in positional form.

Read more about the different parameter passing options in the Oracle 10G documentation.

See message translations for ORA-00907 and search additional resources.

When a query with IN subquery may not return the expected rows

One of the most frequent misunderstandings with the IN clause is caused by the presence of NULL values on both sides of the comparison.

Let's build a simple test case:

create table test1 (a number, d varchar2(30))
/
create table test2 (b number)
/
insert into test1 values(null, 'a is null ')
/
insert into test1 values(1, 'a = 1')
/
insert into test2 values(null)
/
insert into test2 values(1)
/
commit
/
select * from test1
where a in (select b from test2);

result:

A D
- -----
1 a = 1

If for some reason you're assuming that null=null should return TRUE, well, you're taking a wrong assumption for two simple reasons:

1. NULL = NULL always returns FALSE.
2. NULL must be dealt with using the IS operator, not the equal (=) sign.
   

If you imagine that the IN clause could be regarded as a series of equality comparisons, you can easily make out why it can fail if values on either side of the comparison are NULLs.

In case for some reason you want to force a = b to return TRUE even in case of NULLs, then you can try this sub-optimal technique:

select * from test1
where nvl(a,-1) in (select nvl(b,-1) from test2);

A D
- ---------
a is null
1 a = 1

applying NVL to column a however causes the optimizer to rule out any index on table test1, which will probably result in a full table scan and the consequent poor performance of the query if table test1 is big.

These types of problems can be further complicated when you use n-tuples of values.

create table test3 (a number, b number, d varchar2(30))
/
create table test4 (c number, d number)
/
insert into test3 values(null, null, 'a and b are null ')
/
insert into test3 values(1, null, 'a = 1 and b is null')
/
insert into test3 values(1, 1, 'a = 1 and b = 1')
/
insert into test4 values(null, null)
/
insert into test4 values(1, null)
/
insert into test4 values(1, 1)
/
commit
/

select * from test3
where (a,b) in (select c,d from test4);

Result:

A B D
- - ---------------
1 1 a = 1 and b = 1

Conclusion:
having NULLs in the columns you retrieve may lead to unexpected results when using the IN clause, so you need to know very well the data you are working on.

You may think that this sort of tongue-in-cheek advice, however when you're called on to explain why a query doesn't work as expected on a table populated by someone else in a foreign application and without much possibility of inspecting the data yourself, you need to be prepared for the worst.