Re: slow select execution

cbortolin_at_skynet.be (Christian) wrote in message news:<77db1649.0110051422.68fe50a0_at_posting.google.com>...
> Hi
>
>
> we launched the following select :
> select * from fact.access_control wher ac_inst_nm = '003670029'
>
> this select is executed in 0.02 seconds
>
> the following select goes a lot slower (3 seconds ! ! !)
>
> select * from fact.access_control where ac_inst_nm = '003670029' and
> ac_obj_type = 256
>
> we also tried (without a faster response) :
> select * from fact.access_control where ac_obj_type = 256 and
> ac_inst_nm = '003670029'
>
> oracle 8.1.5
> key on ac_inst_nm
> key on ac_inst_nm, ac_xcb, ac_obj_type, ac_xxx
>
> Can anyone give me some hints to get these answers as fast as with the
> first select ?
>
> Thanks
>
> Christian

Christian,
  Although you have a composite index on key on ac_inst_nm, ac_xcb, ac_obj_type, ac_xxx, you may need to supply a hint to the DB inorder to make sure it uses the index you want. Also, because it is a composite index and that you are not using ac_xcb in your query it could degrade the search. Another thing to consider is if the your tables are using partitioning and the optimizer. I have seen issues in the past when the optimizer will override the indexes and cause slower than expected queries. Hope this helps.

Joe Received on Sat Oct 06 2001 - 06:30:58 CEST