This is a very slow query. Any ideas?

Assume that there are two tables:

create table table1 (field1 number(10), field2 number(10), field3 number(10))
create unique index table11 on table1 (field1) create index table12 on table1 (field2)
create index table13 on table1 (field3)

create table table2 (field1 number(10), field2 number(10)) create unique index table21 on table1 (field1) create index table22 on table2 (field2)

Also assume that the tables are populated as follows:

table1:

field1	field2	field3
1	1	1
2	2	2
3	3	3


....

n	n	n

table2:
field1	field2
1	1
2	1
3	1


....

n	1

Here is the query:

select table1.* from table1, table2 t1, table2 t2 where (t1.field1 = table1.field2 and t2.field1 = table1.field3) and (table2.field2 = 1 and table2.field2 = 1) order by table1.field1

Oracle 7.3.2.2 does not like this query. I am running the query through the Oracle ODBC driver 2.00.0301 available from the Oracle Web site.

Does Oracle not like the table aliasing? MS SQL Server does not suffer from this query, but Oracle takes a bit (5 seconds on Pentium Pro 200's) before a result set is returned.

Any help would be appreciated!

Tom Waterhouse
ABTCorporation
tomw_at_abtcorp.com Received on Tue Jun 24 1997 - 00:00:00 CDT