Re: Oracle 8i and poor query performance in some queries

Niall Litchfield wrote:
> But if you are doing a FTS then the blocks go on the LRU list and get aged
> out rapidly. After all no-one in their right mind would scan the same blocks
> 35000 times when they could do it once now would they :(. It really seems
> like these tables get accessed frequently by the id column (and indeed there
> may even be RI between the columns) in which case indexing the id columns
> will probably cure this and other issues.

Indexing id columns is the next step I'll try.

Sorry that I did not mention this before, but the tables a1 and a2 are copies from the production environment. They were created using "create table copy_table as select * from original_table;" for testing purposes only and so they are currently unindexed. The original tables where the ones that had the performance problems in the first place. Column id is indexed in original tables and there is a foreign key relationship between the tables. But query ran as slow on them as it runs on these copies. Now this makes me think this: sure, the original tables were indexed but were they analyzed? I'll check that and get back to you.

--
Heikki