| Date datatypes and indexes [message #58595] |
Sat, 20 September 2003 10:24  |
Gaston
Messages: 4
Registered: August 1999
|
Junior Member |
|
|
|
I have a table with a DATE datatype column. If I query the column using the TRUNC function, the index will not be used. Is there a way around this?
|
|
|
|
| Re: Date datatypes and indexes [message #58596 is a reply to message #58595] |
Sat, 20 September 2003 11:45  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Gaston,
you could create a function based index,that would do the trick. See below :
1.Set the following init.ora parameters
query_rewrite_enabled=true
query_rewrite_integrity=enforced
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
2.Grant query_rewrite to the user creating the index.
SQL> select privilege from session_privs where privilege like 'QUERY%';
PRIVILEGE
----------------------------------------
QUERY REWRITE
SQL> create table test as select * from scott.emp;
Table created.
SQL> desc test
Name Null? Type
------------------------------------------------------------------------ -------- ------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
GRADE VARCHAR2(10)
3.Create the function based index
SQL> create index hiredate_idx on test(trunc(hiredate));
Index created.
4. Verify
SQL> set autotrace on
SQL> select empno,ename from test where trunc(hiredate)='03-DEC-81';
EMPNO ENAME
---------- ----------
7900 James
7902 Ford
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
-- Doesnt use the index for lack of updated statistics.Lets update it
SQL> analyze table test estimate statistics;
Table analyzed.
SQL> select empno,ename from test where trunc(hiredate)='03-DEC-81';
EMPNO ENAME
---------- ----------
7900 James
7902 Ford
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=16)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=16)
2 1 INDEX (RANGE SCAN) OF 'HIREDATE_IDX' (NON-UNIQUE) (Cost=
1 Card=1)
You could also reformat the query to avoid the trunc by using >,<,between operators.
hope this helps
Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
