i have two tables in Oracle 11g server one is partitioned and another is having a 3GB of data. I've added a composite index on both the tables based on the where clause, in the order it appears. The output of explain plan after gather stats shows that the sql is accessing both the tables in full scan mode.Sample query below.
select /*+ PARALLEL(a 8) */ b.col1, a.* from tablea a, tableb b partition(part_00) where b.col1=a.col1 a.col2 is null b.col3 <= a.col3 b.col4>a.col4 b.col4=a.col4
The indexes where composite. I've listed them below. the order in which it is specified in the create index syntax is given below.
create index idx_composite_tableb(col4, col3, col1) local; create index idx_composite_tablea(col4, col2);
Thanks in Advance.