Re: Question about Append hint in Insert

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Jun 2004 10:58:48 +0100
Message-ID: <01bf01c45908$b196ff90$7102a8c0@Primary>

Note in-line

Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st

Original Message ----- From: "Daniel Fink" <Daniel.Fink_at_Sun.COM> To: <oracle-l_at_freelists.org> Sent: Monday, June 21, 2004 7:55 PM Subject: Re: Question about Append hint in Insert

One other 'subtle' difference in this scenario is that you cannot perform an INDEX UNIQUE SCAN on the index when you are using an equality predicate. IIRC, the CBO will cost the allowable RANGE SCAN the same as the UNIQUE SCAN so there should not be differences in query plan selection nor execution time.

>> The CBO is aware of the unique constraint, despite
>> the non-unique index - so you even get the 'single-row
>> table' treatment.

                                            If you are using explain

plan/tkprof, etc.
you will see a drastic difference in the card(inality) estimate,

>> You shouldn't if your query is targetting the full set
>> of columns, because the existence of the constraint
>> will ensure that your index really is unique (unless the
>> constraint is deferred or disabled) so the statistics
>> should be indicating the truth as a consequence of
>> the real situation

but
this does not really factor in to the equation (in this case).

Also, to keep things clear and supportable, make sure to document the reason for doing this.

Regards,
Daniel

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 Wed Jun 23 2004 - 04:55:41 CDT