OPTIMIZING A QUERY THAT INCLUDES A LARGE IN LIST

1 . A method for optimizing a SQL query, in which the SQL query includes a an IN-List query statement to access data in a table, where the method includes:

concluding that an IN-List exceeds a predetermined size;

removing the IN-List query statement; and

inserting a query which utilizes a join operation.

2 . The method of claim 1 further including:

concluding that the IN-List query statement is located within a WHERE clause;

adding a join between the IN-List and the table; and

replacing the IN-List with a join condition in the WHERE clause.

3 . The method of claim 2 further including:

concluding that the clause is an ON clause;

concluding that the join is to an inner table; and

inserting the join statement between the inner table and the IN-List query.

4 . The method of claim 3 further including:

concluding that the join is to an outer table; and

replacing the IN-List with the new join condition.

5 . The method of claim 1 further including:

concluding that no WHERE or ON clause exists; and

placing the IN-List in a spool so as to join the spool with the table.

6 . A database system for accessing a database, the database system including:

a parallel processing system including one or more nodes and a plurality of CPUs, each of the one or more nodes providing access to one or more of the CPUs;

a plurality of virtual processes, each of the one or more CPUs providing access to one or more processes, where each process is configured to manage data stored in one of a plurality of data-storage facilities; and

an optimizer configured to optimize a plan for executing a query including an IN-List query statement to access data in a table, where the optimizer is configured to:

conclude that an IN-List exceeds a predetermined size;

remove the IN-List query statement; and

insert a rewritten query which utilizes a join operation.

7 . The database system of claim 6 where the optimizer is configured to:

conclude the IN-List query statement is located within a WHERE clause;

add a join between the IN-List and the table; and

replace the IN-List with a join operation in the WHERE clause.

8 . The database system of claim 7 where the optimizer is configured to:

conclude that the clause is an ON clause;

conclude that the join is to an inner table; and

insert the join condition between the inner table and the IN-List query.

9 . The database system of claim 6 where the optimizer is configured to:

conclude that the join is to an outer table; and

replace the IN-List with the new join condition.

10 . The database system of claim 6 where the optimizer is configured to:

conclude that no WHERE or ON clause exists; and

place the IN-List in a spool to join the spool with the table.

11 . A computer program, stored on a tangible storage medium, for use in optimizing a query plan for executing a query to access data in a table, where the query includes an IN-LIST query statement, the program including executable instructions that cause a computer to:

conclude that the size of an IN-List exceeds a predetermined size;

remove the IN-List query statement; and

insert a rewritten query which includes a join operation.

12 . The computer program of claim 11 including executable instructions that cause a computer to:

conclude that the IN-List query statement is located within a WHERE clause

add a join between the IN-List and the table; and

replace the IN-List with a join condition in the WHERE clause.

13 . The computer program of claim 12 including executable instructions that cause a computer to:

conclude that the clause is an ON clause;

conclude that the join is to an inner table; and

insert the join condition between the inner table and the IN-List query.

14 . The computer program of claim 13 including executable instructions that cause a computer to:

conclude that the join is to an outer table; and

replace the IN-List with the new join condition.

15 . The computer program of claim 11 including executable instructions that cause a computer to:

conclude that no WHERE or ON clause exists; and

place the IN-List in a spool so as to join the spool with the table.