Re: Is there a column-data-type restricted to truncated date format?
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 14 Apr 2010 06:18:17 -0700 (PDT)
Message-ID: <0f17867a-ec5e-4784-b6d5-542bff371429_at_c21g2000yqk.googlegroups.com>
On Apr 12, 5:39 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 12, 1:57 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
>
>
> > On 04/12/2010 04:54 PM, Mark D Powell wrote:
>
> > > On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> > >> Hi,
>
> > >> for creating a unique index with a date-column A_DATE I'm using a
> > >> calculated column X_DATE as (trunc(A_DATE)):
>
> > >> CREATE UNIQUE INDEX UX_A_TABLE
> > >> ON A_TABLE (
> > >> ...,
> > >> X_DATE
> > >> )
> > >> /
>
> > >> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
> > >> times a day.
> > >> Is there a column-data-type restricted to truncated date format?
>
> > > Normally indexes on date columns are non-unique
>
> > Err, doesn't that depend on the index definition? I don't see any
> > general rule that would make indexes specifically on DATE columns non
> > unique. Or did you mean that usually people define non unique indexes
> > on DATE columns?
>
> For one of my systems, and of course I haven't made sure that the
> requirement for date is met:
>
> 1 select uniqueness,count(*) from dba_indexes a,dba_ind_columns b
> 2 where a.index_name=b.index_name
> 3 and a.owner=b.index_owner
> 4 and b.column_name like '%_DATE%'
> 5* group by uniqueness
> SYS_at_TTST> /
>
> UNIQUENES COUNT(*)
> --------- ----------
> NONUNIQUE 34
> UNIQUE 213
>
> I think I agree with where Mark is coming from, though, if he is
> assuming primary key v. other keys. This system is kinda weird that
> way for an ERP/MRP.
>
>
>
> > > but if you want to
> > > restrict a user to only one entry per day then using trunc(sysdate)
> > > would result in a time of midnight. All date columns always have a
> > > time component. However I think you would need composite index on
> > > user and date if you want to perform this test per user.
>
> > What user? Am I missing something from the original question?
>
> The original question was kind of ambiguous, perhaps Norbert could
> tell us what he is trying to do rather than how to do something
> mysterious.
>
> jg
> --
> _at_home.com is bogus.http://www.infoworld.com/print/119525- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 14 Apr 2010 06:18:17 -0700 (PDT)
Message-ID: <0f17867a-ec5e-4784-b6d5-542bff371429_at_c21g2000yqk.googlegroups.com>
On Apr 12, 5:39 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Apr 12, 1:57 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
>
>
>
>
> > On 04/12/2010 04:54 PM, Mark D Powell wrote:
>
> > > On Apr 12, 6:22 am, Norbert Winkler <norbert.winkl..._at_gmx.de> wrote:
> > >> Hi,
>
> > >> for creating a unique index with a date-column A_DATE I'm using a
> > >> calculated column X_DATE as (trunc(A_DATE)):
>
> > >> CREATE UNIQUE INDEX UX_A_TABLE
> > >> ON A_TABLE (
> > >> ...,
> > >> X_DATE
> > >> )
> > >> /
>
> > >> Using A_DATE in UX_A_TABLE would fail if someone would use sysdate two
> > >> times a day.
> > >> Is there a column-data-type restricted to truncated date format?
>
> > > Normally indexes on date columns are non-unique
>
> > Err, doesn't that depend on the index definition? I don't see any
> > general rule that would make indexes specifically on DATE columns non
> > unique. Or did you mean that usually people define non unique indexes
> > on DATE columns?
>
> For one of my systems, and of course I haven't made sure that the
> requirement for date is met:
>
> 1 select uniqueness,count(*) from dba_indexes a,dba_ind_columns b
> 2 where a.index_name=b.index_name
> 3 and a.owner=b.index_owner
> 4 and b.column_name like '%_DATE%'
> 5* group by uniqueness
> SYS_at_TTST> /
>
> UNIQUENES COUNT(*)
> --------- ----------
> NONUNIQUE 34
> UNIQUE 213
>
> I think I agree with where Mark is coming from, though, if he is
> assuming primary key v. other keys. This system is kinda weird that
> way for an ERP/MRP.
>
>
>
> > > but if you want to
> > > restrict a user to only one entry per day then using trunc(sysdate)
> > > would result in a time of midnight. All date columns always have a
> > > time component. However I think you would need composite index on
> > > user and date if you want to perform this test per user.
>
> > What user? Am I missing something from the original question?
>
> The original question was kind of ambiguous, perhaps Norbert could
> tell us what he is trying to do rather than how to do something
> mysterious.
>
> jg
> --
> _at_home.com is bogus.http://www.infoworld.com/print/119525- Hide quoted text -
>
> - Show quoted text -
My comment that most indexes on date columns are non-unique is in reference to single column indexes which is what I believe Norbert was asking about. In my experience it is rarely logical for single column indexes on date data types to be unique even if if is possible.
Looking back at the sample code it appears that the index is probably multi-column and so a unique index on trunc(sysdate) would limit the table to one row per set of remaining key column values per day. Even with update from Norbert I cannot tell if we answered his question fully or not.
Mark D Powell Received on Wed Apr 14 2010 - 08:18:17 CDT