RE: *****SPAM***** Re: Question on how cardinality is calculated.

Ronnie, as written the use of the upper function on case, unless an FBI is defined, would prevent the CBO from using any index on case. In the case of the posted example value no upper would be necessary to find digits while ignoring leading and trailing characters so if possible remove the function or move it to the right of the relational operator and the CBO will at least be able to consider using an available index. If there is no available index then this does not matter.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Thursday, January 10, 2008 11:22 AM To: ronnie_doggart_at_lagan.com; oracle-l_at_freelists.org Subject: *****SPAM***** Re: Question on how cardinality is calculated.

5% of num_rows

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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

• Original Message ----- From: "Ronnie Doggart" <ronnie_doggart_at_lagan.com> To: <oracle-l_at_freelists.org> Sent: Thursday, January 10, 2008 3:06 PM Subject: Question on how cardinality is calculated.

All

Does anyone know how Oracle calculates the cardinality of a query such as:

Select * from table_1 where upper(case) like '%12345%';

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=131 Card=5553 Bytes=621936)

   1 0 TABLE ACCESS (FULL) OF 'TABLE_1' (Cost=131 Card=5553 Bytes=621936)

Oracle calculates that it is expecting 5553 rows from this query but I know it will only return 2.

How does it do its estimate ?

Ronnie Doggart

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 10 2008 - 11:07:47 CST