Why Is oracle ignoring my index?
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.)