Why Is oracle ignoring my index? This query is not too complex:

select
    trunc(created_date, 'MONTH') as created_date,
    al.op_name,
    al.region,
    al.alarm_type,
     COUNT(1) as total_new,
     SUM(
        (SELECT
            COUNT(1)
         from alarm_table ial
            WHERE ial.status_alarm = 'SOLVED'
            AND TRUNC(ial.solved_date, 'MONTH') = TRUNC(al.created_date, 'MONTH')
            AND ial.region = al.region
            AND ial.op_name = al.op_name
            AND ial.alarm_type = al.alarm_type)
    ) as total_solved
FROM
    alarm_table al
WHERE
    created_date is not null
group by
    trunc(al.created_date, 'MONTH'),
    al.op_name,
    al.region,
    al.alarm_type
order by trunc(al.created_date, 'MONTH') desc


And here is its explain plan:

Plan
SELECT STATEMENT  ALL_ROWSCost: 5,497           
    3 SORT AGGREGATE  Bytes: 51  Cardinality: 1         
        2 TABLE ACCESS BY INDEX ROWID TABLE USER.alarm_table Cost: 22  Bytes: 51  Cardinality: 1    
            1 INDEX RANGE SCAN INDEX USER.IDX_alarm_table_RESU Cost: 3  Cardinality: 73  
    6 SORT ORDER BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541         
        5 HASH GROUP BY  Cost: 5,497  Bytes: 1,055,263  Cardinality: 24,541     
            4 TABLE ACCESS FULL TABLE USER.alarm_table Cost: 3,025  Bytes: 11,696,387  Cardinality: 272,009  


The Question is: I have an index containing the columns al.op_name, al.region and al.alarm_type (that IDX_alarm_table_RESU, which it even uses on the first part).

Since there is no further joins, why would it be doing the FTS?
The outer select needs to find all rows in your table that match the where clause created_date is not null. That column isn't in that index, so the index (statistically) doesn't help &ndash; doing a full table scan is usually much faster than accessing all rows via an index.

If you had a separate index that could be used for that clause, it still might not be if it is not selective enough (i.e. if you don't have a vast majority of rows with null create dates).

If you can extend that index, try adding trunc(created_date, 'MONTH') to it and changing the (outer) where clause to use that function too (the null will "propagate" through the function).
Then you can try adding the status column too to avoid the table completely.

Indexing the function doesn't appear to work here, but indexing the date should help for the outer query.

But looking at the query more closely, I don't understand the meaning of the sum(select count(1) ...) in the select list, which is the most costly part. I don't believe this will return anything meaningful (count of rows per group times number of rows with status='SOLVED' per group).
If you want the number of rows that have status_alarm = 'SOLVED' within each group (this makes sense to me), you could rather:

sum(case when status_alarm = 'SOLVED' then 1 else 0 end) as total_solved


With that and a covering index, the query is answered with a single index fast full scan.

Test setup

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL&gt; 
SQL&gt; drop table foo;

Table dropped.

SQL&gt; create table foo(
  2      cd date
  3    , p1 number not null
  4    , p2 number not null
  5    , p3 number not null
  6    , st number
  7    , stuff char(250));

Table created.

SQL&gt; 
SQL&gt; insert /*+ append */ into foo
  2    select
  3     case when mod(rownum,20) = 0 then null else sysdate-(rownum/10000) end td
  4    ,round(dbms_random.value(0, 5)) p1
  5    ,round(dbms_random.value(0, 5)) p2
  6    ,round(dbms_random.value(0, 5)) p3
  7    ,round(dbms_random.value(0, 4)) st
  8    ,'lets imagine there are lots more columns in the table'
  9    from dual connect by level &lt;= 500000;

500000 rows created.

SQL&gt; commit;

Commit complete.

SQL&gt; create index foo_ix1 on foo(p1, p2, p3);

Index created.

SQL&gt; create index foo_ix2 on foo(cd, p1, p2, p3, st);

Index created.

SQL&gt; exec dbms_stats.gather_table_stats(ownname =&gt; user, tabname =&gt; 'FOO');

PL/SQL procedure successfully completed.


Query structured the same as yours

SQL&gt; 
SQL&gt; set timing on
SQL&gt; set autotrace traceonly
SQL&gt; select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(
  8      (select count(1)
  9       from foo b
 10       where b.st = 0
 11       and trunc(b.cd, 'MONTH') = trunc(a.cd, 'MONTH')
 12       and b.p1 = a.p1
 13       and b.p2 = a.p2
 14       and b.p3 = b.p3)
 15    ) as st
 16  from
 17    foo a
 18  where
 19    trunc(cd, 'MONTH') is not null
 20  group by
 21    trunc(cd, 'MONTH')
 22    ,p1
 23    ,p2
 24    ,p3
 25  order by
 26    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:04:15.83

Execution Plan
----------------------------------------------------------
Plan hash value: 1720639959

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  7885K|   |  5901   (3)| 00:01:11 |
|   1 |  SORT AGGREGATE        |     |     1 |    20 |   |        |      |
|*  2 |   INDEX FAST FULL SCAN | FOO_IX2 |    28 |   560 |   |   583   (2)| 00:00:07 |
|   3 |  SORT ORDER BY         |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|   4 |   HASH GROUP BY        |     |   475K|  7885K|    14M|  5901   (3)| 00:01:11 |
|*  5 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  7885K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."P1"=:B1 AND "B"."P2"=:B2 AND "B"."ST"=0 AND
          TRUNC(INTERNAL_FUNCTION("B"."CD"),'fmmonth')=:B3)
   5 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
   22699250  consistent gets
      3  physical reads
      0  redo size
      22461  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed


That's a lot of gets.

Modified query

SQL&gt; 
SQL&gt; select
  2    trunc(cd, 'MONTH') cm
  3    ,p1
  4    ,p2
  5    ,p3
  6    ,count(1) as oc
  7    ,sum(case when st = 0 then 1 else 0 end) as sc
  8  from
  9    foo a
 10  where
 11    trunc(cd, 'MONTH') is not null
 12  group by
 13    trunc(cd, 'MONTH')
 14    ,p1
 15    ,p2
 16    ,p3
 17  order by
 18    trunc(cd, 'MONTH');

648 rows selected.

Elapsed: 00:00:00.18

Execution Plan
----------------------------------------------------------
Plan hash value: 4221217160

------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |   475K|  9277K|   |  6438   (2)| 00:01:18 |
|   1 |  SORT ORDER BY         |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|   2 |   HASH GROUP BY        |     |   475K|  9277K|    16M|  6438   (2)| 00:01:18 |
|*  3 |    INDEX FAST FULL SCAN| FOO_IX2 |   475K|  9277K|   |   614   (7)| 00:00:08 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(TRUNC(INTERNAL_FUNCTION("CD"),'fmmonth') IS NOT NULL)


Statistics
----------------------------------------------------------
      1  recursive calls
      0  db block gets
       2125  consistent gets
      0  physical reads
      0  redo size
      21304  bytes sent via SQL*Net to client
    996  bytes received via SQL*Net from client
     45  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
    648  rows processed


(Note that even without any index, this version is orders of magnitude more efficient than the first one.)