Hi, I have a query only join two tables, one table has about 10 million rows, the other has 4 thousand rows, the query took about 200 seconds to return 700 thousand rows

This is the query:

Code:
SELECT count(1)
  FROM table1 INNER JOIN table2 ON ( table1.col1 = table2.cola 
                                     AND
                                     table1.col2 = table2.colb)
 WHERE table1.col3<=SYSDATE
   AND (
        (
         table1.col4='a' 
         AND 
         table1.col5<100
        )
        OR
        (
         table1.col4='b' 
         AND 
         (
          table1.col5=10 
          OR
          table1.col5=20
         )
        )
       ) 
   AND table1.col6<>'abc' 
   AND table1.col6<>'def' 
   AND table1.col6<>'ghi'
   AND table2.colc='xy' 
   AND table2.cold='z'
This is the output of ultxpls.sql

Code:
SELECT STATEMENT, GOAL = FIRST_ROWS			Cost=87796	Cardinality=1	Bytes=39
 SORT AGGREGATE				Cardinality=1	Bytes=39
  HASH JOIN			Cost=87796	Cardinality=34711	Bytes=1353729
   TABLE ACCESS FULL	Object owner=joe	Object name=table2	Cost=25	Cardinality=91	Bytes=1274
   TABLE ACCESS FULL	Object owner=joe	Object name=table1	Cost=87756	Cardinality=1461194	Bytes=36529850
                                                       
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("table1"."col1"="table2"."colb")
   3 - filter("table2"."colc"='xy' AND "table2"."cold"='z'
              AND "table2"."colb"='001')
   4 - filter("table1"."col3"<=SYSDATE@! AND
              ("table1"."col4"='a' AND "table1"."col5"<100 OR
              "table1"."col4"='b' AND ("table1"."col5"=10 OR
              "table1"."col5"=20)) AND "table1"."col6"<>'abc' AND
              "table1"."col6"<>'def' AND "table1"."col6"<>'ghi'
              AND "table1"."WHSE"='001')
I want to know why it is doing full table scan, I have index on:

table1 (col1, col2)
table2(col2, col1) primary key

Thanks