How can we improve query performance with out indexes?


  • Hello Experts,

    I have a problem with table(calc) which contain 3 crore records and table doesn't have index, on that table one of the view(View_A) created.

    When i use the view in  below query SELECT count(*)
      Table A INNER JOIN Table B ON (A.a=B.b)
       LEFT OUTER JOIN View_A ON ( Table A.a=View_A.a)
       LEFT OUTER JOIN View_B ON (Table A.a=View_B.a)

    In above query View_A is causing the problem, view_A is created on Calc table. One more thing when i execute select statement on the view it was running fine.

    With out View_A query fetching data fine. Update stats of the table also fine. When i run cost plan for scanning only cost % is 90.

    Can any help me please?.

    Thank you all.



    Wednesday, October 09, 2013 3:04 PM

All replies

  • Jason,

    Not sure what you are trying to do. But outer joins are bad for performance. So try to avoid them.

    You also say that you have a view on a calc table. What are you calculating? Are you using user defined functions maybe?



    Friday, January 17, 2014 1:56 PM