Re: Question on how cardinality is calculated.
Date: Thu, 10 Jan 2008 20:09:39 -0000
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.
Author: Cost Based Oracle: Fundamentals
The Co-operative Oracle Users' FAQ
- Original Message ----- From: "Ronnie Doggart" <ronnie_doggart_at_lagan.com> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> 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 220.127.116.11
To build on the information provided, the actual query used in an application does the following:
select cases.a, cases.b, cases.c, cases.case, enquiry.a, enquiry.b, enquiry.c
from cases, enquiry
enquiry.id = cases.id
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.
http://www.freelists.org/webpage/oracle-l Received on Thu Jan 10 2008 - 14:09:39 CST