Home » RDBMS Server » Performance Tuning » create index on date fields
create index on date fields [message #64916] Tue, 02 March 2004 01:52 Go to next message
santosh
Messages: 85
Registered: October 2000
Member
I have a varchar column and have date values can i index them.
Re: create index on date fields [message #64918 is a reply to message #64916] Tue, 02 March 2004 03:15 Go to previous messageGo to next message
Frank Naude
Messages: 4565
Registered: April 1998
Senior Member
Yes, try it and let us know if you run into problems.

SQL> create table x (col1 date, col2 varchar2(30));

Table created.

SQL> create index i1 on x (col1);

Index created.

SQL>  create index i2  on x (col2);

Index created.


Best regards.

Frank
Re: create index on date fields [message #64921 is a reply to message #64916] Tue, 02 March 2004 08:10 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Santosh, if you mean you have dates stored in varchar2 columns (which you should never have) then yes, you can index them, but when you query the table, you must remeber to convert dates to varchar first (and not leave it to chance).

create table t (date_str varchar2(12));
insert into t values ('12-Mar-2003');

select * from t where date_str = to_char({date value});

You can also create a function based index so that you effectively have a real date value in the index. If you do that, the function based index will only get used if you use the exact same format in the where clause as was used to create the index. See the documentation on that at tahiti.oracle.com
Re: create index on date fields [message #64924 is a reply to message #64921] Thu, 04 March 2004 02:02 Go to previous message
santosh
Messages: 85
Registered: October 2000
Member
Thanks a lot will try it out.
Previous Topic: SYSTEM STATISTICS in Distributed Enviornment
Next Topic: 9i Performance on NT4
Goto Forum:
  


Current Time: Sun Sep 20 23:03:28 CDT 2020