RE: Why the monstrous SORT?

Yes, you're correct Jonathan (like there was any doubt :-)

SQL_test500>select count(*) from oe_line;

  COUNT(*)
----------
    252548

SQL_test500>set autot trace exp

SQL_test500>select count(*) from oe_line where not upper(item_no) LIKE 'AF-%';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=141 Card=1 Bytes=15)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'OE_LINE_2' (NON-UNIQUE) (Cost=141 
Card=12628 Bytes=189420)
                                                                                
^^^^^

12628/252548=.05

SQL_test500>select count(*) from oe_line where not upper(item_no) LIKE 'AF-%' 
and not upper(item_no) LIKE 'IFF%';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=141 Card=1 Bytes=15)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'OE_LINE_2' (NON-UNIQUE) (Cost=141 
Card=632 Bytes=9480)
                                                                                
^^^
632/12628=.05

Thanks!


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
Sent: Tuesday, July 12, 2005 1:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Why the monstrous SORT?



----- Original Message ----- 
From: <oracle-l-bounce@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Cc: "Vlad Sadilovskiy" <vlovsky@xxxxxxxxx>
Sent: Tuesday, July 12, 2005 12:58 AM
Subject: RE: Why the monstrous SORT?


AND NOT upper(a.item_no) LIKE 'AF-%'
      AND NOT upper(a.item_no) LIKE 'IFF%'

I wasn't aware until now that when applying functions in the 
predicate like this the CBO (apparently) uses a default 
selectivity of only 1%.
                        ^^^^^^

----

This depends on the nature of the predicate.
In the case of
    AND NOT upper(a.item_no) LIKE 'AF-%'
I think you'll find it's 5%

As far as I know it's only
    function(colx) = const
that gets 1%


Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005



--
http://www.freelists.org/webpage/oracle-l

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Other related posts: