SQL query not using index and explain plan gives very high cost

[skaushik]

Hi all,

I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table column but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below

select * from history_table ht, small_table st
where ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 748 M 569565
MERGE JOIN 748 M 152G 569565
SORT JOIN 965 51 K 97
TABLE ACCESS FULL USER1.SMALL_TABLE 965 51 K 21
SORT JOIN 15 M 2G 569468
TABLE ACCESS FULL USER1.HISTORY_TABLE 15 M 2G 137725

[skaushik]

Hi all,

I was working on a SQL query where a history table is joined with a small table to get some information. There is an index on the history table (columnA) but the explain plan tells that there is a full table scan on both the tables. The query and the explain plan is below

select * from history_table ht, small_table st
where ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 748 M 569565
MERGE JOIN 748 M 152G 569565
SORT JOIN 965 51 K 97
TABLE ACCESS FULL USER1.SMALL_TABLE 965 51 K 21
SORT JOIN 15 M 2G 569468
TABLE ACCESS FULL USER1.HISTORY_TABLE 15 M 2G 137725

I also tried using hints to force the usage of index on column A by the following query.

select /*+ index(history_table ColumnA_IDX)*/ * from history_table ht, small_table st
where ht.columnA > st.columnB
and sysdate between st.datetime1 and st.datetime2;

The explain plan as a result of this query also was the same as above. I was not able to understand the reason for this behavior by the optimizer. Please let me know if any one knows the reason for this. This query is frequently used and so is causing a huge performance problem for the database.

Thanks a lot.

[debasisdas]

Please find a related discussion here .

[debasisdas]

Both the threads are merged for better management of the forum.

[skaushik]

Thanks. The discussion in that thread was very useful. I am trying out the different suggestions to see if it helps to use the index. Let me know if you can think of some reason too for the query above.

Both the threads are merged for better management of the forum.

[andreasksp]

Please find a related discussion here .

Sorry, but the link is not valid. Can Anyone tell me a valid Link to the Article because I have the same Problem with a few SQL Statements

[Sagar more]

hi..

if ur history table is having more data ..then try once by analysing it and rebuild the index as well...
and if ur current index is on only one column and its not on date column which u r using then drop that index and create composite index on both columns...

i hope this would help

[amitpatel66]

What is your database version?