Re: Creating Table as select...

I haven't tested it, but does setting the 'rely' flag make any difference to the execution plans for a deferrable actually deferred constraint? (I'd check but I'm up to my neck in it at the moment)

I'd say myself, just to be devil's advocate, that the ORA-08102 error still means the constraint is being enforced, and is thus working, so that wouldn't of itself be sufficient to persuade me that non-unique indexes enforcing used on unique constraints are a 'bad thing'

Regards
HJR

-- 
--------------------------------------------
Oracle Insights: www.dizwell.com
--------------------------------------------

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:c0bhlv$ja9$1_at_hercules.btinternet.com...


>

> Just as a side-note, you can't always do things

> the way you would like to because of buggy

> side-effects.

>

> For example - if you create a constraint, and

> let it create the index as a side-effect, then you

> can't (as at about 9.2.0.1 but I ought to test

> 9.2.0.4) perform a treedump without crashing

> with an 'invalid number'.  This happens to be

> my favourite reason for creating indexes independently

> of constraints AT PRESENT.

>

> As another example of strategy and necessity colliding,

> if you create an non-unique index to support a unique

> constraint, and want a compressed index for efficiency

> reasons, then you don't get 'duplicate key in index' errors

> when breaking the constraint, you crash out with error:

>     "ORA-08102: index key not found"

> Still broken in 9.2.0.3 - haven't checked 9.2.0.4 yet,

> but it's fixed in 10.1  This happens to be my favourite

> reason for not creating non-unique indexes to support

> unique constraints.

>

> In passing - if you make a unique constraint deferrable,

> then while it is in a deferred state, the CBO changes the

> cost of using it - so you could rung a query, defer the

> index, run effectively the same query and get a different

> access path.  (It's possible, but probably not going to

> happen often).

>

> -- 

> Regards

>

> Jonathan Lewis

> http://www.jlcomp.demon.co.uk

>

>   The educated person is not the person

>   who can answer the questions, but the

>   person who can question the answers -- T. Schick Jr

>

>

> Next public appearances:

>  March 2004 Hotsos Symposium - The Burden of Proof

>  March 2004 Charlotte NC OUG - CBO Tutorial

>  April 2004 Iceland

>

>

> One-day tutorials:

> http://www.jlcomp.demon.co.uk/tutorial.html

>

>

> Three-day seminar:

> see http://www.jlcomp.demon.co.uk/seminar.html

> ____UK___February

> ____UK___June

>

>

> The Co-operative Oracle Users' FAQ

> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>

>