A Fix for Check Constraints That Harm Cardinalities

I wrote a while ago about the harmful effect of check constraints on query optimization. I’ll pause while you catch up on that …

Anyway, this appears to have been addressed in 10.2.0.4, and it’s now safe to go back into the water on applying check constraints. Bug 5891471.

The bug description also notes that you can set event 10195 to disable transitive predicate generation using check constraints.

I haven’t tested any of this — I will if I get time. Unless someoneelse wants to …

 

About these ads

4 thoughts on “A Fix for Check Constraints That Harm Cardinalities

  1. After an upgrade from 8.1.7 to 10.2.0.1 we saw the following query parsing 50 times a second:

    select condition from cdef$ where rowid=:1

    This was causing a significant CPU drain (Bug 4900129) and event 10195 helped greatly (though we didn’t see the benefit until we got rid of the many histograms that were created automatically when we weren’t looking :))

  2. 8.1.7 to 10.2.0.1 — a bold jump, and I don’t envy you the amount of changes in the optimzer that you had to cope with.

    One of the major gotchas on 10g seems to be the automatic statistics gathering job — is that what was generating those histograms?

    • I think the defaults for the auto stats gather is a histogram on EVERY column. n’est-ce pas?

  3. It was the automatic statistics gathering job that created the histograms. How they were missed in testing I don’t know – shame on us! Anyway METHOD_OPT = 1 fixed almost all our issues.

    (apologies for late reply by the way – forgot I’d posted)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s