Most relational databases stores data in row major format, each row has
multiple columns, and Oracle accessing these columns by reading a row, locating
a column, reading a value (if it exists) and display to end users, these ROWID
/ Index based access are very efficient for most general purpose and OLTP query
engines. On the other hand if we want a single column from the table – perhaps
to perform an aggregation, we incur significant overhead, wider the table
greater the overhead.
Column databases engine operate on column rather than rows. It stores
the value for the column co-located, potentially with lots of optimization
already included in the way it stores the column. This is likely to make the
column based access fast. Instead of having to read the whole row to extract
just the value of a single column, the engine can iterate over a large block
and retrieve many values in multi block operation, columnar databases are
geared towards analytic or read mostly workloads.
Oracle Hybrid columnar compression (HCC) combines the advantage of
columnar data organization in that it stores columns separately within a new
storage type, called compression-unit or CU. But unlike the pure columnar databases
it doesnot neglect the “ROWID based access” the CU is written contiguously to
the disk in the form of multiple standard Oracle blocks. Information pertaining
to a given row is within the same CU, allowing Oracle to blindly issue one or
two read request matching the size of CU and be sure that the row information
has been retrieved.
Updating a single row in HCC compressed table locks the entire CU
containing the row. This can cause extreme contention issue for OLTP type
systems, locking the entire CU is the main reason that HCC is not recommended
for tables/partitions where data is actively updated.
demo@PDB1>
create table stage_qh
2
compress for query high
3 as
4
select * from big_table
5
where rownum <=1000000;
So we got a table HCC compressed for query high and size of the table
is around 10MB and got 1171 blocks filled completely.
demo@PDB1>
exec show_space('STAGE_QH');
Unformatted
Blocks .....................
0
FS1 Blocks
(0-25) ......................
0
FS2 Blocks
(25-50) .....................
0
FS3 Blocks
(50-75) .....................
0
FS4 Blocks
(75-100).....................
0
Full Blocks
............................
1,171
Total Blocks............................ 1,280
Total
Bytes.............................
10,485,760
Total
MBytes............................
10
Unused
Blocks...........................
81
Unused
Bytes............................
663,552
Last Used Ext
FileId....................
24
Last Used Ext
BlockId...................
16,786,944
Last Used
Block.........................
47
Now looking at the data representation
demo@PDB1>
with function rowid_info ( p_rid in rowid )
2
return varchar2 as
3
l_data varchar2(80);
4
l_rowid_type number;
5
l_object_number number;
6
l_relative_fno number;
7
l_block_number number;
8
l_row_number number;
9
begin
10 dbms_rowid.rowid_info(
11 rowid_in => p_rid ,
12 rowid_type => l_rowid_type,
13 object_number => l_object_number,
14 relative_fno => l_relative_fno,
15 block_number =>l_block_number ,
16 row_number =>l_row_number );
17 return
(l_relative_fno||'.'||l_block_number||'.'||l_row_number);
18 end;
19 select id, rowid_info(rowid) x from stage_qh
where rownum <= 10;
20 /
ID X
----------
------------------------------
2001 4.8563.0
2002 4.8563.1
2003 4.8563.2
2004 4.8563.3
2005 4.8563.4
2006 4.8563.5
2007 4.8563.6
2008 4.8563.7
2009 4.8563.8
2010 4.8563.9
10 rows
selected.
We got the values 2001 to 2010 all present in the same block (i.e,
8563),
demo@PDB1>
update stage_qh set object_name = 'xx' where id = 2001;
1 row updated.
After updating one row, we lock the entire CU- which means no other
transaction cannot perform any changes to other rows in same CU. Doing so we will
end up with an error like this
demo@PDB1>
declare
2
pragma autonomous_transaction;
3
begin
4
update stage_qh set owner ='xx'
5
where id = 2002;
6
rollback;
7 end;
8 /
declare
*
ERROR
at line 1:
ORA-00060:
deadlock detected while waiting for resource
ORA-06512: at
line 4
However this got changed in 12c and above, the support for Row level
locking was added in HCC. Which means we can set some space in CU header aside
for tracking DML’s, to enable this feature we need add a new syntax for HCC
demo@PDB1>
create table stage_qh_rll
2 compress for query high row level locking
3 as
4
select * from stage_qh;
Table created.
demo@PDB1>
select table_name,compression,compress_for
2 from
user_tables
3
where table_name like 'STAGE%';
TABLE_NAME COMPRESS COMPRESS_FOR
---------------
-------- ------------------------------
STAGE_QH ENABLED
QUERY HIGH
STAGE_QH_RLL ENABLED
QUERY HIGH ROW LEVEL LOCKING
demo@PDB1>
exec show_space('STAGE_QH_RLL');
Unformatted
Blocks .....................
0
FS1 Blocks
(0-25) ......................
0
FS2 Blocks
(25-50) .....................
0
FS3 Blocks
(50-75) .....................
0
FS4 Blocks
(75-100).....................
0
Full
Blocks ............................
1,343
Total
Blocks............................
1,408
Total
Bytes.............................
11,534,336
Total
MBytes............................ 11
Unused
Blocks...........................
35
Unused
Bytes............................
286,720
Last Used Ext
FileId....................
24
Last Used Ext
BlockId...................
16,788,352
Last Used
Block.........................
93
First of all we can see that “Row level locking” has been requested and
applied to the table, when we compare the size of the tables we can notice the
extra space added to CU header - got increased to 11MB and the number of full
blocks were increased from 1171 to 1343 – to keep track of all DML operations.
Looking at the data representation it was like this
demo@PDB1>
with function rowid_info ( p_rid in rowid )
2
return varchar2 as
3
l_data varchar2(80);
4
l_rowid_type number;
5
l_object_number number;
6
l_relative_fno number;
7
l_block_number number;
8
l_row_number number;
9
begin
10 dbms_rowid.rowid_info(
11 rowid_in => p_rid ,
12 rowid_type => l_rowid_type,
13 object_number => l_object_number,
14 relative_fno => l_relative_fno,
15 block_number =>l_block_number ,
16 row_number =>l_row_number );
17 return (l_relative_fno||'.'||l_block_number||'.'||l_row_number);
18 end;
19 select id, rowid_info(rowid) x from
stage_qh_rll where rownum <= 10;
20 /
ID X
----------
------------------------------
2001 4.8691.0
2002 4.8691.1
2003 4.8691.2
2004 4.8691.3
2005 4.8691.4
2006 4.8691.5
2007 4.8691.6
2008 4.8691.7
2009 4.8691.8
2010 4.8691.9
10 rows
selected
All the rows from 2001 to 2010 are present it the same block (8691), in
the next step a transaction updated a single row in our new table
demo@PDB1>
update stage_qh_rll set object_name = 'xx' where id = 2001;
1 row updated.
With “row lock enabled” on this table, it won’t lock the entire CU –
doing another transaction updating a different row with in the same CU doesnot
produce any kind of “dead lock” issue like above.
demo@PDB1>
declare
2
pragma autonomous_transaction;
3
begin
4
update stage_qh_rll set owner ='xx'
5
where id = 2002;
6
rollback;
7 end;
8 /
PL/SQL
procedure successfully completed.
No waits and no locks in this case.
