RE: Question on how cardinality is calculated.

  • From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
  • To: "ronnie_doggart@xxxxxxxxx" <ronnie_doggart@xxxxxxxxx>, "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 10 Jan 2008 12:47:40 -0600

I hope you have a function based index on upper(case).  If so, then you could 
try an index hint or first_rows hint, but it will still do a full index scan 
because the "like '%123456%'" can't be done with a range scan.  Why do you need 
to do an upper if you are going to have the user entering digits anyway? 
Upper('123456') is the same as '123456'.  Take the upper out and then you can 
create a regular index.  That could be the whole problem to begin with.  A 
regular index will not be used if you have a function on the column.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Ronnie Doggart
Sent: Thursday, January 10, 2008 11:33 AM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Question on how cardinality is calculated.


Thanks to those who answered, the majority pointing out that it is 5% of the 
number of rows.

The oracle version is

To build on the information provided, the actual query used in an application 
does the following:

select cases.a, cases.b, cases.c,, enquiry.a, enquiry.b, enquiry..c 
from cases, enquiry where = and upper(case) like '%123456%';

Now the user always enters six digits which I know identifies 1 record in the 
cases table, but because Oracle uses a cardinality of 5553 for this it performs 
a hash join to the enquiry table, even though only one record will match in the 
enquiry table. The enquiry table contains 1.4 million record so it takes ~5 

Would it be advisable to put a hint on the query to either use a nested loop 
which allows the query to use an index to retrieve the data from enquiry table. 
Or use a cardinality hint since I know we will not be returning 5553 rows which 
will also allow the use of an index.




From: oracle-l-bounce@xxxxxxxxxxxxx on behalf of Jonathan Lewis
Sent: Thu 1/10/2008 4:22 PM
To: Ronnie Doggart; oracle-l@xxxxxxxxxxxxx
Subject: 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" <ronnie_doggart@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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


The information in this message is confidential and may be legally privileged. 
It is intended solely for the addressee. Access to this message by anyone else 
is unauthorised. If you are not the intended recipient, any disclosure, 
copying, or distribution of the message, or any action or omission taken by you 
in reliance on it, is prohibited and may be unlawful. Please immediately 
contact the sender if you have received this message in error.

The views and opinions expressed in this email may not reflect the views and 
opinions of any member of Lagan Technologies Limited, or any of its 
subsidiaries. Lagan Technologies Limited is a company registered in Northern 
Ireland with registration number NI 28773. The registered office of Lagan 
Technologies Limited is 209 Airport Road West, Belfast, Co. Antrim, BT3 9EZ.


Information transmitted by this email is proprietary to Medtronic and is 
intended for use only by the individual or entity to which it is addressed, and 
may contain information that is private, privileged, confidential or exempt 
from disclosure under applicable law. If you are not the intended recipient or 
it appears that this mail has been forwarded to you without proper authority, 
you are notified that any use or dissemination of this information in any 
manner is strictly prohibited. In such cases, please delete this mail from your 

To view this notice in other languages you can either select the following link 
or manually copy and paste the link into the address bar of a web browser:

Other related posts: