Member Avatar for rje7

Hi, i have two tables both 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.

from tablea a, tableb b partition(part_00)
            where
            a.col1=b.col1
            a.col2 is null
            b.col3 is null
            a.col4=b.col4

below are the indexes. the order in which it is specified in the create index syntax is given below.
table a Index: col4, col3, col1
table b index: col4, col2, col1
Thanks in Advance.

  • 2 Contributors
  • 1 Reply
  • 170 Views
  • 4 Months Discussion Span
  • Latest Post Latest Post by pclfw
Member Avatar for pclfw

I am not an Oricle DBA, but in MS SQL the WHERE clause needs to be very specific before it actually fetches directly via the index. I see no reason to expect that Oricle is any different.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.