Home » SQL & PL/SQL » SQL & PL/SQL » Regarding High Water Mark and Index (Oracl 10g)
Regarding High Water Mark and Index [message #430736] Thu, 12 November 2009 01:46 Go to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Hi,

I have a doubt in HWM and Index phenomenon.
HWM - refers to High Water mark in Oacle data segements.
eg If I have inserted 100 rows in a table HWM will be at 100 th poistion and when ever I fire an SQL query oracle will search till 100 th position ..

Now suppose i create an index on that column ...now while retreiving the data will oralce check till HWM .. If yes then how does index retreives data fast...

Thanks
Re: Regarding High Water Mark and Index [message #430738 is a reply to message #430736] Thu, 12 November 2009 01:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Deleting entries from the table will delete them from the index as well, so searching the index will go faster.
The effect you describe (long processing time, even though the actual number of records is lower than the HWM) mainly comes into play with Full Table Scans
Re: Regarding High Water Mark and Index [message #430739 is a reply to message #430738] Thu, 12 November 2009 01:57 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
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

Re: Regarding High Water Mark and Index [message #430740 is a reply to message #430739] Thu, 12 November 2009 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case:
1/ Index is used if either you use a where clause that restrict on this column either you select only columns that are in the index
2/ In this latter case, it is faster because index is smaller than whole table so less blocks are to be scanned.

Regards
Michel
Re: Regarding High Water Mark and Index [message #430741 is a reply to message #430739] Thu, 12 November 2009 02:14 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
. 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 ...)


depending on the type of index the traversal will be more fast (like a B*tree traversal to find revelant rows or using a hash function)
,than going for linear traversal in case of no indexes.
Re: Regarding High Water Mark and Index [message #430742 is a reply to message #430739] Thu, 12 November 2009 02:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashish2345 wrote on Thu, 12 November 2009 08:57
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


Basically your question is how indexes work. It has not a lot to do with High Water Marks.
Read some documentation on the basics of indexes.
Re: Regarding High Water Mark and Index [message #430744 is a reply to message #430742] Thu, 12 November 2009 03:00 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Frank,

My concern is the relevance of HWM concept during index search ...

As per comments by Michel,

--2/ In this latter case, it is faster because index is smaller than whole table so less blocks are to be scanned. ---

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


Thanks a ton to all for guiding me..
Re: Regarding High Water Mark and Index [message #430746 is a reply to message #430744] Thu, 12 November 2009 03:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sounds to me like you are mixing up some things here.
You refer to the HWM, yet you say you did not delete any records.
This means, you are simply referring to the number of records in a table and you wonder why an index is so much faster than a FTS.
The HWM thing comes into play when you delete records. Simply put: the HWM stays at the number of records once in the table, whereas the number of records actually present may be a great deal lower. This means that a FTS would traverse blocks that don't contain any records, thus waisting resources.
Re: Regarding High Water Mark and Index [message #430747 is a reply to message #430744] Thu, 12 November 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: Regarding High Water Mark and Index [message #430748 is a reply to message #430746] Thu, 12 November 2009 03:25 Go to previous messageGo to next message
ashish2345
Messages: 50
Registered: September 2007
Member
Thanks alot Frank, you have absoultely clear my doubt and concept...

Re: Regarding High Water Mark and Index [message #430788 is a reply to message #430747] Thu, 12 November 2009 06:01 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel Cadot wrote on Thu, 12 November 2009 03:25
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


Sir, what I undestood is when we talk about HWM we talk about an attribute associated with tables only.Does the same applied to index as well.

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.
This can also happen when many rows are deleted; a common occurrence in a fast-refresh Materialized View.
And we call it as Fragmented Index



Quote:

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.


Thanks
Re: Regarding High Water Mark and Index [message #430793 is a reply to message #430788] Thu, 12 November 2009 06:10 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

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.


Check this link.

http://richardfoote.wordpress.com/category/index-rebuild/

You will be surprised how oracle indexes works.

Regards

Raj
Re: Regarding High Water Mark and Index [message #430795 is a reply to message #430788] Thu, 12 November 2009 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
High Water Marks are only relevant to indexes in the context of a Fast Full Index scan, when the database reads all the index blocks from first to last without doing any sort of search through the tree structure.

Index fragmentation as you are describing it is not a problem - each data block in the index holds a range of values, as specified by the leaf block pointing to it.

If extra rows are added to the table, then appropriate values are added to the index. If there are empty spaces in the index data block, then they are re-used.

It is possible to get sparsely populated blocks, but only in cases where index blocks start full, and then rows are deleted from the table and new rows with those values are never re-added.

In general, any performance gain that you get from re-organising an index to 'de-fragment' it will incurr a performance penalty to the system in the weeks following as the database has to split down the densely populated blocks as rows continue to move round.
Re: Regarding High Water Mark and Index [message #430820 is a reply to message #430788] Thu, 12 November 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sir, what I undestood is when we talk about HWM we talk about an attribute associated with tables only

HWM exist for al kind of physical objects: segments (of any type, table, index, partition), files and so on.

It is relevant for a table when full table scan occurs or when new rows are inserted, it is relevent for an index when an index fast full scan occurs or when new values implies new blocks, it is relevant when you want to shrink it or allocate new extends and so on.

Regards
Michel
Re: Regarding High Water Mark and Index [message #430842 is a reply to message #430820] Thu, 12 November 2009 08:36 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

@Raj, I have not got chance to read the link provided as yet. I would go through it by the end of the day.

Thanks to you all!!

Regards
Ved
Previous Topic: Convert rows to cols with distinct, sys_connect_by_path
Next Topic: problem with multiple "not in" conditions in query
Goto Forum:
  


Current Time: Tue Sep 27 14:31:22 CDT 2016

Total time taken to generate the page: 0.11371 seconds