FAQ
Hi all,

I think I have a problem with NULL values on composit indexes:

create table test(id int primary key, pia int, ua int, data date, ver int);
create unique index unik on test(pia int, ua , data , ver )
insert into test values (1,37,76,null,1);
insert into test values (2,37,76,null,1);

ORA-00001: unique constraint (MYDB.SYS_C006557) violated

I hope Oracle knows that no two null values are equal :-)

----------------

this one works, instead:

create table test(id int primary key, pia int, ua int, data date, ver int);
create unique index unik on test(data )
insert into test values (1,37,76,null,1);
insert into test values (2,37,76,null,1);


what's wrong?

j

Search Discussions

  • Mark W. Farnham at Mar 29, 2012 at 3:33 pm
    You can assert NEITHER that two nulls are equal nor unequal. Oracle has that
    correct.

    Are you used to using promoting some database that pretends it CAN assert
    inequality between nulls?

    With this question following hot on the heels of the well understood
    trade-off of Oracle for speed and caching rather than guaranteed ordered
    no-skip use for sequences (for which the old solution of incrementing a
    column in a table is as acid as the database), I'm becoming really curious
    about whether you're legitimately asking question or intentionally create
    confusion.

    Oracle's treatment and explanation of the trade-off for sequences dates to
    at least November 1988.
    Oracle's treatment and explanation of its logic values for nulls and
    uniqueness of indexes is older than that.

    If you're really asking questions and this was just a coincidence, that is
    quite a coincidence indeed.

    If you ARE trying to learn the difference between some other RDBMS system
    and Oracle I suggest you search for a guide with the title something like
    "Oracle for DB2 DBAs" or similar titles, as well as reading the Oracle
    Concepts Guide cover to cover as a start.

    Based on the first two questions I've seen from you, that will save you a
    ton of time if your interest is legitimate.

    Yet please do not feel unwelcome on this list. That is not my intent.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of jose soares
    Sent: Thursday, March 29, 2012 10:40 AM
    To: ORACLE-L
    Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Hi all,

    I think I have a problem with NULL values on composit indexes:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(pia int, ua , data , ver ) insert into test
    values (1,37,76,null,1); insert into test values (2,37,76,null,1);

    ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I hope Oracle knows that no two null values are equal :-)

    ----------------

    this one works, instead:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(data ) insert into test values
    (1,37,76,null,1); insert into test values (2,37,76,null,1);


    what's wrong?

    j







    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jo at Mar 29, 2012 at 5:18 pm
    I think I didn't understand the answer, sorry, my english is not so good.
    My question is:

    There is a difference in the use of NULLs between individual indexes and
    composite indexes?

    j



    Mark W. Farnham wrote:
    You can assert NEITHER that two nulls are equal nor unequal. Oracle has that
    correct.

    Are you used to using promoting some database that pretends it CAN assert
    inequality between nulls?

    With this question following hot on the heels of the well understood
    trade-off of Oracle for speed and caching rather than guaranteed ordered
    no-skip use for sequences (for which the old solution of incrementing a
    column in a table is as acid as the database), I'm becoming really curious
    about whether you're legitimately asking question or intentionally create
    confusion.

    Oracle's treatment and explanation of the trade-off for sequences dates to
    at least November 1988.
    Oracle's treatment and explanation of its logic values for nulls and
    uniqueness of indexes is older than that.

    If you're really asking questions and this was just a coincidence, that is
    quite a coincidence indeed.

    If you ARE trying to learn the difference between some other RDBMS system
    and Oracle I suggest you search for a guide with the title something like
    "Oracle for DB2 DBAs" or similar titles, as well as reading the Oracle
    Concepts Guide cover to cover as a start.

    Based on the first two questions I've seen from you, that will save you a
    ton of time if your interest is legitimate.

    Yet please do not feel unwelcome on this list. That is not my intent.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of jose soares
    Sent: Thursday, March 29, 2012 10:40 AM
    To: ORACLE-L
    Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Hi all,

    I think I have a problem with NULL values on composit indexes:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(pia int, ua , data , ver ) insert into test
    values (1,37,76,null,1); insert into test values (2,37,76,null,1);

    ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I hope Oracle knows that no two null values are equal :-)

    ----------------

    this one works, instead:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(data ) insert into test values
    (1,37,76,null,1); insert into test values (2,37,76,null,1);


    what's wrong?

    j







    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Jose Soares _/_/
    Sferacarta Net
    Via Bazzanese 69 _/_/ _/_/_/
    40033 Casalecchio di Reno _/_/ _/_/ _/_/
    Bologna - Italy _/_/ _/_/ _/_/
    Ph +39051591054 _/_/ _/_/ _/_/ _/_/
    fax +390516131537 _/_/ _/_/ _/_/ _/_/
    web:www.sferacarta.com _/_/_/ _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Mar 29, 2012 at 6:29 pm
    (1,1,1,null,1) might be equal to (1,1,1,null,1), so the composite value
    cannot be asserted to be unique.

    (null) versus (null) in a single column index is I take it considered to be
    a missing value for convenience.

    Generalizing, (null,null,null,null,null) for a 5 column composite as in your
    example can also be duplicated.

    So the consistency is that if the index key's complete value is null, they
    don't guard against those as duplicate values.

    If at least one column value in an index is not null, then a duplicate will
    not be allowed.

    If a unique index/constraint is completely null, Oracle won't declare it to
    be a uniqueness violation if you toss in another completely null tuple.

    If a unique index/constraint has at least something filled in then Oracle
    won't let a possible duplicate into the table.

    Since a single column index has just the one column, you could take that to
    mean Oracle's treatment is different for that case, but you'd be wrong.

    It just happens that the whole entry is null by virtue(?) of that one column
    being null.

    Those were the null value/logic choices made a long time ago for Oracle. I'm
    not sure the folks who discussed the reasons underlying this choice are even
    still alive, so take my presumption that it was because it seemed logical to
    presume completely null was a missing value. It may just have been easier to
    implement that way. From a use perspective it is convenient in some cases,
    although I personally prefer avoiding null values being inserted into my
    databases and I look askance at data models that require what should be a
    unique composite from allowing nulls in the first place.

    So no, there is not a difference in the use of NULLs between individual
    indexes and composite indexes.

    regards,

    mwf
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of jo
    Sent: Thursday, March 29, 2012 1:11 PM
    Cc: 'ORACLE-L'
    Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I think I didn't understand the answer, sorry, my english is not so good.
    My question is:

    There is a difference in the use of NULLs between individual indexes and
    composite indexes?

    j



    Mark W. Farnham wrote:
    You can assert NEITHER that two nulls are equal nor unequal. Oracle
    has that correct.

    Are you used to using promoting some database that pretends it CAN
    assert inequality between nulls?

    With this question following hot on the heels of the well understood
    trade-off of Oracle for speed and caching rather than guaranteed
    ordered no-skip use for sequences (for which the old solution of
    incrementing a column in a table is as acid as the database), I'm
    becoming really curious about whether you're legitimately asking
    question or intentionally create confusion.

    Oracle's treatment and explanation of the trade-off for sequences
    dates to at least November 1988.
    Oracle's treatment and explanation of its logic values for nulls and
    uniqueness of indexes is older than that.

    If you're really asking questions and this was just a coincidence,
    that is quite a coincidence indeed.

    If you ARE trying to learn the difference between some other RDBMS
    system and Oracle I suggest you search for a guide with the title
    something like "Oracle for DB2 DBAs" or similar titles, as well as
    reading the Oracle Concepts Guide cover to cover as a start.

    Based on the first two questions I've seen from you, that will save
    you a ton of time if your interest is legitimate.

    Yet please do not feel unwelcome on this list. That is not my intent.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org

    On Behalf Of jose soares
    Sent: Thursday, March 29, 2012 10:40 AM
    To: ORACLE-L
    Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Hi all,

    I think I have a problem with NULL values on composit indexes:

    create table test(id int primary key, pia int, ua int, data date, ver
    int); create unique index unik on test(pia int, ua , data , ver )
    insert into test values (1,37,76,null,1); insert into test values
    (2,37,76,null,1);

    ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I hope Oracle knows that no two null values are equal :-)

    ----------------

    this one works, instead:

    create table test(id int primary key, pia int, ua int, data date, ver
    int); create unique index unik on test(data ) insert into test values
    (1,37,76,null,1); insert into test values (2,37,76,null,1);


    what's wrong?

    j







    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    Jose Soares _/_/
    Sferacarta Net
    Via Bazzanese 69 _/_/ _/_/_/
    40033 Casalecchio di Reno _/_/ _/_/ _/_/
    Bologna - Italy _/_/ _/_/ _/_/
    Ph +39051591054 _/_/ _/_/ _/_/ _/_/
    fax +390516131537 _/_/ _/_/ _/_/ _/_/
    web:www.sferacarta.com _/_/_/ _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file
    allegato sono riservate e, comunque, destinate esclusivamente alla persona o
    ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196.
    La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
    qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
    l’integrità e la sicurezza della presente mail non possono essere garantite.
    Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci
    immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to
    legislative decree 30 June 2003, n. 196. It may contain confidential or
    privileged information. You should not copy or use it to disclose its
    contents to any other person. Transmission cannot be guaranteed to be
    error-free, complete and secure. If you are not the intended recipient and
    receive this communication unintentionally, please inform us immediately and
    then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Mar 30, 2012 at 11:35 am
    You're welcome.

    Often, midnight or one second past midnight on Jan. 1, 1970 can be well
    known as the value for "this is not a real date" in honor of the epoch time
    of UNIX. There are other reasonable choices as well, particularly if you are
    doing history or something like that. In some cases having a special value
    is not useful, but it does prevent row length change when it is updated to
    the "real date" that may later be appearing. And you can search for it with
    an equals predicate on an index if you're looking for dates that haven't
    been processed yet.

    If you're not worried about row length change (or if there is a positive
    trade-off versus having a lower pctfree) you can use defaults values for
    rows inserted without values for particular columns. With a default chosen
    that is unlikely to by a real value, you can check the data dictionary for
    this value to firewall your application software instead of relying on a
    presumed constant (like 1/1/1970.)

    Often a little bit of planning of this nature in the data model design
    considered against the planned data flow through an application can make
    implementing the system easier, and you may indeed end up with a system
    containing few nulls. One notable exception is having a final status value
    of null to mean "DONE" on an indexed transaction control column, which takes
    advantage of the fact that all null index entries do not appear in the index
    for routine Oracle indexes (circling back to the original topic).

    mwf

    -----Original Message-----
    From: jo
    Sent: Friday, March 30, 2012 2:21 AM
    To: mwf@rsiz.com
    Cc: 'ORACLE-L'
    Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Ok, now I understand.
    Thanks for your explanation, Mark,
    and thanks to everyone to answer my question.
    j

    ps:

    You said you prefer to avoid null values to be inserted into your db.
    While it is easy to apply to numeric or text columns, I wonder what do you
    insert into a date column instead of a null.


    Mark W. Farnham wrote:
    (1,1,1,null,1) might be equal to (1,1,1,null,1), so the composite value
    cannot be asserted to be unique.

    (null) versus (null) in a single column index is I take it considered to be
    a missing value for convenience.

    Generalizing, (null,null,null,null,null) for a 5 column composite as in your
    example can also be duplicated.

    So the consistency is that if the index key's complete value is null, they
    don't guard against those as duplicate values.

    If at least one column value in an index is not null, then a duplicate will
    not be allowed.

    If a unique index/constraint is completely null, Oracle won't declare it to
    be a uniqueness violation if you toss in another completely null tuple.

    If a unique index/constraint has at least something filled in then Oracle
    won't let a possible duplicate into the table.

    Since a single column index has just the one column, you could take that to
    mean Oracle's treatment is different for that case, but you'd be wrong.

    It just happens that the whole entry is null by virtue(?) of that one column
    being null.

    Those were the null value/logic choices made a long time ago for Oracle. I'm
    not sure the folks who discussed the reasons underlying this choice are even
    still alive, so take my presumption that it was because it seemed logical to
    presume completely null was a missing value. It may just have been easier to
    implement that way. From a use perspective it is convenient in some cases,
    although I personally prefer avoiding null values being inserted into my
    databases and I look askance at data models that require what should be a
    unique composite from allowing nulls in the first place.

    So no, there is not a difference in the use of NULLs between individual
    indexes and composite indexes.

    regards,

    mwf
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of jo
    Sent: Thursday, March 29, 2012 1:11 PM
    Cc: 'ORACLE-L'
    Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I think I didn't understand the answer, sorry, my english is not so good.
    My question is:

    There is a difference in the use of NULLs between individual indexes and
    composite indexes?

    j



    Mark W. Farnham wrote:
    You can assert NEITHER that two nulls are equal nor unequal. Oracle
    has that correct.

    Are you used to using promoting some database that pretends it CAN
    assert inequality between nulls?

    With this question following hot on the heels of the well understood
    trade-off of Oracle for speed and caching rather than guaranteed
    ordered no-skip use for sequences (for which the old solution of
    incrementing a column in a table is as acid as the database), I'm
    becoming really curious about whether you're legitimately asking
    question or intentionally create confusion.

    Oracle's treatment and explanation of the trade-off for sequences
    dates to at least November 1988.
    Oracle's treatment and explanation of its logic values for nulls and
    uniqueness of indexes is older than that.

    If you're really asking questions and this was just a coincidence,
    that is quite a coincidence indeed.

    If you ARE trying to learn the difference between some other RDBMS
    system and Oracle I suggest you search for a guide with the title
    something like "Oracle for DB2 DBAs" or similar titles, as well as
    reading the Oracle Concepts Guide cover to cover as a start.

    Based on the first two questions I've seen from you, that will save
    you a ton of time if your interest is legitimate.

    Yet please do not feel unwelcome on this list. That is not my intent.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org

    On Behalf Of jose soares
    Sent: Thursday, March 29, 2012 10:40 AM
    To: ORACLE-L
    Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Hi all,

    I think I have a problem with NULL values on composit indexes:

    create table test(id int primary key, pia int, ua int, data date, ver
    int); create unique index unik on test(pia int, ua , data , ver )
    insert into test values (1,37,76,null,1); insert into test values
    (2,37,76,null,1);

    ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I hope Oracle knows that no two null values are equal :-)

    ----------------

    this one works, instead:

    create table test(id int primary key, pia int, ua int, data date, ver
    int); create unique index unik on test(data ) insert into test values
    (1,37,76,null,1); insert into test values (2,37,76,null,1);


    what's wrong?

    j







    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l



    --
    Jose Soares _/_/
    Sferacarta Net
    Via Bazzanese 69 _/_/ _/_/_/
    40033 Casalecchio di Reno _/_/ _/_/ _/_/
    Bologna - Italy _/_/ _/_/ _/_/
    Ph +39051591054 _/_/ _/_/ _/_/ _/_/
    fax +390516131537 _/_/ _/_/ _/_/ _/_/
    web:www.sferacarta.com _/_/_/ _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file
    allegato sono riservate e, comunque, destinate esclusivamente alla persona o
    ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196.
    La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di
    qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza,
    l’integrità e la sicurezza della presente mail non possono essere garantite.
    Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci
    immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to
    legislative decree 30 June 2003, n. 196. It may contain confidential or
    privileged information. You should not copy or use it to disclose its
    contents to any other person. Transmission cannot be guaranteed to be
    error-free, complete and secure. If you are not the intended recipient and
    receive this communication unintentionally, please inform us immediately and
    then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • D'Hooge Freek at Mar 30, 2012 at 1:04 pm
    Be carefull with these kind of bogus values as they can mislead the the cbo.

    Recently I had one case where the cbo was taking a wrong path, because it thought that a certain step would return very few rows. Reason for that was the application was using a date that was far (far far far) in the future to indicate an unknown date and with range predicates the max and min values are used as part of a formula to calculate the selectivity of the predicate.

    Maybe you can add an explicit flag to indicate that the date is unknown and add this flag column to your unique constraint as well?


    Kind regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge@uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer

    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Mark W. Farnham
    Sent: vrijdag 30 maart 2012 13:27
    To: 'ORACLE-L'
    Subject: RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    You're welcome.

    Often, midnight or one second past midnight on Jan. 1, 1970 can be well
    known as the value for "this is not a real date" in honor of the epoch time
    of UNIX. There are other reasonable choices as well, particularly if you are
    doing history or something like that. In some cases having a special value
    is not useful, but it does prevent row length change when it is updated to
    the "real date" that may later be appearing. And you can search for it with
    an equals predicate on an index if you're looking for dates that haven't
    been processed yet.

    If you're not worried about row length change (or if there is a positive
    trade-off versus having a lower pctfree) you can use defaults values for
    rows inserted without values for particular columns. With a default chosen
    that is unlikely to by a real value, you can check the data dictionary for
    this value to firewall your application software instead of relying on a
    presumed constant (like 1/1/1970.)

    Often a little bit of planning of this nature in the data model design
    considered against the planned data flow through an application can make
    implementing the system easier, and you may indeed end up with a system
    containing few nulls. One notable exception is having a final status value
    of null to mean "DONE" on an indexed transaction control column, which takes
    advantage of the fact that all null index entries do not appear in the index
    for routine Oracle indexes (circling back to the original topic).

    mwf

    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan Lewis at Mar 30, 2012 at 1:20 pm
    A possible workaround to the "silly value for null" design error is to
    create a histogram on the column - this allows the optimizer to see that
    there is a big gap in the data range and compensate accordingly. It isn't
    effective in all cases, of course, but may deal with a sufficiently large
    percentage to make it worth doing. The downside includes the impact is has
    on "cursor_sharing = similar" and adaptive cursor sharing - where the
    presence of histograms on columns that appear with bind variables in the
    where clause is sufficient to identify the SQL as "bind sensitive".

    Regards

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    Oracle Core (Apress 2011)
    http://www.apress.com/9781430239543


    ----- Original Message -----
    From: "D'Hooge Freek" <Freek.DHooge@uptime.be>
    To: <mwf@rsiz.com>; "'ORACLE-L'" <oracle-l@freelists.org>
    Sent: Friday, March 30, 2012 2:03 PM
    Subject: RE: ORA-00001: unique constraint (MYDB.SYS_C006557) violated


    Be carefull with these kind of bogus values as they can mislead the the
    cbo.

    Recently I had one case where the cbo was taking a wrong path, because it
    thought that a certain step would return very few rows. Reason for that was
    the application was using a date that was far (far far far) in the future
    to indicate an unknown date and with range predicates the max and min
    values are used as part of a formula to calculate the selectivity of the
    predicate.


    --
    http://www.freelists.org/webpage/oracle-l
  • Martin Berger at Mar 30, 2012 at 2:47 pm
    Mark,

    I have to contradict here. Any 'default' value just to tell 'it is
    bogus' is far from the spirit of relational structures.
    Even the usage of NULL at all can be avoided. The big problem here is
    (as Lex de Haan described) is you can not distinct, if NULL means
    'Inapplicable', 'Not Yet Applicable', or 'Nice to know' (aka
    'unimportant').
    I try to show you some escape here.
    Let's try the original table & inserts:
    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(pia int, ua , data , ver )
    insert into test values (1,37,76,null,1);
    insert into test values (2,37,76,null,1);
    I would like to translate it into:

    create table test(id int primary key, pia int, ua int, ver int);
    create unique index unik on test(pia int, ua , ver );

    create table test_data(id int primary key, test_id int REFERENCES
    test(id), data date );
    create unique index td_uink on test_data(test_id);

    insert into test values (1,37,76,1);
    insert into test values (2,37,76,1);

    so no NULL is needed at all - in best/worst case it's VISIBLE at the
    outer join of test and test_data.

    I'm quite sure this is a better solution than pollute the system first
    with any bogus information, just to try to help out with histograms
    afterwards?

    If someone complains about more IOs because of the 2 tables, I just
    can recommend to check the logic behind CLUSTERS.


    hth
    Martin


    On Fri, Mar 30, 2012 at 13:27, Mark W. Farnham wrote:
    You're welcome.

    Often, midnight or one second past midnight on Jan. 1, 1970 can be well
    known as the value for "this is not a real date" in honor of the epoch time
    of UNIX. There are other reasonable choices as well, particularly if you are
    doing history or something like that.  In some cases having a special value
    is not useful, but it does prevent row length change when it is updated to
    the "real date" that may later be appearing. And you can search for it with
    an equals predicate on an index if you're looking for dates that haven't
    been processed yet.

    If you're not worried about row length change (or if there is a positive
    trade-off versus having a lower pctfree) you can use defaults values for
    rows inserted without values for particular columns. With a default chosen
    that is unlikely to by a real value, you can check the data dictionary for
    this value to firewall your application software instead of relying on a
    presumed constant (like 1/1/1970.)

    Often a little bit of planning of this nature in the data model design
    considered against the planned data flow through an application can make
    implementing the system easier, and you may indeed end up with a system
    containing few nulls. One notable exception is having a final status value
    of null to mean "DONE" on an indexed transaction control column, which takes
    advantage of the fact that all null index entries do not appear in the index
    for routine Oracle indexes (circling back to the original topic).
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Apr 2, 2012 at 8:50 pm
    I took the question to be "if I've designed myself into a hole and I need to
    insert a null value for a date, what would you do instead."

    Designing so that you do not need a null value in the first place is indeed
    a better thing to do. The general missing values topic is way too broad for
    a list and probably begins with the Codd versus Date debates and includes
    the fine insights of Lex and Toon.

    In the context of that broad debate your point is certainly well taken
    Martin.

    I do take issue with the notion that you cannot ascribe a value to NULL for
    a particular column of a particular table. A key case in point is using NULL
    as the final status so that it drops out of a work control index. A view can
    be defined to produce a symbolic value, as needed. The value of this
    technique in exploiting Oracle's physical implementation to keep the search
    set small for tuples you are interested in is too big to ignore.

    Dealing with side effects of the implementation of the CBO is yet another
    issue best dealt with on a case by case basis and subordinate to good
    design. The CBO will change over time. The tricks and techniques we use
    today may not be the tricks and techniques we use tomorrow. This is not to
    say they are unimportant.

    Regards,

    mwf
    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of Martin Berger
    Sent: Friday, March 30, 2012 10:46 AM
    To: mwf@rsiz.com
    Cc: ORACLE-L
    Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Mark,

    I have to contradict here. Any 'default' value just to tell 'it is bogus' is
    far from the spirit of relational structures.
    Even the usage of NULL at all can be avoided. The big problem here is (as
    Lex de Haan described) is you can not distinct, if NULL means
    'Inapplicable', 'Not Yet Applicable', or 'Nice to know' (aka 'unimportant').
    I try to show you some escape here.
    Let's try the original table & inserts:
    create table test(id int primary key, pia int, ua int, data date, ver
    int); create unique index unik on test(pia int, ua , data , ver )
    insert into test values (1,37,76,null,1); insert into test values
    (2,37,76,null,1);
    I would like to translate it into:

    create table test(id int primary key, pia int, ua int, ver int); create
    unique index unik on test(pia int, ua , ver );

    create table test_data(id int primary key, test_id int REFERENCES test(id),
    data date ); create unique index td_uink on test_data(test_id);

    insert into test values (1,37,76,1);
    insert into test values (2,37,76,1);

    so no NULL is needed at all - in best/worst case it's VISIBLE at the outer
    join of test and test_data.

    I'm quite sure this is a better solution than pollute the system first with
    any bogus information, just to try to help out with histograms afterwards?

    If someone complains about more IOs because of the 2 tables, I just can
    recommend to check the logic behind CLUSTERS.


    hth
    Martin


    On Fri, Mar 30, 2012 at 13:27, Mark W. Farnham wrote:
    You're welcome.

    Often, midnight or one second past midnight on Jan. 1, 1970 can be
    well known as the value for "this is not a real date" in honor of the
    epoch time of UNIX. There are other reasonable choices as well,
    particularly if you are doing history or something like that.  In some
    cases having a special value is not useful, but it does prevent row
    length change when it is updated to the "real date" that may later be
    appearing. And you can search for it with an equals predicate on an
    index if you're looking for dates that haven't been processed yet.

    If you're not worried about row length change (or if there is a
    positive trade-off versus having a lower pctfree) you can use defaults
    values for rows inserted without values for particular columns. With a
    default chosen that is unlikely to by a real value, you can check the
    data dictionary for this value to firewall your application software
    instead of relying on a presumed constant (like 1/1/1970.)

    Often a little bit of planning of this nature in the data model design
    considered against the planned data flow through an application can
    make implementing the system easier, and you may indeed end up with a
    system containing few nulls. One notable exception is having a final
    status value of null to mean "DONE" on an indexed transaction control
    column, which takes advantage of the fact that all null index entries
    do not appear in the index for routine Oracle indexes (circling back to
    the original topic).
  • Sid at Mar 29, 2012 at 7:18 pm
    I believe you are comparing composite UNIQUE index with single column index. Please note that Oracle doesn't store the row if all column values are NULL.

    --------------------------------------------------------------------------------------------------------
    SQL> create table test(id int , pia int, ua int, data date, ver int);
    Table created.
    SQL> create unique index unik on test(data);
    Index created.
    SQL> insert into test values (null, null, null, null, null);
    1 row created.
    SQL> insert into test values (null, null, null, null, null);
    1 row created.
    SQL> insert into test values (null, null, null, null, null);
    1 row created.
    --------------------------------------------------------------------------------------------------------


    ________________________________
    From: jo <jose.soares@sferacarta.com>
    To:
    Cc: 'ORACLE-L' <oracle-l@freelists.org>
    Sent: Thursday, 29 March 2012 1:11 PM
    Subject: Re: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I think I didn't understand the answer, sorry, my english is not so good.
    My question is:

    There is a difference in the use of NULLs between individual indexes and
    composite indexes?

    j



    Mark W. Farnham wrote:
    You can assert NEITHER that two nulls are equal nor unequal. Oracle has that
    correct.

    Are you used to using promoting some database that pretends it CAN assert
    inequality between nulls?

    With this question following hot on the heels of the well understood
    trade-off of Oracle for speed and caching rather than guaranteed ordered
    no-skip use for sequences (for which the old solution of incrementing a
    column in a table is as acid as the database), I'm becoming really curious
    about whether you're legitimately asking question or intentionally create
    confusion.

    Oracle's treatment and explanation of the trade-off for sequences dates to
    at least November 1988.
    Oracle's treatment and explanation of its logic values for nulls and
    uniqueness of indexes is older than that.

    If you're really asking questions and this was just a coincidence, that is
    quite a coincidence indeed.

    If you ARE trying to learn the difference between some other RDBMS system
    and Oracle I suggest you search for a guide with the title something like
    "Oracle for DB2 DBAs" or similar titles, as well as reading the Oracle
    Concepts Guide cover to cover as a start.

    Based on the first two questions I've seen from you, that will save you a
    ton of time if your interest is legitimate.

    Yet please do not feel unwelcome on this list. That is not my intent.

    Regards,

    mwf

    -----Original Message-----
    From: oracle-l-bounce@freelists.org
    On Behalf Of jose soares
    Sent: Thursday, March 29, 2012 10:40 AM
    To: ORACLE-L
    Subject: ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    Hi all,

    I think I have a problem with NULL values on composit indexes:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(pia int, ua , data , ver ) insert into test
    values (1,37,76,null,1); insert into test values (2,37,76,null,1);

    ORA-00001: unique constraint (MYDB.SYS_C006557) violated

    I hope Oracle knows that no two null values are equal :-)

    ----------------

    this one works, instead:

    create table test(id int primary key, pia int, ua int, data date, ver int);
    create unique index unik on test(data ) insert into test values
    (1,37,76,null,1); insert into test values (2,37,76,null,1);


    what's wrong?

    j







    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    Â

    --
    Jose Soares                              _/_/Â
    Sferacarta Net                                Â
    Via Bazzanese 69                      _/_/    _/_/_/
    40033 Casalecchio di Reno            _/_/  _/_/  _/_/
    Bologna - Italy                      _/_/  _/_/  _/_/
    Ph  +39051591054              _/_/  _/_/  _/_/  _/_/
    fax +390516131537            _/_/  _/_/  _/_/  _/_/
    web:www.sferacarta.com        _/_/_/      _/_/_/

    Le informazioni contenute nella presente mail ed in ogni eventuale file allegato sono riservate e, comunque, destinate esclusivamente alla persona o ente sopraindicati, ai sensi del decreto legislativo 30 giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della mail trasmessa, da parte di qualsiasi soggetto diverso dal destinatario, sono vietate. La correttezza, l’integrità e la sicurezza della presente mail non possono essere garantite. Se avete ricevuto questa mail per errore, Vi preghiamo di contattarci immediatamente e di eliminarla. Grazie.

    This communication is intended only for use by the addressee, pursuant to legislative decree 30 June 2003, n. 196. It may contain confidential or privileged information. You should not copy or use it to disclose its contents to any other person. Transmission cannot be guaranteed to be error-free, complete and secure. If you are not the intended recipient and receive this communication unintentionally, please inform us immediately and then delete this message from your system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 29, '12 at 2:37p
activeApr 2, '12 at 8:50p
posts10
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase