RE: ENABLE NOVALIDATE behaviour bug

From: Looney, Jason <Jason.Looney_at_echostar.com>
Date: Tue, 29 Jun 2004 16:40:58 -0600
Message-ID: <B8C9CF34B3F7164282C8610C93BB94AF01A923B3@riv-exchb1.echostar.com>

Richard,

Is this true for foreign key constraints as well, or does the optimizer use them for plan generation?

Jason.

-----Original Message-----

From: Richard Foote [mailto:richard.foote_at_bigpond.com] Sent: Tuesday, June 29, 2004 6:26 AM
To: oracle-l_at_freelists.org
Subject: Re: ENABLE NOVALIDATE behaviour bug

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

Original Message ----- From: "Mark Richard" <mrichard_at_transurban.com.au> To: <oracle-l_at_freelists.org> Sent: Tuesday, June 29, 2004 4:09 PM Subject: ENABLE NOVALIDATE behaviour bug

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:

alter table blah add (mycol char(1));
update blah set mycol = 'F'; {perhaps include a parallel hint on this statement}
alter table blah modify mycol not null enable novalidate;

I was hoping to use "enable novalidate" to avoid a verification of all records when I know they will be populated.

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


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

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

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

Received on Tue Jun 29 2004 - 17:39:28 CDT