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.