CODEWITHSUNDEEP
oracle10gdatabase-tuning
user3375522
user3375522

Reputation: 11

I need a suggestion on query tuning techiniques

select /* all_rows */x1,x2,x3 
from view_x
where x1 in 
(select a.b1 from mytable a,mytable2 b
where a.b2=b.c2)

as view_x is a view, which is trying to get the data from the other source(@othertable_dblink)

I have index on b1. but as view_x is a view , I don't have privilege to create a index on that.

NOTE: Due to this, the mytable and mytable2 are going on error like "table access full"

My Question: How can I reduce the time on this, by not allowing it to go for "table access full"

if there are any query tuning techniques , pls let me know.

Upvotes: 1

Views: 30

Answers (1)

Mat
Mat

Reputation: 206689

"Table access full" is not an error, it's a data access path. Sometimes it's even the optimal one.

If you're sure the performance problem is on the sub-select, to speed that up the optimal indexes are likely:

  • Index on mytable2(c2)
  • Index on mytable1(b2,b1) (in that order)

The fields that need to be indexed to be useful for the join are mytable2.c2 and mytable1.b2, an index on mytable.b1 alone won't help for the join at all.

But depending on the size of the tables and the number of rows returned by that join, full scans might be the fastest option.

Upvotes: 3

Related Questions