1 package dbms_compression authid current_user is
2
3 COMP_NOCOMPRESS CONSTANT NUMBER := 1;
4 COMP_ADVANCED CONSTANT NUMBER := 2;
5 COMP_QUERY_HIGH CONSTANT NUMBER := 4;
6 COMP_QUERY_LOW CONSTANT NUMBER := 8;
7 COMP_ARCHIVE_HIGH CONSTANT NUMBER := 16;
8 COMP_ARCHIVE_LOW CONSTANT NUMBER := 32;
9 COMP_BLOCK CONSTANT NUMBER := 64;
10 COMP_LOB_HIGH CONSTANT NUMBER := 128;
11 COMP_LOB_MEDIUM CONSTANT NUMBER := 256;
12 COMP_LOB_LOW CONSTANT NUMBER := 512;
13 COMP_INDEX_ADVANCED_HIGH CONSTANT NUMBER := 1024;
14 COMP_INDEX_ADVANCED_LOW CONSTANT NUMBER := 2048;
15 COMP_BASIC CONSTANT NUMBER := 4096;
16 COMP_INMEMORY_NOCOMPRESS CONSTANT NUMBER := 8192;
17 COMP_INMEMORY_DML CONSTANT NUMBER := 16384;
18 COMP_INMEMORY_QUERY_LOW CONSTANT NUMBER := 32768;
19 COMP_INMEMORY_QUERY_HIGH CONSTANT NUMBER := 65536;
20 COMP_INMEMORY_CAPACITY_LOW CONSTANT NUMBER := 131072;
21 COMP_INMEMORY_CAPACITY_HIGH CONSTANT NUMBER := 262144;
22
23 COMP_RATIO_MINROWS CONSTANT NUMBER := 1000000;
24 COMP_RATIO_ALLROWS CONSTANT NUMBER := -1;
25 COMP_RATIO_LOB_MINROWS CONSTANT NUMBER := 1000;
26 COMP_RATIO_LOB_MAXROWS CONSTANT NUMBER := 5000;
27 COMP_RATIO_INDEX_MINROWS CONSTANT NUMBER := 100000;
28
29 OBJTYPE_TABLE CONSTANT PLS_INTEGER := 1;
30 OBJTYPE_INDEX CONSTANT PLS_INTEGER := 2;
31 OBJTYPE_PART CONSTANT NUMBER := 2;
32 OBJTYPE_SUBPART CONSTANT NUMBER := 3;
33
34 --Record for calculating an individual index cr on a table
35 type compRec is record(
36 ownname varchar2(255),
37 objname varchar2(255),
38 blkcnt_cmp PLS_INTEGER,
39 blkcnt_uncmp PLS_INTEGER,
40 row_cmp PLS_INTEGER,
41 row_uncmp PLS_INTEGER,
42 cmp_ratio NUMBER,
43 objtype PLS_INTEGER
44 );
45
46 type compRecList is table of compRec;
47
48 --Get compression ratio for an object: table/index. Default is table.
49 PROCEDURE get_compression_ratio(
50 scratchtbsname IN varchar2,
51 ownname IN varchar2,
52 objname IN varchar2,
53 subobjname IN varchar2,
54 comptype IN number,
55 blkcnt_cmp OUT PLS_INTEGER,
56 blkcnt_uncmp OUT PLS_INTEGER,
57 row_cmp OUT PLS_INTEGER,
58 row_uncmp OUT PLS_INTEGER,
59 cmp_ratio OUT NUMBER,
60 comptype_str OUT varchar2,
61 subset_numrows IN number DEFAULT COMP_RATIO_MINROWS,
62 objtype IN PLS_INTEGER DEFAULT OBJTYPE_TABLE
63 );
64
65 --Get compression ratio for lobs
66 PROCEDURE get_compression_ratio(
67 scratchtbsname IN varchar2,
68 tabowner IN varchar2,
69 tabname IN varchar2,
70 lobname IN varchar2,
71 partname IN varchar2,
72 comptype IN number,
73 blkcnt_cmp OUT PLS_INTEGER,
74 blkcnt_uncmp OUT PLS_INTEGER,
75 lobcnt OUT PLS_INTEGER,
76 cmp_ratio OUT NUMBER,
77 comptype_str OUT varchar2,
78 subset_numrows IN number DEFAULT COMP_RATIO_LOB_MAXROWS
79 );
80
81
82 --Get compression ratio for all indexes on a table. The compression
83 --ratios will be returned as a collection.
84 PROCEDURE get_compression_ratio(
85 scratchtbsname IN varchar2,
86 ownname IN varchar2,
87 tabname IN varchar2,
88 comptype IN number,
89 index_cr OUT compRecList,
90 comptype_str OUT varchar2,
91 subset_numrows IN number DEFAULT COMP_RATIO_INDEX_MINROWS
92 );
93
94 function get_compression_type (
95 ownname IN varchar2,
96 tabname IN varchar2,
97 row_id IN rowid,
98 subobjname IN varchar2 DEFAULT NULL
99 )
100 return number;
101
102 PROCEDURE dump_compression_map (
103 ownname IN varchar2,
104 tabname IN varchar2,
105 comptype IN number
106 );
107
108 /* SYNTAX:
109 call incremental_compress(<Owner name>, <Table name>, <Partition Name>, <Column Name>, [Dump], [Auto Compress], [Where Clause]);
110 <Owner Name>: Name of the owner of the table
111 <Table Name>: Name of table under consideration
112 <Partition Name>: If the table is partitioned (or sub-partitioned), specify the specific partition (or sub-partition)
113 name here. If the table is sub-partitioned, then each sub-partition will have to be compressed
114 separately. For tables that are not partitioned, this parameter is ignored, so a '' can be specified.
115 NOTE: Each partition or subpartition will have to be compressed separately. It is erroneous to
116 specify a partition name for a table with sub-partitions. The specific sub-partition name will
117 have to be specified.
118 <Column Name>: This column can be any column name in the table. An update statement of the type
119 'update table_name set column_name = column_name' will be run, so choosing any column name should
120 not make any functional difference.
121 [Dump]: An optional parameter that dumps out the space saved in each block into the trace files. It is turned
122 OFF by default (set to 0). It is advised not to turn this feature on for large tables or partitions
123 because of excessive logging.
124 [Auto Compress]: If table is not created compressed or compression was never used on this table/partition, setting this to 1 will
125 force an alter table to switch on and then switch off compression on this table/partition.
126 [Where Clause]: An optional where clause supplied to the update statement. */
127
128 PROCEDURE incremental_compress (
129 ownname IN dba_objects.owner%type,
130 tabname IN dba_objects.object_name%type,
131 partname IN dba_objects.subobject_name%type,
132 colname IN varchar2,
133 dump_on IN number default 0,
134 autocompress_on IN number default 0,
135 where_clause IN varchar2 default '');
136
137 PROCEDURE clear_analysis (
138 ownname IN varchar2,
139 tabname IN varchar2,
140 comptype IN number default 0);
141
142
143 end dbms_compression;
[Home] [Help]