If I search with and FK ID_Entity on a big table, takes 0.05s to return 2000 rows, but if I search with the FK and a Varchar2(1) takes too long!

Tags:
Oracle
Oracle 11g
I have a table with 3000000 rows.
With a
Select count(1) from  Conceptos where id_entidad = 1
Takes 0.05s to return the number 2000
But if I search
Select count(1) from  Conceptos where id_entidad = 1 and Esta_Aprobado = 'S'
Then it takes 7s to return the same number
I hace index over Id Entidad and Esta_Aprobado, ane even that takes Too long!
Its crazy how bad it works.


Software/Hardware used:
Oracle 11g
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Have you run an autotrace on the query, or at least an explain plan?  It
could be that the index on id_entidad works better but the optimizer is
incorrectly choosing the one on esta_aprobado.

If that is the
case, you could use a hint to tell the optimizer which index to use, or
maybe just analyzing the table to update statistics would make it choose
the correct one.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: