| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Calculating IX_SEL in 10053.
>
> It's always worth checking the full execution
> plan - possibly hinted to make Oracle take a
> path that has been rejected - so that you can
> see what access and filter predicates Oracle has
> produced from your inputs.
>
Thanks for the feedback.
Here is the full explain plan - unhinted....
| Id | Operation | Name | Rows |Bytes | Cost |
| 0 | UPDATE STATEMENT | | 1 |
20 | 3 |
| 1 | UPDATE | PS_TY_MF_TIM_EMP | |
| |
|* 2 | INDEX RANGE SCAN | PS_TY_MF_TIM_EMP | 1 |
20 | 2 |
| 3 | SORT AGGREGATE | | 1 |
27 | |
|* 4 | FILTER | | |
| |
|* 5 | TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME | 1 |
27 | 5 |
|* 6 | INDEX RANGE SCAN | PSCTL_PAYABLE_TIME | 1 |
| 4 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access("SYS_ALIAS_2"."PROCESS_INSTANCE"=1145447)
4 -
filter(TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'fmy')<=TO_DATE('24-DEC-06','DD-M
ON-YY'))
5 - filter("PAY"."EMPL_RCD"=:B1)
6 - access("PAY"."EMPLID"=:B1 AND "PAY"."TRC"='C169' AND
"PAY"."DUR">=TRUNC(TO_DATE('27-NOV-06','DD-MON-YY'),'fmy') AND
"PAY"."DUR"<=TO_DATE('24-DEC-06','DD-MON-YY'))
It looks like it is using EMPLID, TRC, and DUR. But like you say, even with the extra selectivity added, the overall selectivity is way out.
> This may be because of an index sanity check that kicks in
> to stop the selectivity falling below 1/distinct_keys for the
> index.
I wasn't aware of this check....Its difficult to keep up with beast that is the CBO..!
Is there any way I can work out how it arrives at the IX_SEL and TB_SEL values. Because it is obviously applying some calculations which are different to the formula that you include in your book.
Matt Received on Fri Jan 05 2007 - 07:34:49 CST
 |
 |