I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this.

AND   REF_RT_ID in (13, 63) 
AND   PHONE_NUMBER like '123%' 

There are two INDEXES on the table, one on PHONE_NUMBER and one on another column not used here.

I struggled to get the query to run faster and one of the more senior guys at work suggested I force a FULL TABLE SCAN. I did this and it worked wonders.

Even though it worked, it goes against the general concept that full table scans are bad.

Could someone please explain why this is the case?

  • How selective is that index (i.e. what percentage of rows would be selected if it was the only where filter)? How big is it compared to the table? – Mat Oct 30 '12 at 11:38
  • Can't say what the percentage of rows would be from the top of my head, but the index is approx. 130GB (roughly 10% of the table) in size. – Pieter van Niekerk Oct 30 '12 at 12:29
  • A full table scan is not always bad. The "general concept" that they are bad is a misconception: asktom.oracle.com/pls/asktom/… – a_horse_with_no_name Oct 30 '12 at 13:46

[...] it goes against the general concept that full table scans are bad.

This has exceptions, like all general concepts. A full table scan can be less expensive than an index scan followed by table access by rowid - sometimes much less expensive.

Remember that:

  • Index blocks need to be read too, in addition to the table's blocks. If the index is large (a sizable percentage of the table's size), the pressure on the SGA (and associates latches) might be noticeable
  • If the sort order of the index doesn't match the way the data is stored in the actual table, the number of logical I/Os necessary to fulfill the query could be (potentially much) more than the number of LIOs to do the full table scan. (The index's clustering factor is one of the indicators you can look at to estimate this.)

I find the figures in this article Oracle clustering_factor tips explain the issue pretty well.

Essentially, if scanning via your index makes the data block I/O requests jump all over your table, the overall cost is going to be higher than if you could do large sequential reads. An FTS is more likely to use multi-block direct-path reads, bypassing the SGA entirely, which is also potentially good - no "cache thrashing", less latching.

If you have a covering index, chances are that's going to beat a full table scan all the time. If not, it's going to depend on what percentage of actual blocks (data + index) will need to be processed (the index's selectivity for that query), and how well they're "physically sorted" with respect to each-other.

As to why the optimizer is picking the "wrong" path for you here: hard to tell. Stale statistics on the index or table could be an issue like always, the estimate calculated base on the LIKE might be off for certain patterns, instance parameters could favor indexes a bit too much, ... If this is the only query that's misbehaving, and your stats are up to date, using a /*+ full */ hint doesn't sound too bad.

| improve this answer | |

Edit Matt pointed out that this is a Oracle question, I thought it was for MS SQL Server. I'm not sure if this answer applies to MS SQL Server but if it's OK I'm going to leave this up just in case it does apply and if it can help others. MODS if this is not supposed to be here please delete or let me know how! Thanks. End edit

Based on your question, you are missing a covering index. What that means is SQL servers is conducting bookmark look ups which could be really slow.

What's interesting is that SQL Server is not doing a table scan using the query optimizer. Are your statistics up to date? Is this a very selective query?

Also, "included" columns could be very useful in this case. A included column doesn't have a pointer in the index b tree non leaf nodes, but the column value is included on the index leaf page thus avoiding the need for expensive bookmark look up operations which can take a lot of physical IO.

I don't like to make indexes per query but if you have 2 single column indexes, merging those 2 into 1 index then adding the "included" columns from your select statement should be very useful.

| improve this answer | |
  • Hum. Question is tagged Oracle, not SQL Server. I'm not sure what "Bookmark lookups" are (not a term used in Oracle). Oracle indexes store the indexed values in their structure (with a twist for compressed indexes) so if I understand your remarks correctly, OP already has that. – Mat Oct 30 '12 at 16:44
  • Oh thanks Mat. I didn't realize. I was reading/typing from my smartphone before heading to work *(lol). Since you know Oracle let me pick your brain for a minute and see if my answer is valid for Oracle as well. Does the answer of 'adding a index which combines both single column indexes and also includes all the 'select' columns' apply in this case in Oracle world? Thanks! – Ali Razeghi Oct 30 '12 at 17:18
  • What's the standard procedure in stackexchange in a case like this? Do I just delete my answer since it was a "MS SQL Server" answer instead of "Oracle"? Pardon, it's the first time I've answered a Oracle question by mistake. Thanks! – Ali Razeghi Oct 30 '12 at 17:22

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.