Re: Bitmap index - how many rows get locked

There is currently no realistic way you can discover how many rows are locked when you change the bitmap-indexed column of a single row. The number is dependent on blocksize, distribution of data, and the coincidental overlap of the FROM and TO bitmap chunks.

The answer could vary from a couple of dozen up to about 128,000.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 01 October 2001 01:45

|Hi,
|
|Is it possible to find out the actual number of locked rows?
|
|For example:
|
|SQL> desc test
| Name Null? Type
| ----------------- -------- ------------
| F NUMBER(38)
|
|SQL> select * from test;
|
| F
|----------
| 1
| 1
| 1
| 2
| 3
|
|5 rows selected.
|
|SQL> update test set f=f where f=2 or f=3;
|
|2 rows updated.
|
|SQL>
|
|Is there a view I can query to find out that 2 rows are locked?
|
|v$locked_object will let me see that there is 1 table locked and find
out
|which table it is.
|v$lock will show 1 * TM lock and 1 * TX lock.
|
|
|Putting this another way, if I use bitmap indexes and update a field
in the
|index, I may lock more than 1 row (Oracle will use
bitmap-segment-level
|locking). How can I find out how many rows are locked in this case.
|
|For a specific case I guess I should find out how many rows are in a
single
|bitmap-segment but I haven't yet investigated how to do this.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).