Database Reorganization and Index Compression

[Former Member]

Hi. I am planning a database reorganization and would like to know if it is possible to perform index compression at the same time, using BRTools/BRSpace. I'd like to kill two birds with one stone, rather than performing the operations separately. Since during the reorganization indexes will be rebuild anyway, it would be nice to specify at reorg time, for the indexes to be compressed. This way, the indexing work would only occur one time.

Any help / thoughts would be appreciated.

James

[Former Member · Accepted solution]

Yep, it is possible. You can to specify first for the create ddl option and manually insert the compression statement.

For example you start a table reorganisation:

+Options for reorganization of tables: SAPR3.T000 (degree 1)+

+1 ~ New destination tablespace (newts) ........ []+

+2 ~ Separate index tablespace (indts) ......... []+

+3 - Parallel threads (parallel) ............... [1]+

+4 ~ Table/index parallel degree (degree) ...... []+

5 - Create DDL statements (ddl) ............... [first]

6 ~ Category of initial extent size (initial) . []

7 ~ Sort by fields of index (sortind) ......... []

8 - Table reorganization mode (mode) .......... [online]

The reorg stops after the ddl statements where created:

BR0280I BRSPACE time stamp: 2008-10-31 13.48.39

BR1115I Number of tables DDL statements were created successfully for: 1

BR0280I BRSPACE time stamp: 2008-10-31 13.48.39

BR1148I You can check/change the DDL statements in file /oracle/SID/sapreorg/sdzdriov/ddl.sql now

BR0280I BRSPACE time stamp: 2008-10-31 13.48.39

BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRSPACE:

Now you can edit the ddl.sql in the sapreorg folder. Then go on with the reorg.

Regards Michael

[stefan_koehler · Accepted solution]

Hello Michael,

> You can to specify first for the create ddl option and manually insert the compression statement.

That is very interesting, because of until now i thought that the BRTools are using the whole procedures of DBMS_REDEFINITION. I have traced the BRTool calls and you are right.

> 'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPSR3"', ORIG_TABLE => '"Z_TEST"', INT_TABLE => '"Z_TEST#$"', OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID); END;'

>

> 'CREATE INDEX "SAPSR3"."I_TEST#$" ON "SAPSR3"."Z_TEST#$" ("A", "B")

> PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPRESS 1

> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

> TABLESPACE "PSAPSR3USR"

>

> 'BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE (UNAME => '"SAPSR3"', ORIG_TABLE => '"Z_TEST"', INT_TABLE => '"Z_TEST#$"'); END;'

So SAP just reorganize the table with DBMS_REDEFINITION and then creates the indexes manually with the DDL script.

I thought that they are using the procedure COPY_TABLE_DEPENDENTS with the option " copy_indexes => dbms_redefinition.cons_orig_params".

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_redefi.htm#i999656

So you can reorganize the table and compress the indexes with additional modifications in one step.

Regards

Stefan

[Former Member · Accepted solution]

Hi. After posting my question, I saw in not 646681 and was thinking that the ddl.sql route was my only option at this point to compress indexes during a table reorg. Thank you for confirming this. I will follow upon this and try it out in our sandbox environment.

Have you actually used this method? I am wondering if it is as simple as restarting / continuing the reorg after the edits have been made to the ddl.sql script. This of course is my hope, but if you already know, could you shed some more light on it, please? Thanks.

James

[Former Member · Accepted solution]

Thank you for following up on the ddl.sql suggestion with more information. This may come in handy should I have to troubleshoot any problems.

[Former Member · Accepted solution]

Have you actually used this method?

Yes, i did, just to mention, this method was recommended by SAP in a OSS message i had. Though it was not the index compression then. We were having a problem with early versions of BR*Space which added all 'NOT NULL' constraints after the create table statement (and the data load). This led to very long runtimes with large tables with lots of columns. But they changed this in the recent versions.

However just give it a try on the test box and post here if you have still issues.

Regards Michael

[Former Member · Accepted solution]

Thank you. I will followup.

[Former Member · Accepted solution]

Hi. I said I would followup on this, so here it is. This was pretty easy and worked as expected.

I ran brtools using <sid>adm account in one telnet session, specifying "first" for the ddl.sql option as noted earlier in this discussion. This spun through the tables, producing the ddl.sql file and then prompted to continue or stop. I let it sit there.....

Meanwhile, in another telnet session, signed on as ora<sid> account, I backed up and then modified the ddl.sql file to include "COMPRESS" just after the "TABLESPACE" clause at the end of each create [unique] index statement I desired compression for.

Then I went back to the "brtools" session and spedified continue.

A query against dba_indexes verified that I was indeed getting compressed indexes instead of non-compressed ones. See the "compression" column of this view. DISABLED means non-compressed and ENABLED means compressed.

The above method is working for me. I do not know what would happen precisely if brtools processing was stopped or somehow aborted prior to continuing. I hope I don't learn either. lol.

Thanks to you and others for all your help!

James

[Former Member · Accepted solution]

As you can see BRSPACE online reorganizations are very powerful. I have also used them for activating partitioning or switching to an index organized table. In the near future I will also check to what extent table conversions (additional columns, dropped columns, modified columns) can take advantage of (parallelized) BRSPACE online reorganizations.

Regards

Martin

[former_member204746]

to compress index while reorging them in another tablespace:

alter index "MSEG~0" rebuild online compress 3 parallel tablespace PSAPMSEGI nologging;

but this does not take care of tables.

[Former Member]

Thank you Eric. Your answer highlights my problem. I am aware that I can perform the action before or after reorg (i.e. of the table), but I want to do it during the reorg.

[stefan_koehler]

Hello James,

> I am planning a database reorganization and would like to know if it is possible to perform index compression at the same time, using BRTools/BRSpace.

No, the BR*Tools are not supporting index compression until yet.

So you have to do the reorganisation with the BRTools and after that the compression via SQLPLUS like described in sapnote #1109743.

But if you have compressed the indexes before the reorg with the BR*Tools.. the compression will be kept.

Regards

Stefan