It’s like a murder mystery…

I’ve been doing some OLTP compression for a client, because they have too much data – and JDE loves whitespace.

So I whoop up some great statements.

create table TESTDTA.F42199SRM as select * from TESTDTA.F42199 where 1=0;

alter table TESTDTA.F42199SRM compress for OLTP;

alter table TESTDTA.F42199SRM move tablespace TESTDTAT;

alter table TESTDTA.f42199SRM NOLOGGING;

insert into TESTDTA.F42199SRM select * from TESTDTA.F42199;

All good, let’s check the size of the tables – still 320 GB…

How’s this for a table that works (F0911)

F0911 went from 381GB to 21GB… WHAT!!!! I’ll take a CPU hit any day of the week to be able to load the entire F0911 in 21GB! The table above shows ORADTA uncompressed and CRPDTA and TESTDTA compressed.

Hmm, so it takes an hour to run… I get no errors… But my tables is still 231GB… Something is wrong. then I remember reading something about not working if the table has more than 256 columns and I see that F42199 has 266… Doh!!!

What am I going to do?

It seems simple at first, create an updateable view, nice.. works perfectly expect – when trying to insert the data.

I create a couple of smaller tables and then define the view to be a select over the top of them. Then insert all of the data and I’m done!

CREATE TABLE "TESTDTA"."F42199_T1"

( "SLKCOO" NCHAR(5) NOT NULL ,

"SLDOCO" NUMBER NOT NULL ,

"SLDCTO" NCHAR(2) NOT NULL ,

"SLLNID" NUMBER NOT NULL ,

"SLSFXO" NCHAR(3),

"SLMCU" NCHAR(12),

"SLCO" NCHAR(5),

"SLOKCO" NCHAR(5),

"SLOORN" NCHAR(8),

"SLOCTO" NCHAR(2),

"SLOGNO" NUMBER,

"SLRKCO" NCHAR(5),

"SLRORN" NCHAR(8),

"SLRCTO" NCHAR(2),

"SLRLLN" NUMBER,

"SLDMCT" NCHAR(12),

"SLDMCS" NUMBER,

"SLAN8" NUMBER,

"SLSHAN" NUMBER,

"SLPA8" NUMBER,

"SLDRQJ" NUMBER(6,0),

"SLTRDJ" NUMBER(6,0),

"SLPDDJ" NUMBER(6,0),

"SLADDJ" NUMBER(6,0),

"SLIVD" NUMBER(6,0),

"SLCNDJ" NUMBER(6,0),

"SLDGL" NUMBER(6,0),

"SLRSDJ" NUMBER(6,0),

"SLPEFJ" NUMBER(6,0),

"SLPPDJ" NUMBER(6,0),

"SLVR01" NCHAR(25),

"SLVR02" NCHAR(25),

"SLITM" NUMBER,

"SLLITM" NCHAR(25),

"SLAITM" NCHAR(25),

"SLLOCN" NCHAR(20),

"SLLOTN" NCHAR(30),

"SLFRGD" NCHAR(3),

"SLTHGD" NCHAR(3),

"SLFRMP" NUMBER,

"SLTHRP" NUMBER,

"SLEXDP" NUMBER,

"SLDSC1" NCHAR(30),

"SLDSC2" NCHAR(30),

"SLLNTY" NCHAR(2),

"SLNXTR" NCHAR(3),

"SLLTTR" NCHAR(3),

"SLEMCU" NCHAR(12),

"SLRLIT" NCHAR(8),

"SLKTLN" NUMBER,

"SLCPNT" NUMBER,

"SLRKIT" NUMBER,

"SLKTP" NUMBER,

"SLSRP1" NCHAR(3),

"SLSRP2" NCHAR(3),

"SLSRP3" NCHAR(3),

"SLSRP4" NCHAR(3),

"SLSRP5" NCHAR(3),

"SLPRP1" NCHAR(3),

"SLPRP2" NCHAR(3),

"SLPRP3" NCHAR(3),

"SLPRP4" NCHAR(3),

"SLPRP5" NCHAR(3),

"SLUOM" NCHAR(2),

"SLUORG" NUMBER,

"SLSOQS" NUMBER,

"SLSOBK" NUMBER,

"SLSOCN" NUMBER,

"SLSONE" NUMBER,

"SLUOPN" NUMBER,

"SLQTYT" NUMBER,

"SLQRLV" NUMBER,

"SLCOMM" NCHAR(1),

"SLOTQY" NCHAR(1),

"SLUPRC" NUMBER,

"SLAEXP" NUMBER,

"SLAOPN" NUMBER,

"SLPROV" NCHAR(1),

"SLTPC" NCHAR(1),

"SLAPUM" NCHAR(2),

"SLLPRC" NUMBER,

"SLUNCS" NUMBER,

"SLECST" NUMBER,

"SLCSTO" NCHAR(1),

"SLTCST" NUMBER,

"SLINMG" NCHAR(10),

"SLPTC" NCHAR(3),

"SLRYIN" NCHAR(1),

"SLDTBS" NCHAR(1),

"SLTRDC" NUMBER,

"SLFUN2" NUMBER,

"SLASN" NCHAR(8),

"SLPRGR" NCHAR(8),

"SLCLVL" NCHAR(3),

"SLCADC" NUMBER,

"SLKCO" NCHAR(5),

"SLDOC" NUMBER,

"SLDCT" NCHAR(2),

"SLODOC" NUMBER,

"SLODCT" NCHAR(2),

"SLOKC" NCHAR(5),

"SLPSN" NUMBER,

"SLDELN" NUMBER,

"SLTAX1" NCHAR(1),

"SLTXA1" NCHAR(10),

"SLEXR1" NCHAR(2),

"SLATXT" NCHAR(1),

"SLPRIO" NCHAR(1),

"SLRESL" NCHAR(1),

"SLBACK" NCHAR(1),

"SLSBAL" NCHAR(1),

"SLAPTS" NCHAR(1),

"SLLOB" NCHAR(3),

"SLEUSE" NCHAR(3),

"SLDTYS" NCHAR(2),

"SLNTR" NCHAR(2),

"SLVEND" NUMBER,

"SLCARS" NUMBER,

"SLMOT" NCHAR(3),

"SLROUT" NCHAR(3),

"SLSTOP" NCHAR(3),

"SLZON" NCHAR(3),

"SLCNID" NCHAR(20),

"SLFRTH" NCHAR(3),

"SLSHCM" NCHAR(3),

"SLSHCN" NCHAR(3),

"SLSERN" NCHAR(30),

"SLUOM1" NCHAR(2),

"SLPQOR" NUMBER,

"SLUOM2" NCHAR(2),

"SLSQOR" NUMBER,

"SLUOM4" NCHAR(2),

"SLITWT" NUMBER,

"SLWTUM" NCHAR(2),

"SLITVL" NUMBER,

"SLVLUM" NCHAR(2),

"SLRPRC" NCHAR(8),

"SLORPR" NCHAR(8),

"SLORP" NCHAR(1),

"SLCMGP" NCHAR(2),

"SLGLC" NCHAR(4),

"SLCTRY" NUMBER,

"SLFY" NUMBER,

"SLSO01" NCHAR(1),

"SLSO02" NCHAR(1),

"SLSO03" NCHAR(1),

"SLSO04" NCHAR(1),

"SLSO05" NCHAR(1),

"SLSO06" NCHAR(1),

"SLSO07" NCHAR(1),

"SLSO08" NCHAR(1),

"SLSO09" NCHAR(1),

"SLSO10" NCHAR(1),

"SLSO11" NCHAR(1),

"SLSO12" NCHAR(1),

"SLSO13" NCHAR(1),

"SLSO14" NCHAR(1),

"SLSO15" NCHAR(1),

"SLACOM" NCHAR(1),

"SLCMCG" NCHAR(8),

"SLRCD" NCHAR(3),

"SLGRWT" NUMBER,

"SLGWUM" NCHAR(2),

"SLSBL" NCHAR(8),

"SLSBLT" NCHAR(1),

"SLLCOD" NCHAR(2),

"SLUPC1" NCHAR(2),

"SLUPC2" NCHAR(2),

"SLUPC3" NCHAR(2),

"SLSWMS" NCHAR(1),

"SLUNCD" NCHAR(1),

"SLCRMD" NCHAR(1),

"SLCRCD" NCHAR(3),

"SLCRR" NUMBER,

"SLFPRC" NUMBER,

"SLFUP" NUMBER,

"SLFEA" NUMBER,

"SLFUC" NUMBER,

"SLFEC" NUMBER,

"SLURCD" NCHAR(2),

"SLURDT" NUMBER(6,0),

"SLURAT" NUMBER,

"SLURAB" NUMBER,

"SLURRF" NCHAR(15),

"SLTORG" NCHAR(10),

"SLUSER" NCHAR(10),

"SLPID" NCHAR(10),

"SLJOBN" NCHAR(10),

"SLUPMJ" NUMBER(6,0) NOT NULL ENABLE,

"SLTDAY" NUMBER NOT NULL ENABLE,

"SLSO16" NCHAR(1),

"SLSO17" NCHAR(1),

"SLSO18" NCHAR(1),

"SLSO19" NCHAR(1),

"SLSO20" NCHAR(1),

"SLIR01" NCHAR(30),

"SLIR02" NCHAR(30),

"SLIR03" NCHAR(30),

"SLIR04" NCHAR(30),

"SLIR05" NCHAR(30),

"SLSOOR" NUMBER(15,0),

"SLVR03" NCHAR(25),

"SLDEID" NUMBER,

"SLPSIG" NCHAR(30),

"SLRLNU" NCHAR(10),

"SLPMDT" NUMBER,

"SLRLTM" NUMBER,

"SLRLDJ" NUMBER(6,0),

"SLDRQT" NUMBER,

"SLADTM" NUMBER,

"SLOPTT" NUMBER,

"SLPDTT" NUMBER,

"SLPSTM" NUMBER,

"SLXDCK" NCHAR(1)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

COMPRESS FOR OLTP NOLOGGING

STORAGE(INITIAL 4294967296 NEXT 209715200 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SSDMAX"

PARALLEL ;

CREATE TABLE "TESTDTA"."F42199_T2"

( "SLKCOO" NCHAR(5) NOT NULL ,

"SLDOCO" NUMBER NOT NULL ,

"SLDCTO" NCHAR(2) NOT NULL ,

"SLLNID" NUMBER NOT NULL ,

"SLUPMJ" NUMBER(6,0) NOT NULL ENABLE,

"SLTDAY" NUMBER NOT NULL ENABLE,

"SLXPTY" NUMBER,

"SLDUAL" NCHAR(1),

"SLBSC" NCHAR(10),

"SLCBSC" NCHAR(10),

"SLCORD" NUMBER,

"SLDVAN" NUMBER,

"SLPEND" NCHAR(1),

"SLRFRV" NCHAR(3),

"SLMCLN" NUMBER,

"SLSHPN" NUMBER,

"SLRSDT" NUMBER,

"SLPRJM" NUMBER,

"SLOSEQ" NUMBER,

"SLMERL" NCHAR(3),

"SLHOLD" NCHAR(2),

"SLHDBU" NCHAR(12),

"SLDMBU" NCHAR(12),

"SLBCRC" NCHAR(3),

"SLODLN" NUMBER,

"SLOPDJ" NUMBER(6,0),

"SLXKCO" NCHAR(5),

"SLXORN" NUMBER,

"SLXCTO" NCHAR(2),

"SLXLLN" NUMBER,

"SLXSFX" NCHAR(3),

"SLPOE" NCHAR(6),

"SLPMTO" NCHAR(1),

"SLANBY" NUMBER,

"SLPMTN" NCHAR(12),

"SLNUMB" NUMBER,

"SLAAID" NUMBER,

"SLPRAN8" NUMBER,

"SLSPATTN" NCHAR(50),

"SLPRCIDLN" NUMBER,

"SLCCIDLN" NUMBER,

"SLSHCCIDLN" NUMBER,

"SLOPPID" NUMBER,

"SLOSTP" NCHAR(3),

"SLUKID" NUMBER,

"SLCATNM" NCHAR(30),

"SLALLOC" NCHAR(1),

"SLFULPID" NUMBER(15,0),

"SLALLSTS" NCHAR(30),

"SLOSCORE" NUMBER,

"SLOSCOREO" NCHAR(1),

"SLCMCO" NCHAR(5),

"SLKITID" NUMBER,

"SLKITAMTDOM" NUMBER,

"SLKITAMTFOR" NUMBER,

"SLKITDIRTY" NCHAR(1),

"SLOCITT" NCHAR(1),

"SLOCCARDNO" NUMBER

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

COMPRESS FOR OLTP NOLOGGING

STORAGE(INITIAL 4294967296 NEXT 209715200 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "SSDMAX"

PARALLEL ;

CREATE VIEW TESTDTA.F42199_T3 as

select t1.SLKCOO ,

t1.SLDOCO ,

t1.SLDCTO ,

t1.SLLNID ,

t1.SLSFXO ,

t1.SLMCU ,

t1.SLCO ,

t1.SLOKCO ,

t1.SLOORN ,

t1.SLOCTO ,

t1.SLOGNO ,

t1.SLRKCO ,

t1.SLRORN ,

t1.SLRCTO ,

t1.SLRLLN ,

t1.SLDMCT ,

t1.SLDMCS ,

t1.SLAN8 ,

t1.SLSHAN ,

t1.SLPA8 ,

t1.SLDRQJ ,

t1.SLTRDJ ,

t1.SLPDDJ ,

t1.SLADDJ ,

t1.SLIVD ,

t1.SLCNDJ ,

t1.SLDGL ,

t1.SLRSDJ ,

t1.SLPEFJ ,

t1.SLPPDJ ,

t1.SLVR01 ,

t1.SLVR02 ,

t1.SLITM ,

t1.SLLITM ,

t1.SLAITM ,

t1.SLLOCN ,

t1.SLLOTN ,

t1.SLFRGD ,

t1.SLTHGD ,

t1.SLFRMP ,

t1.SLTHRP ,

t1.SLEXDP ,

t1.SLDSC1 ,

t1.SLDSC2 ,

t1.SLLNTY ,

t1.SLNXTR ,

t1.SLLTTR ,

t1.SLEMCU ,

t1.SLRLIT ,

t1.SLKTLN ,

t1.SLCPNT ,

t1.SLRKIT ,

t1.SLKTP ,

t1.SLSRP1 ,

t1.SLSRP2 ,

t1.SLSRP3 ,

t1.SLSRP4 ,

t1.SLSRP5 ,

t1.SLPRP1 ,

t1.SLPRP2 ,

t1.SLPRP3 ,

t1.SLPRP4 ,

t1.SLPRP5 ,

t1.SLUOM ,

t1.SLUORG ,

t1.SLSOQS ,

t1.SLSOBK ,

t1.SLSOCN ,

t1.SLSONE ,

t1.SLUOPN ,

t1.SLQTYT ,

t1.SLQRLV ,

t1.SLCOMM ,

t1.SLOTQY ,

t1.SLUPRC ,

t1.SLAEXP ,

t1.SLAOPN ,

t1.SLPROV ,

t1.SLTPC ,

t1.SLAPUM ,

t1.SLLPRC ,

t1.SLUNCS ,

t1.SLECST ,

t1.SLCSTO ,

t1.SLTCST ,

t1.SLINMG ,

t1.SLPTC ,

t1.SLRYIN ,

t1.SLDTBS ,

t1.SLTRDC ,

t1.SLFUN2 ,

t1.SLASN ,

t1.SLPRGR ,

t1.SLCLVL ,

t1.SLCADC ,

t1.SLKCO ,

t1.SLDOC ,

t1.SLDCT ,

t1.SLODOC ,

t1.SLODCT ,

t1.SLOKC ,

t1.SLPSN ,

t1.SLDELN ,

t1.SLTAX1 ,

t1.SLTXA1 ,

t1.SLEXR1 ,

t1.SLATXT ,

t1.SLPRIO ,

t1.SLRESL ,

t1.SLBACK ,

t1.SLSBAL ,

t1.SLAPTS ,

t1.SLLOB ,

t1.SLEUSE ,

t1.SLDTYS ,

t1.SLNTR ,

t1.SLVEND ,

t1.SLCARS ,

t1.SLMOT ,

t1.SLROUT ,

t1.SLSTOP ,

t1.SLZON ,

t1.SLCNID ,

t1.SLFRTH ,

t1.SLSHCM ,

t1.SLSHCN ,

t1.SLSERN ,

t1.SLUOM1 ,

t1.SLPQOR ,

t1.SLUOM2 ,

t1.SLSQOR ,

t1.SLUOM4 ,

t1.SLITWT ,

t1.SLWTUM ,

t1.SLITVL ,

t1.SLVLUM ,

t1.SLRPRC ,

t1.SLORPR ,

t1.SLORP ,

t1.SLCMGP ,

t1.SLGLC ,

t1.SLCTRY ,

t1.SLFY ,

t1.SLSO01 ,

t1.SLSO02 ,

t1.SLSO03 ,

t1.SLSO04 ,

t1.SLSO05 ,

t1.SLSO06 ,

t1.SLSO07 ,

t1.SLSO08 ,

t1.SLSO09 ,

t1.SLSO10 ,

t1.SLSO11 ,

t1.SLSO12 ,

t1.SLSO13 ,

t1.SLSO14 ,

t1.SLSO15 ,

t1.SLACOM ,

t1.SLCMCG ,

t1.SLRCD ,

t1.SLGRWT ,

t1.SLGWUM ,

t1.SLSBL ,

t1.SLSBLT ,

t1.SLLCOD ,

t1.SLUPC1 ,

t1.SLUPC2 ,

t1.SLUPC3 ,

t1.SLSWMS ,

t1.SLUNCD ,

t1.SLCRMD ,

t1.SLCRCD ,

t1.SLCRR ,

t1.SLFPRC ,

t1.SLFUP ,

t1.SLFEA ,

t1.SLFUC ,

t1.SLFEC ,

t1.SLURCD ,

t1.SLURDT ,

t1.SLURAT ,

t1.SLURAB ,

t1.SLURRF ,

t1.SLTORG ,

t1.SLUSER ,

t1.SLPID ,

t1.SLJOBN ,

t1.SLUPMJ ,

t1.SLTDAY ,

t1.SLSO16 ,

t1.SLSO17 ,

t1.SLSO18 ,

t1.SLSO19 ,

t1.SLSO20 ,

t1.SLIR01 ,

t1.SLIR02 ,

t1.SLIR03 ,

t1.SLIR04 ,

t1.SLIR05 ,

t1.SLSOOR ,

t1.SLVR03 ,

t1.SLDEID ,

t1.SLPSIG ,

t1.SLRLNU ,

t1.SLPMDT ,

t1.SLRLTM ,

t1.SLRLDJ ,

t1.SLDRQT ,

t1.SLADTM ,

t1.SLOPTT ,

t1.SLPDTT ,

t1.SLPSTM ,

t1.SLXDCK ,

t2.SLXPTY ,

t2.SLDUAL ,

t2.SLBSC ,

t2.SLCBSC ,

t2.SLCORD ,

t2.SLDVAN ,

t2.SLPEND ,

t2.SLRFRV ,

t2.SLMCLN ,

t2.SLSHPN ,

t2.SLRSDT ,

t2.SLPRJM ,

t2.SLOSEQ ,

t2.SLMERL ,

t2.SLHOLD ,

t2.SLHDBU ,

t2.SLDMBU ,

t2.SLBCRC ,

t2.SLODLN ,

t2.SLOPDJ ,

t2.SLXKCO ,

t2.SLXORN ,

t2.SLXCTO ,

t2.SLXLLN ,

t2.SLXSFX ,

t2.SLPOE ,

t2.SLPMTO ,

t2.SLANBY ,

t2.SLPMTN ,

t2.SLNUMB ,

t2.SLAAID ,

t2.SLPRAN8 ,

t2.SLSPATTN ,

t2.SLPRCIDLN ,

t2.SLCCIDLN ,

t2.SLSHCCIDLN ,

t2.SLOPPID ,

t2.SLOSTP ,

t2.SLUKID ,

t2.SLCATNM ,

t2.SLALLOC ,

t2.SLFULPID ,

t2.SLALLSTS ,

t2.SLOSCORE ,

t2.SLOSCOREO ,

t2.SLCMCO ,

t2.SLKITID ,

t2.SLKITAMTDOM ,

t2.SLKITAMTFOR ,

t2.SLKITDIRTY ,

t2.SLOCITT ,

t2.SLOCCARDNO

FROM TESTDTA.F42199_T1 T1, TESTDTA.F42199_T2 t2

WHERE t1.SLKCOO = t2.SLKCOO

AND t1.SLDOCO = t2.SLDOCO

AND t1.SLDCTO = t2.SLDCTO

AND t1.SLUPMJ = t2.SLUPMJ

AND t1.SLTDAY = t2.SLTDAY

AND t1.SLLNID = t2.SLLNID ;

create unique index TESTDTA.F42199_T1PK ON TESTDTA.f42199_T1 (SLKCOO,SLDOCO,SLDCTO,SLUPMJ,SLTDAY,SLLNID);

create unique index TESTDTA.F42199_T2PK ON TESTDTA.f42199_T2 (SLKCOO,SLDOCO,SLDCTO,SLUPMJ,SLTDAY,SLLNID);

The create my other table, but I get the following when I do an insert:

SQL Error: ORA-01776: cannot modify more than one base table through a join view

01776. 00000 - "cannot modify more than one base table through a join view"

*Cause: Columns belonging to more than one underlying table were either

inserted into or updated.

*Action: Phrase the statement as two or more separate statements.

I thought that I could try a trigger, but I don’t care about that anymore.

So – it’s time to get creative.

I’ve going to select 10 columns where the data is blank and drop them from the table. Create a new view over that table and select constants for the other values – JOB DONE!

Then I’ll compress the table and all will be good.

I hear you saying “You cannot do that, what if someone want’s to use one of those columns). I say, 320GB and 80 million rows cannot be wrong!

select count(1) from testdta.f42199 where SLRLTM>0;

select count(1) from testdta.f42199 where SLRLDJ>0 ;

select count(1) from testdta.f42199 where SLDRQT>0 ;

select count(1) from testdta.f42199 where SLADTM>0 ;

select count(1) from testdta.f42199 where SLOPTT>0 ;

select count(1) from testdta.f42199 where SLPDTT>0 ;

select count(1) from testdta.f42199 where SLPSTM>0 ;

select count(1) from testdta.f42199 where SLXPTY>0 ;

select count(1) from testdta.f42199 where SLDEID>0 ;

select count(1) from testdta.f42199 where SLSOOR >0 ;

So I use the above to fine some fields that are blank for all 80 million records and will academically remove them from the table.

Create a view that just selects ‘’ or 0 and job done!