Indexes for 'greater than' queries

Question

I have several queries, most of them being:

select * from Blah where col > 0

and

select * from Blah where date > current_date

Since they're both kind of a range, would an unclustered b+ tree index on col and date be a good idea to speed up the queries? Or a hash index? Or would no index be better?

Answer 1

Creating an INDEX on the column used in the filter predicate as a date range condition should be useful as it would do a INDEX RANGE SCAN.

Here is a demonstration about How to create, display and read EXPLAIN PLAN in Oracle.

Let's see the test cases for both scenarios:

Test# 1 : Without index

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp WHERE hiredate > to_date('01/04/1981','mm/dd/yyyy');

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

   1 - filter("HIREDATE">TO_DATE(' 1981-01-04 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

14 rows selected.

SQL>

Test# 1 : With index

SQL> CREATE INDEX emp_idx ON emp(hiredate);

Index created.

SQL>
SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM emp WHERE hiredate > to_date('01/04/1981','mm/dd/yyyy');

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3589413211

-----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |    14 |   518 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |    14 |   518 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_IDX |    14 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("HIREDATE">TO_DATE(' 1981-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

14 rows selected.

SQL>

So, in the second test case, you see an index range scan. I would suggest you to do a similar test on your environment too.