| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: Tuesday, July 12, 2005 1:37 AM
To: oracle-l_at_freelists.org
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
Received on Tue Jul 12 2005 - 11:46:10 CDT
 |
 |