Forum Stats

  • 3,728,700 Users
  • 2,245,675 Discussions
  • 7,853,706 Comments

Discussions

Performance Issue

3504038
3504038 Member Posts: 2

Hello All,

We have query that having join of 4 tables 3 are master table contains 2000 records approximate and other is master table contains 5,00,00,000 records approx.

Now we have join like all the master table having reference into transaction table with filter condition.

We are not able to create partition on transaction table.

Transaction table already have 23 index on it.

So can any one help me out here how can I improve the performance of that query.

Thanks in advance.

Regards,

Jinesh Vora

Tagged:
Marco van HoeckelMustafa_KALAYCI

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited July 2017
    3504038 wrote:Hello All,We have query that having join of 4 tables 3 are master table contains 2000 records approximate and other is master table contains 5,00,00,000 records approx.Now we have join like all the master table having reference into transaction table with filter condition.We are not able to create partition on transaction table.Transaction table already have 23 index on it.So can any one help me out here how can I improve the performance of that query.Thanks in advance. Regards,Jinesh Vora

    What query?

    What performance problem?

    You need to share the actual details.

    Tables with 23 indexes? WOW!

    Marco van HoeckelMustafa_KALAYCI
  • jgarry
    jgarry Member Posts: 13,842
    edited July 2017

    It sounds like you have a design problem as well as a performance problem.  Use the methodology John linked to to accurately describe the problem in a way we can help you (sometimes just going through the steps can help you figure it out yourself).  As Andrew alluded to, 23 indices on a transaction table seems suspicious - are they all being used for specific queries, or did someone decide the more the merrier?  What kind of statistics gathering are you doing?  Which exact version/patch level are you on?  Is this custom code or something you bought?

    The essence of performance tuning is to not do unnecessary work.  In this context, that often means the plan the optimizer works out is not the best, as it is not being told correctly what the situation is.  Or it could be simply a bad query.

This discussion has been closed.