Date datatype in index - performance

Hello,

We have tables that have 50-80 million rows in them that have a seven-column concatenated primary key. All columns are NUMBER.

One of the columns is a six-digit date (YYMMDD). I'm about to change that column to a
date datatype *OR* at NUMBER (8) (YYYYMMDD).

I thought I heard in the past that it's a bad idea to have a date datatype in an index as it affects performance.

Can anyone explain?

At first I thought that Oracle would have to convert the date in the select statement for
comparison on every row, but then thought it would only have to do the conversion once.

Example:

select * from my_table
where table_date = to_date('19980709','YYYYMMDD');

Wouldn't Oracle simply convert the to_date piece once?

Thank you!

-Bruce Bristol

bbristol_at_oanservices.com

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 09 1998 - 11:59:37 CDT