optimizing subqueries with in operator

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Richard Gjerde (richard_gjerde_at_yahoo.no)
Date: 05/11/04 

Date: Tue, 11 May 2004 15:00:51 +0200

Can anybody help me with the following problem?

I have a table A with columns A_1, ..., A_n and I want to select those rows
where A_i has the same value as it has on those rows where A_k has a certain
value. I have tried the following query:

select * from A where A_i in (select A_i from A where A_k = 'some value')

The problem is that this query is terribly slow. If I know in advance that
the subquery only returns one row and use "=" instead of "in", it is much
more efficient, but I cannot in general assume this. I have tried various
other things like self join and using "exists", but I have not been able to
improve efficiency much. With a test table with random data and 3,5 million
rows this query takes more than one minute on MSsql while on Oracle after
optimizing it takes 0,1 seconds!

Relevant Pages

  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)
  • RE: DBI-1.48 bind_param_inout produces ORA-01461 colliding with a CLOB
    ... can pass strings as varchar's and have them inserted ... In theory I could write something to query the ... CLOB w/in Oracle is probably what is killing me... ...
    (perl.dbi.users)
  • Re: Equilevant of Oracle for optional records
    ... and a normalized t2 could be more useful, to build the query, and once this ... the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Equilevant of Oracle for optional records
    ... this query is done, then flatten the result? ... example, the Iqaluit Trauma: ... Oracle handles this data structure perfectly but I suspect it might be ...
    (microsoft.public.access.queries)
  • Re: Updatable ... not
    ... I have code in the front-end that imports data from csv files into ... The back-end also links to Oracle tables for data matching and ... work on data in the Oracle table. ... I built a new query, just like the original query, and no, I still ...
    (microsoft.public.access.modulesdaovba)