RE: Question on how cardinality is calculated.
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "'Mark W. Farnham'" <mwf@xxxxxxxx>, <ronnie_doggart@xxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 12 Jan 2008 13:12:06 -0500
One more thing - somewhere else in the thread you've asserted there is
exactly one match for the case like '%123456%'.
Now without reengineering the data you have, you're going to have to at
least scan an index on case. If I recall correctly you've got something like
1.3 M rows in enquiry and .11 M rows in cases, and currently you get what
you need in about 5 seconds (deemed unacceptable). If about .5 seconds is
acceptable, then if you don't have any type conversions going on it seems
likely the optimizer will choose either a table scan or a fast full index
scan on the case index to get you cases.id and then use the index lookup on
enquiry.id if you formulate the query as:
select c.a, c.b, c.c, c.case, e.a, e.b
from enquiry e, cases c
where e.id =
(
select c2.id
from cases c2
where c2.case like '%123456%'
and rownum < 2
)
and e.id = c.id;
You can leave out the "and rownum < 2" bit if you want it to blow up if your
assertion is wrong. You could also switch it to an "in" clause, but I
haven't tested whether the optimizer uses a presumptive 1 cardinality for
equals when the syntax will otherwise blow up. (It should, since any other
cardinality should blow up.) For an "in" clause the optimizer could not
validly make that assumption. Without testing your exact case in your exact
environment, I don't know enough to predict whether either the "=" or "in"
alternative will have its predicates pushed so you end up with still
scanning enquiry for a hash resolution. Notice there is a presumption that
c.id is unique - if you had multiple different cases for a single id this
would not be an isofunctional rewrite.
Jonathan's suggestion to reengineer the data collection should result in a
very small difference between actual fetch time and zero, since the
optimizer per that suggestion should be able to index lookup a single row
from cases and the one or two rows by index for enquiry. But if you can't do
that, the re-write above might work out good enough.
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Thursday, January 10, 2008 3:45 PM
To: 'ronnie_doggart@xxxxxxxxx'; 'jonathan@xxxxxxxxxxxxxxxxxx';
'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Question on how cardinality is calculated.
try
select c.a, c.b, c.c, c.case, e.a, e.b
from enquiry e,
(select id, a, b, c, case from cases where case like '%123456%) c
where e.id = c.id;
notice I've eliminated the upper function, since unless there is some
character set I am ignorant of where digits are stored differently for upper
and lower case, it is irrelevant.
if that does not immediately work, you might embark on hinting. Presumably
you want to hint either the index on case for the virtual table query or
cardinality of 1 and hint the id index on enquiry. Adding the pseudocolumn
rownum to the virtual table query might also help, like:
select c.crownum,c.a,c.b,c.c,c.case, e.a, e.b
from enquiry e,
(select rownum crownum,id, a, b, c, case
from cases
where case like '%123456%') c
where e.id = c.id;
if the cardinality hint does not function correctly (if I recall correctly
that comes and goes at various releases and I'm not sure which) and the hint
to use the case index does not generate the lowest cost join plan because of
the 5% thing, then you might get the case rows through a gtt, although that
does not in general scale and grabs latches you probably don't want to grab
if this is a busy system in a parallel sense. If it is not high concurrency
and you just need snappier response for a few users, at least the optimizer
will have good cardinality in hand from the gtt subset of case so it should
use the index on enquiry. Notice this is definitely not your first choice.
If you're already slapping a generated literal in there for the "case like"
to reparse it probably scales no worse than what you're currently doing.
You might think that further adding a "and rownum < 2" predicate to the
virtural table query would stand in on releases where the cardinality hint
does not function as expected, but my testing has not found a release on
which Oracle semantically scans for a rownum predicate to definitely limit
the cardinality. They could reliably do this, but for whatever reason they
apparently do not. Of course adding that substitutes your assurance that
only one row can be retrieved for real world results, but I have not
experienced it working, anyway.
JL can probably suggest a more direct way to get the plan you want.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Ronnie Doggart
Sent: Thursday, January 10, 2008 12:33 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
________________________________
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
--
//www.freelists.org/webpage/oracle-l
Other related posts: