SELECT tb.substation_id as Station,
ta.island_id as Island,
ta.area_number as Anumber
FROM islands ta,
nodes tb
WHERE ta.landname = 'ABC'
AND (ta.island_id = tb.island_id
AND ta.start_date = tb.start_date)
AND ta.start_date <= sysdate
AND nvl(end_date,to_date('31-dec-2499','dd-mon-yyyy')) > sysdate 
GROUP BY ta.area_number

ConnectÚ Ó :
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> set linesize 120
SQL> drop table t_ind
  2  /

Table supprimÚe.

SQL> create table t_ind as select * from all_objects
  2  /

Table crÚÚe.

SQL> alter table t_ind modify created date null
  2  /

Table modifiÚe.

SQL> update t_ind set created = null where rownum < 51
  2  /

50 ligne(s) mise(s) Ó jour.

SQL> commit
  2  /

Validation effectuÚe.

SQL> create index ix_t_ind on t_ind(object_type, created)
  2  /

Index crÚÚ.

SQL> exec dbms_stats.gather_table_stats(user,'T_IND',cascade=>true)

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>
SQL> set autotrace traceonly explain
SQL> select t.object_name
  2    from t_ind t
  3   where t.object_type = 'SYNONYM'
  4     and (created > sysdate - 1000 Or created is null)
  5  /

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2224001492

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   279 |  9765 |    25   (0)| 00:00:01 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_IND    |     2 |    70 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_T_IND |     5 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T_IND    |   277 |  9695 |    23   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_T_IND |   278 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED" IS NULL)
   5 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED">SYSDATE@!-1000 AND
              "CREATED" IS NOT NULL)
       filter(LNNVL("CREATED" IS NULL))

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> set linesize 120
SQL> drop table t_ind
  2  /

Table supprimÚe.

SQL> create table t_ind as select * from all_objects
  2  /

Table crÚÚe.

SQL> alter table t_ind modify created date null
  2  /

Table modifiÚe.

SQL> update t_ind set created = null where rownum < 51
  2  /

50 ligne(s) mise(s) Ó jour.

SQL> commit
  2  /

Validation effectuÚe.

SQL>
SQL> create index ix_t_ind on t_ind(object_type, created)
  2  /

Index crÚÚ.

SQL> create index ix2_t_ind on t_ind(object_type,  NVL(CREATED,TO_DATE('2499-12-31', 'yyyy-mm-dd')))
  2  /

Index crÚÚ.

SQL> exec dbms_stats.gather_table_stats(user,'T_IND',cascade=>true)

ProcÚdure PL/SQL terminÚe avec succÞs.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> select t.object_name
  2    from t_ind t
  3   where t.object_type = 'SYNONYM'
  4     and (created > sysdate - 1000 Or created is null)
  5  /

248 ligne(s) sÚlectionnÚe(s).


Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2224001492

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   280 |  9800 |    25   (0)| 00:00:01 |
|   1 |  CONCATENATION               |          |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_IND    |     2 |    70 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IX_T_IND |     5 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T_IND    |   278 |  9730 |    23   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IX_T_IND |   279 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED" IS NULL)
   5 - access("T"."OBJECT_TYPE"='SYNONYM' AND "CREATED">SYSDATE@!-1000 AND
              "CREATED" IS NOT NULL)
       filter(LNNVL("CREATED" IS NULL))


Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         58  consistent gets
          0  physical reads
          0  redo size
       7828  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        248  rows processed

SQL>
SQL> select
  2         t.object_name
  3    from t_ind t
  4   where t.object_type = 'SYNONYM'
  5     and NVL(CREATED,TO_DATE('2499-12-31', 'yyyy-mm-dd'))> sysdate - 1000
  6  /

248 ligne(s) sÚlectionnÚe(s).


Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 2876975224

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |   423 | 14805 |    34   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_IND     |   423 | 14805 |    34   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX2_T_IND |   423 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("T"."OBJECT_TYPE"='SYNONYM' AND NVL("CREATED",TO_DATE('2499-12-31
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))>SYSDATE@!-1000 AND
              NVL("CREATED",TO_DATE('2499-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) IS NOT
              NULL)


Statistiques
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         56  consistent gets
          0  physical reads
          0  redo size
       7828  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        248  rows processed