Slow query need suggestion on rewrite or index
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