Databases Reference
In-Depth Information
We will start with a test table, compressed for
QUERY HIGH
. The sections in this recipe will perform
UPDATE
statements on this table and use it to demonstrate features of DML on HCC tables:
SQL> create table d14.myobj_dmltest
2 tablespace tbs_queryhigh compress for query high
3 as select * from dba_objects;
Table created.
SQL>
Determining the Compression Type of an Updated Row
First, we will use
DBMS_COMPRESSION.GET_COMPRESSION_TYPE
to identify the compression type for a single row in our
test table. Listing 16-8 can be used for this purpose.
Listing 16-8.
lst16-08-comptype.sql
SQL> select '&&owner' owner, '&&table_name' tabname, rowid myrowid,
decode(dbms_compression.get_compression_type('&&owner','&&table_name',rowid),
1,'No Compression', 2,'Basic/OLTP', 4,'HCC Query High',8,'HCC Query Low',
16,'HCC Archive High', 32,'HCC Archive Low',64,'Block') comptype
from "&&owner"."&&table_name"
where &&predicate
/
Enter value for owner: D14
Enter value for table_name: MYOBJ_DMLTEST
Enter value for predicate: OBJECT_NAME='SOURCE$'
Owner Table RowId CompType
--------------- ---------------------- -------------------- --------------------
D14 MYOBJ_DMLTEST AAAG2pAAAAAAACDADe HCC Query High
Now we will update the row and re-run the query in Listing 16-8:
SQL> update d14.myobj_dmltest
2 set object_name=object_name where object_name='SOURCE$';
1 row updated.
SQL> commit;
Commit complete.
SQL> @lst16-08-comptype.sql
... Output omitted for brevity, see above
Enter value for owner: D14
Enter value for table_name: MYOBJ_DMLTEST
Enter value for predicate: OBJECT_NAME='SOURCE$'
Owner Table RowId CompType
--------------- ---------------------- -------------------- --------------------
D14 MYOBJ_DMLTEST AAAG2pAAAAAAACUAAA No Compression
Note that our
ROWID
changed from
AAAG2pAAAAAAACDADe
to
AAAG2pAAAAAAACUAAA
; this is an indication that row
migration has occurred. Additionally, the compression type on the new row is “1”, or uncompressed. This happens
Search WWH ::
Custom Search