OraFAQ Forum: Performance Tuning » create index on date fields

Home » RDBMS Server » Performance Tuning » create index on date fields

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

create index on date fields [message #64916]

Tue, 02 March 2004 01:52

santosh
Messages: 85
Registered: October 2000

Member

I have a varchar column and have date values can i index them.

Report message to a moderator

Re: create index on date fields [message #64918 is a reply to message #64916]

Tue, 02 March 2004 03:15

Frank Naude
Messages: 4596
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

Report message to a moderator

Re: create index on date fields [message #64921 is a reply to message #64916]

Tue, 02 March 2004 08:10

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

Report message to a moderator

Re: create index on date fields [message #64924 is a reply to message #64921]

Thu, 04 March 2004 02:02

santosh
Messages: 85
Registered: October 2000

Member

Thanks a lot will try it out.

Report message to a moderator

Previous Topic:	SYSTEM STATISTICS in Distributed Enviornment
Next Topic:	9i Performance on NT4

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 19 12:42:46 CST 2026