Re: Question about Append hint in Insert

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Jun 2004 10:58:48 +0100

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@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: