> Letters are processed for each individual employer on a program that > runs on our app-server. I don't need to get into the evils of row-by- > row processing. It's not my batch job but it's my problem now that > the queries are no longer performing. I was expecting such an answer - still it's important to raise the question. > I dont recall ever saying that a FTS is faster. I'm saying that what > used to take a fraction of a second per execution is now taking > upwards of 30 seconds. This table has a pair of inline CLOBS so an > FTS can sometimes take a while. > > Both execution plans can be found at > > http://www.neilkodner.com/allstats_both_queries.txt But those plans don't show evidence that they perform differently - the only difference is a hash join instead of a nested loop and a hash unique instead of sort unique but the number of blocks processed is the same - these two examples ought to perform almost equally. The access to LETTER_BIN is the same operation and since PARTIES returns exactly a single row I can't identify why these two plans should show a significantly different run time behaviour - and according to the ALLSTATS LAST output both completed in the same time (less than a second since no physical reads were involved). When comparing actual execution time you need to be very careful if physical reads are involved or not - reading 2,000 blocks at 8ms per block will take significantly more time than reading those 2,000 from memory. Can you provide an example with measured different runtime behaviour? By the way, the LETTER_BIN table seems to have a significant number of migrated/chained rows - probably due to the large rows as you say with inline CLOBs. > As a trial, I also modified the query to use parties.party_id rather > than letter_bin.prty_id - it had no effect on the plan's cardinality. Sorry, my intention here was to have both in the query - a filter on PARTIES.PRTY_ID _and_ LETTER_BIN.PRTY_ID to avoid the transitive closure from taking place. Regards, Randolf Oracle related stuff blog: http://oracle-randolf.blogspot.com/ Co-author of the forthcoming "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684 ___________________________________________________________ Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.! http://produkte.web.de/go/02/ -- //www.freelists.org/webpage/oracle-l