Home » RDBMS Server » Performance Tuning » Problem on understanding cardinality of index (10.2.0.3)
icon9.gif  Problem on understanding cardinality of index [message #319206] Fri, 09 May 2008 06:11 Go to next message
stsy
Messages: 6
Registered: August 2005
Junior Member
Hello
I have a little difficulty to understand an explain plan for the following query
select /*+ index(mvt,FK_T_MOUVEMENTSTOCK11) */
mvt.*
from
samse.t_redressement tredress,samse.t_mouvementstock mvt
where
mvt.id_redressement=tredress.id_redressement and
tredress.datedernieremodification >= TO_DATE ('01/03/08', 'DD/MM/YY')
AND tredress.datedernieremodification <
(TO_DATE ('31/03/08', 'DD/MM/YY') + 1);

I join you the 10053 trace
I don t understand the cardinality 18 in the index scan.
If i make a select count(*) from t_mouvementstock where id_redressement=:b1 the optimizer estimates the ca
rdinality to 1 (almost 1 000 000 distinct values of id_redressement on 1 000 000 lines on t_mouvementstock where id_redressement is not null) . So why shows me
the 18 cardinality in the explain plan ?
Thanks for your help (excuse for my bad english)
10053 trace forgotten [message #319209 is a reply to message #319206] Fri, 09 May 2008 06:17 Go to previous messageGo to next message
stsy
Messages: 6
Registered: August 2005
Junior Member
No Message Body
Re: Problem on understanding cardinality of index [message #319211 is a reply to message #319206] Fri, 09 May 2008 06:18 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Because the index scan will be performed 18 times as part of a loop?

Obviously you haven't provided us with much to go on...
Re: Problem on understanding cardinality of index [message #319222 is a reply to message #319206] Fri, 09 May 2008 06:44 Go to previous messageGo to next message
stsy
Messages: 6
Registered: August 2005
Junior Member
I think i have difficulty to read this explain plan as i dont understand this 18. First it reads t_redresseemnt. Then for each row it reads FK_T_MOUVEMENTSTOCK11 to find the row with the same id_redressement. For me, the 18 should correspond to the estimated cardinality of almost "select count(*) from t_mouvementstock where id_redressement is not null and id_redressement=onevalueofidredressement so almost 1. That the optimizer makes with the 5- of Predicate Information
Miss i something ?
-------------------------------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 228K | |
| 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_MOUVEMENTSTOCK | 1 | 68 | 10 | 00:00:01 |
| 3 | NESTED LOOPS | | 29K | 2417K | 228K | 00:47:38 |
| 4 | TABLE ACCESS FULL | T_REDRESSEMENT | 23K | 321K | 3447 | 00:00:42 |
| 5 | INDEX RANGE SCAN | FK_T_MOUVEMENTSTOCK11| 18 | | 2 | 00:00:01 |
-------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - filter(TO_DATE('01/03/08','DD/MM/YY')<TO_DATE('31/03/08','DD/MM/YY')+1)
4 - filter(("TREDRESS"."DATEDERNIEREMODIFICATION">=TO_DATE('01/03/08','DD/MM/YY') AND "TREDRESS"."DATEDERNIEREMODIFICATION"<TO_DATE('31/03/08','DD/MM/YY')+1))
5 - access("MVT"."ID_REDRESSEMENT"="TREDRESS"."ID_REDRESSEMENT")
5 - filter("MVT"."ID_REDRESSEMENT" IS NOT NULL)
Re: Problem on understanding cardinality of index [message #319224 is a reply to message #319222] Fri, 09 May 2008 06:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How up to date are your statistics and how do you gather them?
Re: Problem on understanding cardinality of index [message #319225 is a reply to message #319206] Fri, 09 May 2008 06:52 Go to previous messageGo to next message
stsy
Messages: 6
Registered: August 2005
Junior Member
Thanks for your replies

Statistics are done every day with dbms_stats.gather_table_stats
No histograms are done on id_redressement of t_mouvementstock
Re: Problem on understanding cardinality of index [message #319228 is a reply to message #319206] Fri, 09 May 2008 06:55 Go to previous message
stsy
Messages: 6
Registered: August 2005
Junior Member
Smartin, when you say "Because the index scan will be performed 18 times as part of a loop" it means that it is the total number of index range scan estimated for all the query ?
Thanks
Previous Topic: why oracle choose NL in this case?
Next Topic: Tuning Error with Order by clause
Goto Forum:
  


Current Time: Fri Dec 02 12:15:53 CST 2016

Total time taken to generate the page: 0.07775 seconds