. I am fetching records without any index on the table..

2. I created the index on coumn and then fetch record based on the column .. same query as used in 1 ...

In above 2 conditions in second time taken will be less , ( why is it so as it has to move to HWM in case 2 also ...)

Thanks a lot Frank for immediate reply..

I am re-phrasing my self...
Suppose Ihave table with 1000 rows and I don't delete them ..

It has HWM at 1000th position ....

There r 2 cases ..

1. I am fetching records without any index on the table..

2. I created the index on coumn and then fetch record based on the column .. same query as used in 1 ...

In above 2 conditions in second time taken will be less , ( why is it so as it has to move to HWM in case 2 also ...)

Thanks a lot in advance

It means that during index scan it will not search till HWM but till blocks where it will get match ...

Quote:

It means that during index scan it will not search till HWM but till blocks where it will get match ...

Yes it scan til the HWM of the INDEX not the TABLE in this case.

Regards
Michel

Oracle tables have an attribute called the High Water Mark. This is the highest numbered block in the table that has ever contained a row. When an INSERT statement cannot find a free block to populate, it goes straight to the High Water Mark and allocates a new block.

When rows are deleted, the High Water Mark does not come down. This is not normally a problem, except for Full Table Scans, because a Full Table Scan must search every block beneath the High Water Mark for data.

If a table contains 1000 rows, then you would expect a full table scan to be fairly speedy. But if it once contained 100000 rows, then you would be wrong: the full table scan of the 1000 rows will take roughly the same amount of time as a 100000 row full table scan.

If the table is continually growing and shrinking (through regular deletes), then you should look for a different way to manage it. Perhaps it can be partitioned, so that instead of deleting rows you truncate or drop partitions.

If the table is not going to grow back to full size in the medium term, then it should be dropped and rebuilt by the DBA to move the High Water Mark back down to an appropriate level.

When we frequently update /delete an indexed column,it usually involves deleting it from one position in the index and inserting into another place. If this happens often enough, the index can become riddled with holes. Range scans on the index will read more blocks than they have to because each block is under-utilised.

Sir, what I undestood is when we talk about HWM we talk about an attribute associated with tables only