PHIL@PHILL11G2 > CREATE TABLE ranges
  2  (
  3  startr VARCHAR(8),
  4  endr VARCHAR(8)
  5  );

TABLE created.

PHIL@PHILL11G2 > INSERT INTO ranges ( SELECT  UTL_RAW.CAST_FROM_BINARY_INTEGER((object_id*10)), UTL_RAW.CAST_FROM_BINARY_INTEGER((object_id*10)+7) FROM dba_objects);

72286 ROWS created.

PHIL@PHILL11G2 > CREATE INDEX ranges_idxs ON ranges (TO_NUMBER(startr,'XXXXXXXX'));

INDEX created.

PHIL@PHILL11G2 > CREATE INDEX ranges_idxe ON ranges (TO_NUMBER(endr,'XXXXXXXX'));

INDEX created.

PHIL@PHILL11G2 > EXEC dbms_stats.gather_table_stats('PHIL','RANGES');

PL/SQL PROCEDURE successfully completed.

PHIL@PHILL11G2 > SELECT COUNT(*)
FROM ranges
  2    3  WHERE (TO_NUMBER(startr,'XXXXXXXX') < &new_end) AND (TO_NUMBER(endr,'XXXXXXXX') > &new_start);
Enter VALUE FOR new_end: 200
Enter VALUE FOR new_start: 100
old   3: WHERE (TO_NUMBER(startr,'XXXXXXXX') < &new_end) AND (TO_NUMBER(endr,'XXXXXXXX') > &new_start)
NEW   3: WHERE (TO_NUMBER(startr,'XXXXXXXX') < 200) AND (TO_NUMBER(endr,'XXXXXXXX') > 100)

  COUNT(*)
----------
    10

PHIL@PHILL11G2 > EXPLAIN PLAN FOR
  2  SELECT COUNT(*)
  3  FROM ranges
  4  WHERE (TO_NUMBER(startr,'XXXXXXXX') < &new_end) AND (TO_NUMBER(endr,'XXXXXXXX') > &new_start);
Enter VALUE FOR new_end: 200
Enter VALUE FOR new_start: 100
old   4: WHERE (TO_NUMBER(startr,'XXXXXXXX') < &new_end) AND (TO_NUMBER(endr,'XXXXXXXX') > &new_start)
NEW   4: WHERE (TO_NUMBER(startr,'XXXXXXXX') < 200) AND (TO_NUMBER(endr,'XXXXXXXX') > 100)

Explained.

PHIL@PHILL11G2 > SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
PLAN hash VALUE: 790546767

--------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | ROWS  | Bytes | COST (%CPU)| TIME     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE          |         |     1 |    10 |        |      |
|*  2 |   TABLE ACCESS BY INDEX ROWID| RANGES      |    17 |   170 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN      | RANGES_IDXS |    17 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - filter(TO_NUMBER("ENDR",'XXXXXXXX')>100)
   3 - ACCESS(TO_NUMBER("STARTR",'XXXXXXXX')<200)

16 ROWS selected.

PHIL@PHILL11G2 >