Question

The EMPLOYEES table from Use The Index, Luke has the DATE_OF_BIRTH field (Type: date).

How can one query for all employees that are, let's say, 42 years old - USING AN INDEX?

Answer 1

I'm probably falling into the trap here, but I couldn't help trying it out.

CREATE INDEX emp_date_of_birth ON employees (date_of_birth);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

SELECT employee_id 
  FROM employees 
 WHERE date_of_birth between add_months(trunc(SYSDATE), -12*42) 
                         and add_months(trunc(SYSDATE), -12*41)-1;

answered Sep 15 '10 at 13:33

machee
16●1

edited Sep 15 '10 at 14:16

Markus Winand ♦♦
936●5●11●20

That's the correct approach, but I think there is still a minor glitch in the details.

(Sep 16 '10 at 01:16) Markus Winand ♦♦

Answer 2

I like the solution above but I would change the query to not use a function. The application code would pass along 2 dates which would represent the "between" dates so that the following index can be used:

CREATE INDEX emp_date_of_birth ON employees (date_of_birth);

thoughts?

This answer is marked "community wiki".

answered Mar 02 '11 at 23:41

levg62
1●1

edited Mar 02 '11 at 23:41

There is nothing against that. But you need to take care of the details, especially when using BETWEEN, which is always inclusive. That's why you sometimes need ugly code like shown in Obfuscating Dates. Code like that, which must know the smallest representable interval for the particular database/type, should probably not go to the application. I'd use explicit >= and < in that case.

(Mar 03 '11 at 01:11) Markus Winand ♦♦