Re: Creating Table as select...

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