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!
No comments:
Post a Comment