CODEWITHSUNDEEP
sqloracle-database
Albert
Albert

Reputation: 1216

Query takes a long time

I have this query (in oracle) which takes a very long time (between 15-30secs) (query1):

SELECT numcen
  FROM centros
 WHERE TO_NUMBER (centros.numcen) = TO_NUMBER (?)
    OR TO_NUMBER (centros.numcen) IN (
          SELECT TO_NUMBER (dc.centro)
            FROM datos_centro dc, centros c
           WHERE TO_NUMBER (c.numcen) = TO_NUMBER (dc.centro)
             AND TO_NUMBER (dc.centro_superior) = TO_NUMBER (?));

I don't know why, because it's a very simple query. I thought it was because of subquery inside IN, but if I run such subquery (query2):

SELECT TO_NUMBER (dc.centro)
  FROM datos_centro dc, centros c
 WHERE TO_NUMBER (c.numcen) = TO_NUMBER (dc.centro)
   AND TO_NUMBER (dc.centro_superior) = TO_NUMBER (?)

It only takes 100-200ms. Even more, if I run query2 and I put its result inside IN in query1 replacing subquery, result is immediate.

I can't run an Explain Plan because I have no rights. It's even worse under if I run under mysql (replacing TO_NUMBER by CAST). It takes more than 2mins, which is unacceptable.

So, is there a way to improve first query (query1)? Should I split into two? Will it be the same query if I replace OR by UNION (which is much faster)?

Any advice will be very welcome. Thanks and sorry for my english.

Upvotes: 0

Views: 376

Answers (3)

Jin
Jin

Reputation: 141

You can do a explain plan on your two queries. explain plan for [ your sql ]; select * from table(dbms_xplan.display);

The optimizer will tell the different between different plans.

Looks to me, the first query will have to use a nested loop and go through each rows in centros and evaluate each record in subquery to filter it out.

The second query will do a hash join between these two tables and do two read then join. This is much less work.

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52336

Referencing centros in your subquery is redundant.

try:

SELECT numcen
  FROM centros
 WHERE TO_NUMBER (centros.numcen) = TO_NUMBER (?)
    OR TO_NUMBER (centros.numcen) IN (
          SELECT TO_NUMBER (dc.centro)
            FROM datos_centro dc
             AND TO_NUMBER (dc.centro_superior) = TO_NUMBER (?));

... or ...

SELECT numcen
  FROM centros
 WHERE TO_NUMBER (centros.numcen) IN (
          SELECT TO_NUMBER (?)
            FROM dual
          UNION ALL
          SELECT TO_NUMBER (dc.centro)
            FROM datos_centro dc
             AND TO_NUMBER (dc.centro_superior) = TO_NUMBER (?));

If you don't need those TO_NUMBER() functions then get rid of them, or add function-based indexes on TO_NUMBER(centros.numcen) and TO_NUMBER (dc.centro_superior).

Upvotes: 0

Avi
Avi

Reputation: 1145

U just try to use global temp table concept ,insert result set of sub qry in temp table and then use select * from temp table or directly join it with your main qry.

I am sure ,this will improve your performance by manyfold , even i had this issue .

Just give try and let me know if u have any prob.

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER) ON COMMIT PRESERVE ROWS;

insert into my_temp_table 

  SELECT TO_NUMBER (dc.centro)
  FROM datos_centro dc, centros c
 WHERE TO_NUMBER (c.numcen) = TO_NUMBER (dc.centro)
   AND TO_NUMBER (dc.centro_superior) = TO_NUMBER (?)

Upvotes: 0

Related Questions