Forum Stats

  • 3,853,216 Users
  • 2,264,194 Discussions
  • 7,905,288 Comments

Discussions

SQL Assertions / Declarative multi-row constraints

13468928

Comments

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Hemant K Chitale wrote:
    
    First Oracle has to test to see if the ASSERTION rule has to be applied.  
    

    Check, we have this one covered.

    Then, it has to apply the ASSERTION rule if the test returns TRUE. 
    

    Check, covered too, also it'll do a delta check (i.e. not re-evaluate the whole assertion expression) whenever possible.

    And there's the complexity of concurrent INSERTs / UPDATEs.
    

    Check, also covered. There will be serialization between concurrent transactions. This is inevitable for the implementation to be correct (just like with FK's). The serialization will be minimal though, and only happen when necessary.

    Yes, I know serialisation.

    I say "Doesn't seem easy.".

    Initially it will be like Replication in V7 (was using triggers which were changed to C code in 8i),  CBO in V7  (we trusted the statistics, we really did !)  .....  Analytic SQL in 10g. 

    As pointed out, this had to wait till machines were powerful enough.  Machines are never powerful enough for the volume and complexity of data we handle.

    I wouldn't use ASSERTION in the first release.  That doesn't mean that I haven't upvoted this.

    Hemant K Chitale

  • How about performance impact because of SQL assertion?

  • Toon_Koppelaars-Oracle
    Toon_Koppelaars-Oracle Member Posts: 100 Employee

    How about performance impact because of SQL assertion?

    The goal is that the performance impact will be no worse, than when you would have coded the integrity check yourself inside your application layer(s).

    ErwinSmout
  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    How about the the performance impact on insert/delete/update  for indexes? Or for triggers? Or for check constraints? Or for foreign or primary key constraints?

    Yes, there will be cases where there will be severe performance degradation due to a feature like the one being proposed. And the cause of this will not be the feature, but due to the stupidity and ignorance of the person(s) implementing it.

    A tool cannot be blamed for not working properly, when used incorrectly.

    The tool can be blamed when it leads the user down the garden path.

  • Ruslan D.
    Ruslan D. Member Posts: 25

    How about the the performance impact on insert/delete/update  for indexes? Or for triggers? Or for check constraints? Or for foreign or primary key constraints?

    Yes, there will be cases where there will be severe performance degradation due to a feature like the one being proposed. And the cause of this will not be the feature, but due to the stupidity and ignorance of the person(s) implementing it.

    A tool cannot be blamed for not working properly, when used incorrectly.

    +1 For the idea.

    It would be great to have the same states as for constraints:

    -enabled-disabled;

    -validated-nonvalidate.

    So we could do a big load, and then enable-validate an Assertion. So Oracle could check that in a batch, vs record-by-record.

    Also would be great to have RELY as in constraints. So you could have RELY NOVALIDATE - it certain scenarios CBO may benefit from those assertions, e.g. in simplest case when queries have subqueries matching Assertions.

    Now when I wrote above, I think why create Assetion and not build on top of Constraint objects we already have?

  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    +1 For the idea.

    It would be great to have the same states as for constraints:

    -enabled-disabled;

    -validated-nonvalidate.

    So we could do a big load, and then enable-validate an Assertion. So Oracle could check that in a batch, vs record-by-record.

    Also would be great to have RELY as in constraints. So you could have RELY NOVALIDATE - it certain scenarios CBO may benefit from those assertions, e.g. in simplest case when queries have subqueries matching Assertions.

    Now when I wrote above, I think why create Assetion and not build on top of Constraint objects we already have?

    Why?  Because SQL standard.

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Any rule that describes data is better if its is within the database. Inclusion of assertions will leverage this further. And I am sure CBO will make the best out of it as well

  • EricaHarris
    EricaHarris Member Posts: 9 Blue Ribbon

    +1 Yes please!

  • AmineDz
    AmineDz Member Posts: 7 Blue Ribbon

    Great Idea ! I trust Toon as Tom Kyte trusts him !