two cases of missing indexes

Case 1:

SQL> select status, count(*) from dbaantispam.tb_sms_out group by status;

    STATUS   COUNT(*)
---------- ----------
         1     112459


SQL> alter session set current_schema=dbaantispam;

Session altered.

SQL> set autotrace on
SP2-0613: Unable to verify PLAN_TABLE format or existence
SP2-0611: Error enabling EXPLAIN report

cd  $ORACLE_HOME/rdbms/admin/

SQL> @utlxplan

Table created.

SQL> set autotrace on

SQL> conn /
Connected.
SQL> set autotrace on


SQL>  SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   2    1     INDEX (FULL SCAN) OF 'SMS_OUT_ID_PK' (UNIQUE)




Statistics
----------------------------------------------------------
        248  recursive calls
          0  db block gets
      31466  consistent gets
       3072  physical reads
          0  redo size
        467  bytes sent via SQL*Net to client
        461  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          0  rows processed

As you can see there are lot of  consistent reads and physical reads .

SQL> @chk_indcol
Enter value for tbl: TB_SMS_OUT
old   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('&tbl')
new   1: select index_name,column_name,column_position from dba_ind_columns where table_name=upper('TB_SMS_OUT')

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION
------------------------------ -------------------- ---------------
SMS_OUT_ID_PK                  SMS_OUT_ID                         1

-- No index found on STATUS column
SQL> create index dbaantispam.tb_sms_out_statusidx on tb_sms_out(status) tablespace antispam_idx;

Index created.



SQL>  SELECT sms_out_id, text, msisdn, messagetype FROM dbaantispam.tb_sms_out WHERE status = 0 ORDER BY sms_out_id ASC;

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_SMS_OUT'
   3    2       INDEX (RANGE SCAN) OF 'TB_SMS_OUT_STATUSIDX' (NON-UNIQ
          UE)





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

2 consistent reads and 1 physical reads only.



Case 2 :


alter session set current_schema=dbamsgctr;


SQL> set timing on
SQL> set pages 1000
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
  2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
  3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
  4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');


'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:07:21.94

Execution Plan
----------------------------------------------------------




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     380503  consistent gets
     380195  physical reads
          0  redo size
        586  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Huge amounts of logical reads, because of this statement, during 3 hours ,  the "Buffer Cache Reads" is 278.4G

SQL> set timing on pages 1000
SQL> create index msg_idx2  on tb_msg_transaction(transaction_timestamp) tablespace msgctr_idx nologging parallel (degree 3 );


Index created.

Elapsed: 00:14:55.86

alter index msg_idx2 logging noparallel;



SQL> set autotrace on
SQL> SELECT '11:00-11:59', COUNT(destination_type) FROM dbamsgctr.tb_msg_transaction
  2   WHERE destination_type = 0 AND msg_type LIKE '%SMS%'
  3   AND transaction_timestamp >= TO_DATE('2010-03-16 11:00:00', 'yyyy-mm-dd hh24:mi:ss')
  4   AND transaction_timestamp <= TO_DATE('2010-03-16 11:59:59', 'yyyy-mm-dd hh24:mi:ss');

'11:00-11:5 COUNT(DESTINATION_TYPE)
----------- -----------------------
11:00-11:59                       0

Elapsed: 00:00:02.81

Execution Plan
----------------------------------------------------------




Statistics
----------------------------------------------------------
        164  recursive calls
          0  db block gets
         28  consistent gets
          4  physical reads
          0  redo size
        586  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tremendous improvement !      minutes of run becomes 3 seconds !!! 
Again , housekeeping and Oracle developer understands  how Oracle database works is important.