 Problem on understanding cardinality of index [message #319206] |
Fri, 09 May 2008 06:11  |
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)
|
|
|
|
|
|
|
|
| Re: Problem on understanding cardinality of index [message #319222 is a reply to message #319206] |
Fri, 09 May 2008 06:44  |
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)
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
