Group By not using index

Question

There is a table which has trades and its row count is 220 million, one of column is counterparty. The column is indexed. If I run a normal query like:

select * 
  from <table> 
 where counterparty = 'X'

The plan shows it uses index. Where as if I use group by on same column, it doesn't use index and does table scan. i.e.: for below query:

select counterparty, count(*)
  from <table>
 group by counterparty

Could you please advise, why it's not using the index for group by? FYI - I have already run the db stats.

FYI - the plan for 1st and second query is shown below:

Note - we are migrating data from Sybase to oracle, when I use same group by in Sybase with same indexes. The query uses indexes, but not in oracle.

First

Plan hash value: 350128866

| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |                     |  2209 |  1469K|   914   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| FXCASHTRADE         |  2209 |  1469K|   914   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | SCB_FXCASHTRADE_002 |  2209 |       |    11   (0)| 00:00:01 |


Predicate Information (identified by operation id):

    2 - access("COUNTERPARTY"='test')

Second

> Plan hash value: 2920872612

| Id  | Operation          | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |             |   100K|  2151K|       |  6558K  (1)| 00:00:38 |
|   1 |  HASH GROUP BY     |             |   100K|  2151K|  6780M|  6558K  (1)| 00:00:38 |
|   2 |   TABLE ACCESS FULL| FXCASHTRADE |   221M|  4643M|       |  6034K  (1)| 00:00:35 |

Answer 1

I am going to make an educated guess and say that counterparty is defined as a nullable column. As such, Oracle can't solely rely on the index to generate the results of your group by query, since null values need to be included in the results, but (Oracle) indexes don't include null values. With that in mind, a full table scan makes sense.

If there is no good reason for counterparty to be nullable, go ahead and make it not null. The execution plan should then change to use the index as expected.

Alternatively, if you can't make that change, but you don't care about null values for this particular query, you can tweak the query to filter our null values explicitly. This should also result in a better execution plan.

select counterparty, count(*)
  from tbl
 where counterparty is not null -- add this filter
 group by counterparty

Note: I'm no Sybase expert, but I assume that indexes include null values. Oracle indexes do not include null values. That would explain the difference in execution plan between both databases.