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 – 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>
SQL> drop table foo;
Table dropped.
SQL> 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>
SQL> 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 <= 500000;
500000 rows created.
SQL> commit;
Commit complete.
SQL> create index foo_ix1 on foo(p1, p2, p3);
Index created.
SQL> create index foo_ix2 on foo(cd, p1, p2, p3, st);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'FOO');
PL/SQL procedure successfully completed.
Query structured the same as yours
SQL>
SQL> set timing on
SQL> set autotrace traceonly
SQL> 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>
SQL> 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.)
