| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: ENABLE NOVALIDATE behaviour bug
Thanks to Tanel and Richard for explaining this. I know realise that I misunderstood the meaning of "novalidate" - I was hoping it might mean "trust me Oracle, I know it to be true" but it can only mean "from today on, enforce this - who knows about the past". I should have realised that.
I'll take Tanel's advice of creating the column with a default. This is yet another area I wasn't sure about... I wasn't positive that a default value would be applied to existing rows since adding a default clause to a pre-existing column doesn't do this. So I've asked one question and received two answers - Well done and thankyou.
Regards,
Mark.
"Richard Foote"
<richard.foote_at_big To: <oracle-l_at_freelists.org>
pond.com> cc:
Sent by: Subject: Re: ENABLE NOVALIDATE behaviour bug
oracle-l-bounce_at_fr
eelists.org
29/06/2004 22:26
Please respond to
oracle-l
Hi Mark,
Just to expand a little on a point made by Tanel. It can be a little "dangerous" to enable a constraint with novalidate. By doing so, you're effectively telling Oracle the data is valid, honest, whist the optimizer takes the attitude, "actually, I really don't believe you". This means that possibly useful constraint data can't be used by the optimizer when determining the best plan.
A very simple example. We had a statement that required an "empty" set to
be
returned and used a query to list all null values for a not null column.
Problem being the constraint was inadvertently enabled with novalidate
after
the table was rebuilt meaning that a previous "efficient" plan was replaced
by a horrendously expensive and unnecessary FTS. Although it might sound
like an odd thing to do (and in this specific example, it was a rather odd
thing to do), it's not actually uncommon for queries to sometimes request
data that can't possibly exist due to a constraint. Only by having a
validated constraint can the optimizer "know" that such a query will return
no rows and act accordingly (or "know" that there are no nulls and use an
index etc ...).
Sometimes performing one scan to validate a constraint can save many subsequent unnecessary scans !!
Cheers
Richard
Hi List,
I regularly have to change the structure of some fairly large tables (~200m rows). Often we use the opportunity to do a full table rebuild if we want to change other settings (such as index locations) but other times we would prefer to modify the existing table.
I currently have the scenario where I need to add a single CHAR(1) column to a 250m row table and populate it with a constant value (new records may have a different value). The approach I am considering is:
I was hoping to use "enable novalidate" to avoid a verification of all records when I know they will be populated.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 29 2004 - 17:12:24 CDT
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
 |
 |