Have you tried a leading or ordered hint?
select /*+ leading(z) */ x.cola, y.colb, z.colc from tabz z, tabx x, taby y where x.conna = y.conna and x.connb = z.connb and x.noindcol = 'a'
select /*+ ordered */ x.cola, y.colb, z.colc from tabz z, tabx x, taby y where x.conna = y.conna and x.connb = z.connb and x.noindcol = 'a'
Hi,
I have three tables : tabx, taby, tabz. tabx and taby are huge and have around 10 million rows. tabz is small and has 1 million.
query :
select tabx.cola, taby.colb, tabz.colc from tabx, taby, tabz where tabx.conna = taby.conna and tabx.connb = tabz.connb and tabx.noindcol = 'a'
The connecting columns conna and connb have indexes. the tabx.noindcol does not have index.
Ideally I would like to start with full table scan for the small table tabz then go to tabx using index filter then go to taby using index. One full table scan is unavoidable.
Regards
-- //www.freelists.org/webpage/oracle-l