Re: Cost is higher but query is much faster

In article <a7tadp$8nu7f$1_at_ID-114862.news.dfncis.de>, "Alan" says...
>
>Also, remember that the best CBO query will never perform any faster than an
>optimally constructed RBO query.
>

you are totally kidding right?

What about:

select count(*) from one_billion_row_table where unindex_column = 5;

Parallel query kicks in -- fast = true;

select count(*) from one_billion_row_table whre bitMappedIndexed_column = 5;

and count( distinct bitMappedIndexed_column ) = 10.... with RBO -- full scan, slow. with CBO -- bitmap index, fast....

or how about wanting to use partitions (now RBO is impossible, cannot be done)

or IOTS (index organized tables), rbo = no go...

or speeding up a query like:

select * from t where upper(column) = 'ABCDEFG';

function based indexes -- used with CBO -- means speed....

and the list goes on and on and on. With every dot release there is more and more and more that cannot be done with RBO that can be done with CBO. There are quite simply many features that are NOT available with RBO.

>
>"Ed prochak" <ed.prochak_at_alltel.com> wrote in message
>news:e51b160.0203271037.2bfeed19_at_posting.google.com...
>> While there is great technology in the ORACLE Optimizer, it still
>> basically is making a guess. It views the query with the hint as
>> costing more because it expects to do more I/O (read the index, then
>> read the table). It is not clear to the Optimizer that the index will
>> help as much as it does IN THIS CASE.
>>
>> A different date value might result in the hint version taking longer.
>> The Optimizer tries to do the best for all cases.
>>
>> just my 2cents
>> Ed
>>
>>
>> crockydile_at_hotmail.com (BG) wrote in message
>news:<c1e19cc6.0203261417.ec8d5de_at_posting.google.com>...
>> > Hello.
>> >
>> > I am under the impression that a lower cost is better than a higher
>> > cost.
>> >
>> > I have never tuned before but had to do to a query taking several
>> > minutes to complete (about 150,000 rows). When I did not give a hint
>> > the query was doing a full table scan and its cost was 2892. After I
>> > gave it a hint its cost went up to 44141 but it is now lightning fast.
>> >
>> > I notice that with the hint that disk IO is significantly less and
>> > this probably accounts for the speed, but why did the cost go up so
>> > much??
>> >
>> > Thanks
>> > Brian
>> >
>> >
>> >
>> >
>> >
>> >
>> > SELECT
>> > --SELECT /*+ INDEX(messages dt_recvd, MESSAGES_IND4) */
>> > msgseqnum,
>> > msgtype,
>> > cust_id,
>> > msgid,
>> > LOWER (sender),
>> > LOWER (receiver),
>> > NVL (SUBSTR (subject, 1, 25), '<>'),
>> > NVL (status, '<>'),
>> > TO_CHAR (dt_recvd, 'Mon DD, YYYY HH24:MI A.M.')
>> > FROM messages
>> > WHERE (dt_recvd between TO_DATE ('Mar 26, 2000', 'Mon dd, yyyy')
>> > AND TO_DATE ('Mar 26, 2002', 'Mon dd, yyyy') + 1)
>> > AND msgtype = UPPER ('new')
>> > AND cust_id <> '_none_'
>> >
>> >
>> >
>> > *(Results without giving an index hint)*
>> > Execution Plan
>> > ----------------------------------------------------------
>> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2892 Card=33934 Byte
>> > s=4818628)
>> >
>> > 1 0 TABLE ACCESS (FULL) OF 'MESSAGES' (Cost=2892 Card=33934 By
>> > tes=4818628)
>> > Statistics
>> > ----------------------------------------------------------
>> > 0 recursive calls
>> > 4 db block gets
>> > 186388 consistent gets
>> > 183632 physical reads
>> > 0 redo size
>> > 2077 bytes sent via SQL*Net to client
>> > 317 bytes received via SQL*Net from client
>> > 2 SQL*Net roundtrips to/from client
>> > 0 sorts (memory)
>> > 0 sorts (disk)
>> > 14 rows processed
>> >
>> >
>> > (Results with an index hint)
>> > Execution Plan
>> > ----------------------------------------------------------
>> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44141 Card=33934 Byt
>> > es=4818628)
>> >
>> > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MESSAGES' (Cost=44141 Ca
>> > rd=33934 Bytes=4818628)
>> >
>> > 2 1 INDEX (RANGE SCAN) OF 'MESSAGES_IND4' (NON-UNIQUE) (Cost
>> > =361 Card=33934)
>> > Statistics
>> > ----------------------------------------------------------
>> > 0 recursive calls
>> > 0 db block gets
>> > 12049 consistent gets
>> > 11710 physical reads
>> > 0 redo size
>> > 2100 bytes sent via SQL*Net to client
>> > 317 bytes received via SQL*Net from client
>> > 2 SQL*Net roundtrips to/from client
>> > 0 sorts (memory)
>> > 0 sorts (disk)
>> > 14 rows processed
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp