RE: Question on how cardinality is calculated.
- From: "Ronnie Doggart" <ronnie_doggart@xxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jan 2008 17:32:48 -0000
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
________________________________
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
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com <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@xxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, January 10, 2008 3:06 PM
Subject: Question on how cardinality is calculated.
All
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
--
//www.freelists.org/webpage/oracle-l
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
