Re: Performance problem: anything else I can do to make this faster?

Well, may be your environment is just "too suitable" for full-table scan. What are the values for following parameters - (none can be said off-hands, but the following params mostly dictate the type of access scan chosen) --

• optimizer_index_cost_adj
• optimizer_index_caching
• db_file_multiblock_read_count
• db_block_size
• workarea_size_policy (auto or manual)?
• clustering factor of the index -- may be, most of the rows have and emp.department_id = 40101 AND link.category_id = 7 AND emp.type_id = 2; ?
• Try using the hints -- (a) /*+ USE_NL(emp link) */ (b) try 'alter session set '_fast_full_scan_enabled' = false; (c) use implicit index hint for the single-column index on id (if any!)

I don't think you're using RULE based optimizer - since

(a) the explain plan shows
"Optimizer=CHOOSE"
(b) RBO cannot do HASH joins.. Received on Wed Mar 02 2005 - 19:41:27 CST