Re: Question on how cardinality is calculated.

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.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

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

All,

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

The oracle version is 9.2.0.8

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

Thanks

Ronnie

--

http://www.freelists.org/webpage/oracle-l Received on Thu Jan 10 2008 - 14:09:39 CST