Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.)

share|improve this answer
    
+1 for the FTS being preferred to a ranged scan, but your advice about indexing will not work since NULL values are not indexed. –  Vincent Malgrat Sep 13 '12 at 9:01
    
The index can be used since we're interested in not null rows - the optimizer knows that no row satisfying that filter will be absent from the index. Has to go to the table to find the null rows though, but that's not the case here. Now whether the index will be used for that specific query, I don't know. –  Mat Sep 13 '12 at 9:22
    
You're right, my bad, I misread the filter. –  Vincent Malgrat Sep 13 '12 at 9:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.