Re: Question on how cardinality is calculated.

From: Jonathan Lewis <>
Date: Thu, 10 Jan 2008 20:09:39 -0000
Message-ID: <024201c853c4$bb3690f0$0200a8c0@Primary>

I think I'd go for the complete hinted plan rather than using a cardinality() hint, as the hint may get phased out in favour of the opt_estimate() hint in the future.

Before that, I'd look at options for creating some strategy for writing the code that didn't require a full tablescan on the CASES table. Is it possible to have a trigger that extracts the numeric bit that you want when the data is inserted and updated - if so you could create a reference table holding that and the PK of CASES to do the job you want.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

  • Original Message ----- From: "Ronnie Doggart" <> To: <>; <> Sent: Thursday, January 10, 2008 5:32 PM 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 seconds.

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.



-- Received on Thu Jan 10 2008 - 14:09:39 CST

Original text of this message