By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,454 Members | 1,803 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,454 IT Pros & Developers. It's quick & easy.

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

P: 6
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
Feb 28 '08 #1
Share this Question
Share on Google+
7 Replies


P: 6
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.
Feb 28 '08 #2

debasisdas
Expert 5K+
P: 8,127
Please find a related discussion here .
Feb 29 '08 #3

debasisdas
Expert 5K+
P: 8,127
Both the threads are merged for better management of the forum.
Feb 29 '08 #4

P: 6
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.
Feb 29 '08 #5

P: 1
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
Mar 26 '08 #6

P: n/a
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
Oct 23 '10 #7

amitpatel66
Expert 100+
P: 2,367
What is your database version?
Oct 25 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.