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

From: Powell, Mark D <>
Date: Thu, 10 Jan 2008 12:07:47 -0500
Message-ID: <>


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-----

[] On Behalf Of Jonathan Lewis Sent: Thursday, January 10, 2008 11:22 AM To:; Subject: *****SPAM***** Re: Question on how cardinality is calculated.

5% of num_rows


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

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


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


-- Received on Thu Jan 10 2008 - 11:07:47 CST

Original text of this message