0

I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan.

Basically, I've got a single table that is very large (27 million rows) with an index on ScheduleID. What I'm trying to do is get all the records that have a ScheduleID that match one of the ScheduleIDs in a given list (currently stored in a temp table). The temp table will probably have around 100 rows, although it could be up to 500 rows. And for each row in temp row, it will have up to around 10 matching records in the large table. So we are talking about the query returning about 5000 rows max (out of 27 million).

Now, for some reason, Oracle thinks that the best solution is to use a hash join and do a full table scan of the large table. You can see the query and its explain plan that Oracle generates below.

select scheduleintervalid, io_scheduleinterval.version
from io_scheduleinterval
inner join io_querytemp tmp on tmp.data = scheduleid

Oracle Explain Plan

This doesn't seem to make any sense to me. It is not taking advantage of the index on ScheduleID, which I would think would make the query significantly faster. Now from my understanding, the hash join couldn't take advantage of the index, but wouldn't a nested loop join be much faster. Am I missing something here?

Is there something else I can do to speed up the query (I've tried things like using a dynamic IN clause)?

Thanks for the help!

  • Is the index on scheduleid valid/usable? Do you have histograms on that column on either table? (check dba_tab_col_statistics, suitably filtered - posting the output of that might be interesting, but be careful low_value and high_value can be decoded if that data is potentially sensitive) Are there non-null constraints on both columns? – Mat Mar 30 '15 at 14:47
  • There are not non-null constraints on both columns. There is a non-null constraint on IO_ScheduleInterval.ScheduleID but there is no such constraint on IO_QueryTemp.Data (although there is no reason that there couldn't be). Could that potentially cause performance issues? – David R. Mar 30 '15 at 14:56
  • In case I wasn't clear, IO_QueryTemp is a GTT and has no indexes or constraints. – David R. Mar 30 '15 at 14:59
1

Answer left as comments by the question author

I think I may have found the issue, and it definitely makes sense. The type of the data column on the temp table was nvarchar2(100) while the type of the scheduleid column was char(22).

So apparently with the different types, the index cannot be used. When I added a to_char to the data column, it was able to finally use the index.

This temp table is also used in some other joins, where the data is of type varchar(100). In hindsight, maybe I could have had multiple columns on the temp table for different types, but what I have now works.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.