Re: HELP - Terrible query performance on newly partitioned table

Hello Jim,

a few things worth mentioning.
1. Partitioned tables will always force the CBO te be used.

   Using the RULE hint will not change that behaviour. 2 The CBO, in this case, because of the CHOOSE setting, will try to

   optimize ALL_ROWS, which might not be desirable.    This favours full table scans / hash joins above index scans.    Also, without statistics, the CBO usually produces awsome inefficient    plans.
3 The difference between selecting 'count(*)' and '*' is that there is

   probably no need for table access in order to retrieve columns like    DEB_BRANCH_CODE, much more can be done be reading solely the index.

My advice:
1. Analyze all tables involved. My experience is that

   'analyze table <table_name. estimate statistics for table for all indexes     for all columns' will do (thus using default sample size). 2. Use FIRST_ROWS hint, as it will tend to use indexes on the joined tables.

   If that does not give the desired plan, add USE_NL hints on the joined    tables - for all tables involved.
3. Use an additional ORDERED hint, if parsing time is (too) long.

   It might also help in forcing the USE_NL hints to work.

Kind Regards,

Herman de Boer
IT Consultancy Group bv
the Netherlands Received on Fri Jan 04 2002 - 12:38:32 CST