| PB statistic CBO [message #202539] |
Fri, 10 November 2006 04:45  |
Haler
Messages: 71
Registered: October 2004
|
Member |
|
|
Hello
I'm problem with statistics CBO.
Here is result from tkprof.
1)Table=ARTICLE with index on item ZONAPP
HERE IS statistics on table and index.
SELECT CLEART,MARQUE,FAMILLE,CATEG,LARBDE,LARBDM,SERIE,STRUC,DIAMJ,DIAMP, etc...
FINCOM,TGAP,ROWIDTOCHAR(ROWID)
FROM
ARTICLE WHERE ZONAPP>=:1 AND (ZONAPP<:2 OR ZONAPP LIKE '1756514HGA%')
ORDER BY ZONAPP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.53 0.53 0 20957 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.53 0.53 0 20957 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 75
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 TABLE ACCESS FULL ARTICLE
2)Table=ARTICLE with index on item ZONAPP
HERE IS NO STATISTICS !
SELECT CLEART,MARQUE,FAMILLE,CATEG,LARBDE,LARBDM,SERIE,STRUC,DIAMJ,DIAMP,
CDEINTAUT,TYPART,UNITE,QCONDIT,DERACH,TC4,TEXTE1,TEXTE2,VAL4,VAL5,DEBCOM,
etc...
FINCOM,TGAP,ROWIDTOCHAR(ROWID)
FROM
ARTICLE WHERE ZONAPP>=:1 AND (ZONAPP<:2 OR ZONAPP LIKE '1756514HGA%')
ORDER BY ZONAPP
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 75
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT ORDER BY
1 CONCATENATION
1 TABLE ACCESS BY INDEX ROWID ARTICLE
1 INDEX RANGE SCAN ARTICLE_ZONAPP_NDX (object id 57801)
0 TABLE ACCESS BY INDEX ROWID ARTICLE
0 INDEX RANGE SCAN ARTICLE_ZONAPP_NDX (object id 57801)
WHY acces by index here (one stats)
and not in 1) (with stats)?
|
|
|
|
| Re: PB statistic CBO [message #202543 is a reply to message #202539] |
Fri, 10 November 2006 05:09  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Because when you have no statistics, you end up using the Rule Based Optimiser.
This is a (pretty complex) rule engine that generates the execution plan from a set of decisions about the objects involved in the query.
It will almose always decide to use an index based approach if possible.
|
|
|
|
|
|
| Re: PB statistic CBO [message #202610 is a reply to message #202572] |
Fri, 10 November 2006 09:24 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The CBO is in general substantially better than the RBO, IF it is given the statistics it needs to work on.
The RBO will still pick an index based plan, even if it needs to access every row in the table (a case in which a Full table scan would be far quicker).
Are your tables analyzed?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
