2 Replies Latest reply: Apr 15, 2013 12:08 PM by Chandrakaanth Ramamurthy RSS

    Avoide FULL scan tables for Big tables

      Dear all,
      I am issuing SELECT statement by using 2 tables (Suppose tab1, tab2). there are 3 matching columns in each tables which are using to join both tables. I created Index on all 3 columns in both tables. But when i checked the Explain plan, Oracle goes to FULL SCAN for both tables. I want to avoid it. I also tryied Composite Index. but NO LUCK. below steps i followed:

      - Created indexes in columns ( id,type, branch) in tab1 and similarly on tab2.
      - Analyzed tab1 and tab2
      issued select statement and applying joins like "Where tab1.id=tab2.in and tab1.type=tab2.type and tab1.branch=tab2.branch)".

      - Please note the volume of tab1 is 400,000,000 and tab2 is 210,000,000. and there is no partitions in any tables.

      I am using Oracle 11g.

      I request you to advice to manage Big volumn data.

      Thanks in advance.