Hi,
I have a query which is using a view and some other tables in an ERP system. I have no control over the view code.
Very simply I have something like this:
Select *
From v, b
Where v.id=b.id
And b.column='Something';
b is a table with an index on "column" and v is a view of two tables along the lines of:
Select a.id, a.code, c.description
From a, c
Where a.id=c.id;
Where both a and b have an index defined on id = let's say a_pk and c_pk.
What I would like my plan to do is index scan b.column (which returns just a few rows), then nested loops through the two tables in the view (a and c) using the two indexes (in whatever order).
However what I am finding the query is doing is nested looping table b with one table in the view (i.e. a) and then hash join the results of that to the other table in the view (c). Unfortunately the tables a and c are several million rows and thus this is taking longer than I'd hoped.
Now, I know the reason this is happening is due to a cardinality mis-estimate on table b - the optimizer thinks it's going to get far more rows than it actually is - and I'm looking to resolve that. However it got me thinking - if I wasn't using the view I could use a hint to drive the index usage:
Select /*+index(c c_pk)*/ *
From a, b, c
Where v.id=b.id
And b.id=c.id
And b.column='Something';
however how would I go about pushing that into the view? I can't use the view alias of course because the view definition might go to the table multiple times. I can't use the alias as defined in the view because my main query might have the same. So is it possible without pulling the code out of the view and bringing it inline?
Remember, I can't modify the view code.