Re: I still believe CBO is better than RBO, but WTH?

When I said 'cardinality' I meant the figure coming from the cardinality column of the explain plan - however, that might actually be the number you've printed against each line anyway.

Can you give us the index definitions - i.e. list of columns - for each of the three indexes used, and whether they are supposed to be unique or not, and which columns are nullable.

>Yeah, but this should in fact be faster. This one, in all, will pull
>about 230 rows from ZOT, which then get joined to PSTREESELECT06. It
>should be faster, but isn't. The other one pulls about 9000 rows

But how many times does it try to access the ZOT table, - i.e. how many rows are first returned from PS_PSTREESELECT10, and how many index entries from ZOT_i1 have to be examined, and how many rows, before the 230 rows are returned ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.