Oracle. Ещё один способ партиционирования больших и нагруженных таблиц

Ладно-ладно, я пошутила, 300 Гб вполне достаточно, чтобы прочувствовать…

CREATE TABLE Scott.TEST_EXCHANGE
(
  ISN                NUMBER                     NOT NULL,
  HISTESTIMATIONISN  NUMBER                     NOT NULL,
  SOURCECODE         CHAR(1 BYTE)               NOT NULL,
  SOURCEISN          NUMBER                     NOT NULL,
  VALUECODE          CHAR(1 BYTE)               NOT NULL,
  VALUENAME          VARCHAR2(255 BYTE),
  DATAVALUE          CLOB,
  CREATED            DATE                       NOT NULL,
  CREATEDBY          NUMBER                     NOT NULL,
  UPDATED            DATE                       NOT NULL,
  UPDATEDBY          NUMBER                     NOT NULL
)
LOB (DATAVALUE) STORE AS BASICFILE (
  TABLESPACE TDATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  RETENTION
  )
  tablespace TDATA		
;

alter session enable parallel query;
alter session enable parallel DML;

declare
    V_size number := 0.300; -- объем в Тб
    V_rows number := 5000000; /* количество записей, с которого 
                      перепроверяем полученный объем, чтобы не 
                      опрашивать на каждом проходе и не считать лишнего*/
    v_pks   number := 100000; -- размер пачки    
    i number := 0;
    s number;
    v_sql varchar2(32000);
begin
  /*Сгенерируем нужный(V_size) объем данных в табличку, надеюсь ваша 
  база не совсем пустая, так как данные я планирую позаимствовать из 
  нее что бы не перенапрягать генераторы случайных значений*/
  loop
    for r in (
    /* Для большей наглядности воспользуемся вложенными запросами, а то 
      много скобок и не так красиво.
      Далее в этом запросе читать комментарии снизу вверх: */
        Select  owner, table_name, column_name, num_rows
      				, nvl(varchar2_col,'null') varchar2_col, n 
          		, RPAD (ncols, LENGTH (ncols) + (3 - n) * 2, ',0') ncols 
          --если колонок меньше 3 достраиваем нулями. 
          From (Select  owner, table_name, column_name, ' '||ncols ncols
                      , num_rows, varchar2_col
                      , nvl((LENGTH(ncols) 
                             - LENGTH(TRANSLATE (ncols, 'x,', 'x')))/2
                           ,0) n /* считаем сколько у нас в этой таблице 
                     			         вышло пригодных для инсерта колонок*/
                  From (Select l.owner, l.table_name, l.column_name
                             , t.num_rows, varchar2_col
                             , NVL (
                                SUBSTR (l.ncols
                                        ,1
                                        ,INSTR (l.ncols, ',', 1, 3*2+1) - 1)
                                   , l.ncols) ncols
                                    /* отбрасываем в списке колонок лишнее, нам 
                              			 нужно только 3 числа, первичный 
                              			 ключ мы присвоим уникальный, 
                                     а ключ будущего партиционирования 
                              			HISTESTIMATIONISN будем генерить */
                          From (Select l.owner
                                , l.table_name
                                , l.column_name
                                , max(case when l.column_name = c.column_name 
                                      then c.data_type end) lob_type
                                , max(case when c.data_type = 'VARCHAR2' 
                                      then c.column_name end) varchar2_col
                                , LISTAGG (Case When c.data_type = 'NUMBER' 
                                           Then ',nvl(' ||c.column_name||',0)' End) 
                                --так как  not null полей может не хватить прицепим nvl
                                Within Group (Order By c.nullable Desc, c.column_id) 
                                ncols
                                -- собираем колонки с типом NUMBER
                                From  dba_lobs l 
                                Join all_tab_columns c 
                                On c.owner = l.owner And c.table_name = l.table_name
                                /*воспользуемся таблицами с лобами и вытащим из них 
                                лобы и числа, что бы не дергать dbms_random 
                                и не кипятить проц*/
                                Group By  l.owner, l.table_name, l.column_name) l
                        join dba_tables t 
                          on t.table_name = l.table_name and t.owner = l.owner and num_rows>0
                        join all_tab_privs p 
                        on p.table_name = t.table_name and p.table_schema = t.owner 
                        --что бы на наступить на гранты 
                        where lob_type='CLOB'))
                order by case when num_rows > v_pks then 1 else 0 end desc
    ) loop
		  v_sql :=
       'INSERT /*+ APPEND PARALLEL(16) */ 
       INTO Scott.TEST_EXCHANGE (ISN, HISTESTIMATIONISN, CREATEDBY, UPDATEDBY, 
       SOURCEISN, DATAVALUE, VALUECODE, SOURCECODE, VALUENAME, CREATED, UPDATED)'||         chr(10)|| 
        /* ключики себе генерим для ПК последовательно, для будущего ключа 
        партиционирования c разбросом ~50 значений по пачке d 100 000 (v_pks)*/
        'SELECT rownum+:i, floor((rownum+:i)/(:v_pks/50)) '
        					||r.ncols||' ,'||r.column_name||
        --чтобы не писать много двойных кавычек используем конструкцию q'{...}'
        q'{,decode(mod(rownum,10),1,'V',2,'R',3,'A',4,'D',5,'P',6,'B',7,'T',8,'W',9,'E','S'),'N', substr(}'
         ||r.varchar2_col||',1,200) ,sysdate,sysdate
         FROM '||r.owner||'.'||r.table_name ||'
         SAMPLE BLOCK ('||case when ceil(100*v_pks/r.num_rows)> 100 
                          then 99 else ceil(100*v_pks/r.num_rows) end||') 
                          --'||r.num_rows||'
         WHERE ROWNUM <=:v_pks';
        execute immediate v_sql using i,i,v_pks,v_pks;
        i:=i+sql%rowcount;
        commit;
        if i>= V_rows then /*с этого количества строк начинаем проверять 
														размерчик*/
            select sum(bytes)/power(1024,4) into s
              from (select sum(bytes) bytes
                      from dba_segments 
                    where owner = 'Scott' 
                      and segment_name = 'TEST_EXCHANGE'
                    union
                    select sum(bytes)
                      from dba_lobs l 
                      Join dba_segments s 
                        on l.OWNER = s.OWNER 
                       and l.SEGMENT_NAME = s.SEGMENT_NAME
                     where  l.owner = 'Scott' 
                       and l.TABLE_NAME = 'TEST_EXCHANGE');
            exit when s >=v_size;
        end if;
    end loop;
        --если табличек с лобами не достаточно, придется по ним еще раз пробежаться
    select sum(bytes)/power(1024,4) into s
      from (select sum(bytes) bytes
              from dba_segments 
             where owner = 'Scott' and segment_name = 'TEST_EXCHANGE'
             union
            select sum(bytes)
              from dba_lobs l 
              Join dba_segments s 
                on l.OWNER = s.OWNER and l.SEGMENT_NAME = s.SEGMENT_NAME
             where  l.owner = 'Scott' and l.TABLE_NAME = 'TEST_EXCHANGE');
    exit when s >=v_size;
  end loop;
exception when others then 
  DBMS_OUTPUT.put_line (v_sql); 
  raise;
end;

CREATE UNIQUE INDEX Scott.PK_TEST_EXCHANGE ON Scott.TEST_EXCHANGE
(ISN) parallel 32;
  
Alter index Scott.PK_TEST_EXCHANGE  noparallel;


CREATE INDEX Scott.X_TEST_EXCHANGE_HISTORY ON Scott.TEST_EXCHANGE
(CREATED, SOURCECODE, VALUECODE, VALUENAME) parallel 32;
  
Alter index Scott.X_TEST_EXCHANGE_HISTORY  noparallel;

ALTER TABLE Scott.TEST_EXCHANGE ADD (
  CONSTRAINT CHK_TEST_EXCHANGE_VALUECODE
  CHECK (ValueCode in ( 'V', 'R', 'A', 'D', 'P', 'B', 'T', 'W', 'E','S','I','H','C'))
  ENABLE NOVALIDATE
,  CONSTRAINT PK_TEST_EXCHANGE
  PRIMARY KEY
  (ISN)
  USING INDEX Scott.PK_TEST_EXCHANGE
  ENABLE VALIDATE);