RE: *****SPAM***** Re: Question on how cardinality is calculated.
Date: Thu, 10 Jan 2008 12:07:47 -0500
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
[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
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- 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.
Does anyone know how Oracle calculates the cardinality of a query such as:
Select * from table_1 where upper(case) like '%12345%';
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 ?
http://www.freelists.org/webpage/oracle-l Received on Thu Jan 10 2008 - 11:07:47 CST