W tym kursie oracle sql opiszę przykład działania tabeli partycjonowanej. Do pokazania działania partycjonowania użyję tabeli partycjonowanej po zakresie (ang. Range Partition). Pokażę jak działa partycjonowanie oraz jakie niesie ze sobą konsekwencje na bazie. Partycjonowanie po zakresie przypisuje dane do partycji na podstawie zakresu klucza partycji. Dla każdej partycji określa się zakresy w których występują dane. Przykładem takiego partycjonowania może być partycjonowanie po dacie gdzie każdy miesiąc jest osobną partycją lub zwykłe zakresy wartości od-do. Z poniższego kursu dowiesz się jakie operacje na partycjach są dozwolone oraz jakie wiążą się z nimi skutki.

Ten wpis jest częścią Kursu Oracle SQL

Partycjonowanie po zakresie a struktura danych

W partycjonowaniu po zakresie dzielimy tabelę na mniejsze kawałki (partycje) w zależności od zakresu. Każda z partycji jest zakresem z określoną wartością maxymalną (HIGH_VALUE). Utwórzmy więc dwie tabele, jedną jako zwykłą a drugą z tym samym zakresem danych ale z partycjami.

Utworzenie tabeli porównawczej:

CREATE TABLE TEST_TABLE (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , COL1 DATE
  , CONSTRAINT TEST_TABLE_ID_PK PRIMARY KEY (ID)
);

Utworzenie tabeli partycjonowanej po zakresie (ang. range partition) wraz z partycjami:

CREATE TABLE TEST_TABLE_PARTITIONED (
    ID NUMBER GENERATED ALWAYS AS IDENTITY
  , FOO VARCHAR2(255)
  , BAR NUMBER
  , COL1 DATE
  , CONSTRAINT TEST_TABLE_PARTITIONED_ID_PK PRIMARY KEY (ID)
) PARTITION BY RANGE (COL1)
 ( PARTITION YEAR_UNDER_2017 VALUES LESS THAN (TO_DATE('2017-01-01','YYYY-MM-DD'))
 , PARTITION YEAR_2017 VALUES LESS THAN (TO_DATE('2018-01-01','YYYY-MM-DD'))
 , PARTITION YEAR_2018 VALUES LESS THAN (TO_DATE('2019-01-01','YYYY-MM-DD'))
 , PARTITION YEAR_2019 VALUES LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD'))
 , PARTITION YEAR_2020 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD')) 
 );

Powyżej widzimy utworzenie tabeli wraz z 5 partycjami w zależności od dat. Tabela jest partycjonowana po kolumnie COL1 typu DATE. Każda z partycji jest pewnym zakresem dat z wartościami maksymalnymi. Baza danych sama wie do której partycji dodać rekord i tak dodając rekord z wartością w kolumnie COL1 2019-10-13 zostanie dodany tylko do partycji YEAR_2019. Każdy rekord znajduje się tylko w jednej partycji.

Mając już przygotowane dwie tabele dodajmy do nich te same rekordy poniższym skryptem:

BEGIN
  FOR I IN 0 .. 10000
  LOOP
    INSERT INTO TEST_TABLE (
        FOO
      , BAR
      , COL1
    ) VALUES (
        'ACME: ' || MOD(I, 10)
      , I
      , SYSDATE - MOD(I, 1000)
    );
    
    INSERT INTO TEST_TABLE_PARTITIONED (
        FOO
      , BAR
      , COL1
    ) VALUES (
        'ACME: ' || MOD(I, 10)
      , I
      , SYSDATE - MOD(I, 1000)
    );
  END LOOP;
END;
/

COMMIT;

oraz przeliczmy statystki:

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST_TABLE');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST_TABLE_PARTITIONED');

W tym momencie mamy przygotowane dwie tabele jedna zwykła TEST_TABLE (tzn. bez partycji) a druga TEST_TABLE_PARTITIONED podzielona na 5 partycji.

Porównanie widoku tabeli i tabeli partycjonowanej.

Zobaczmy więc jak wyglądają definicje tabel w tabeli user_tables:

SELECT
    *
FROM USER_TABLES
WHERE TABLE_NAME IN ('TEST_TABLE', 'TEST_TABLE_PARTITIONED');

Powyższy zrzut jest wycinkiem wyniku jednak widać z niego, że definicja zwykłej tabeli od tabeli partycjonowanej różni się znaczącą. Szczególną różnicą miedzy tabelami jest brak definicji tablespace czy dane dotyczące przestrzeni danych w tabeli. Jest tak ponieważ tabela partycjonowana to “zbiór” osobnych segmentów z których każdy może znajdować się w innym tablespace.

Zobaczmy więc jak obie tabele wyglądają w widoku segmentów:

SELECT
    *
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN ('TEST_TABLE', 'TEST_TABLE_PARTITIONED');

W widoku DBA_SEGMENTS widzimy utworzony jeden segment dla standardowej tabeli. Dla tabeli partycjonowanej utworzonych zostało segmentów, jeden per partycja. Teraz widzimy, w którym tablespace znajduje się która partycja/segment.

Każdy segment składa się z extentów więc zobaczmy ich widok:

SELECT
    *
FROM DBA_EXTENTS
WHERE SEGMENT_NAME IN ('TEST_TABLE', 'TEST_TABLE_PARTITIONED');

W powyższym widoku zauważyć można, że ilość extentów dla tabeli partycjonowanej jest większa, oraz ma inny rozkład. Jest to spowodowane tym, że każda partycja to osobny segment składający się z extentów dlatego np. partycje YEAR_2018 i YEAR_2019 posiadają po dwa extenty.

Przydatnym widokiem pozwalającym zajrzeć do partycji jest DBA_TAB_PARTITIONS:

SELECT 
    *
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME IN ('TEST_TABLE', 'TEST_TABLE_PARTITIONED');

Przede wszystkim w widoku tym nie zobaczymy żadnych informacji o tabeli TEST_TABLE ponieważ nie jest tabelą partycjonowaną. W widoku tym natomiast znajdziemy wiele informacji o samych partycjach jak np. maksymalną wielkość zakresu, parametry takie jak PCT_FREE, czy statystki takie jak ilość rekordów.

Operacje na tabelach partycjonowanych

Korzystanie z tabel partycjonowanych jest często niezauważalne przez użytkownika bazy danych. Pisząc jednak SQL z wykorzystaniem tych tabel należy być świadomym ich specyfiki. Poniżej przedstawię kilka operacji na tabelach partycjonowanych które wyglądają nieco inaczej niż na standardowych tabelach i warto znać ich charakterystykę.

ENABLE ROW MOVEMENT

Wiemy już, że każda partycja to osobny segment więc co się stanie gdy zaktualizujemy rekord w tabeli partycjonowanej, czy zostanie w starej partycji ze znacznikiem a może zostanie przeniesiony? Przed wykonaniem update na tabeli zobaczmy dwa rekordy z zwykłej tabeli i tabeli partycjonowanej:

SELECT 'TEST_TABLE' TAB, ROWID, ID, COL1 FROM TEST_TABLE WHERE ID = 1000
UNION ALL
SELECT 'TEST_TABLE_PARTITIONED' TAB, ROWID, ID, COL1 FROM TEST_TABLE_PARTITIONED WHERE ID = 1000;

wykonajmy więc operację update na kolumnie COL1.

UPDATE TEST_TABLE SET COL1 = SYSDATE WHERE ID = 1000; -- 1 row updated.
UPDATE TEST_TABLE_PARTITIONED SET COL1 = SYSDATE WHERE ID = 1000;

Pierwszy update na zwykłej tabeli wykona się bez problemu.Drugi update nie uda się i zwróci błąd ORA-14402:

Error starting at line : 91 in command -
UPDATE TEST_TABLE_PARTITIONED SET COL1 = SYSDATE WHERE ID = 1000
Error report -
ORA-14402: aktualizacja kolumny klucza partycji spowodowałaby zmianę partycji

Standardowo tabela partycjonowana nie pozwala aktualizować kolumn po których jest partycjonowana jeżeli zmiana kolumny spowodowałaby zmianę partycji. Aby umożliwić update na rekordzie który spowoduje zmianę partycji należy w tabeli partycjonowanej włączyć opcję ENABLE ROW MOVEMENT.

ALTER TABLE TEST_TABLE_PARTITIONED ENABLE ROW MOVEMENT;

Po wykonaniu powyższej zmiany baza danych pozwoli zmienić partycję rekordowi po jego update. Wykonajmy więc ponownie update:

UPDATE TEST_TABLE_PARTITIONED SET COL1 = SYSDATE WHERE ID = 1000; -- 1 row updated.

Teraz rekord udało się zaktualizować. Zmiana partycji niesie ze sobą pewne konsekwencje w szczególności zmianę ROWID rekordu. Zobaczmy więc wynik poprzedniego zapytania SELECT:

SELECT 'TEST_TABLE' TAB, ROWID, ID, COL1 FROM TEST_TABLE WHERE ID = 1000
UNION ALL
SELECT 'TEST_TABLE_PARTITIONED' TAB, ROWID, ID, COL1 FROM TEST_TABLE_PARTITIONED WHERE ID = 1000;

Względem poprzedniego wyniku widzimy zmianę w ROWID rekordu o ID 1000. Dzieje się tak ponieważ w ROWID zaszyty jest nr segmentu w którym znajduje się rekord. Skoro więc rekord został przeniesiony do nowego segmentu to zmianie uległ również ROWID.

INTERVAL

Dodając rekord do tabeli partycjonowanej zostaje on automatycznie przypisany do odpowiedniej mu partycji. Co jednak gdy dodamy rekord który nie pasuje do żadnej partycji? Spróbujmy dodać więc do tabeli TEST_TABLE_PARTITIONED rekord z roku 2021

INSERT INTO TEST_TABLE_PARTITIONED (FOO, BAR, COL1) VALUES ('FOO', 1, TO_DATE('2021-10-10', 'YYYY-MM-DD'));

W odpowiedzi dostaniemy z bazy danych błąd i komunikat ORA-14400:

Error starting at line : 105 in command -
INSERT INTO TEST_TABLE_PARTITIONED (FOO, BAR, COL1) VALUES ('FOO', 1, TO_DATE('2021-10-10', 'YYYY-MM-DD'))
Error report -
ORA-14400: wstawiony klucz partycji nie jest odpowiedni dla żadnej partycji

Ostatnia partycja w tabeli TEST_TABLE_PARTITIONED ustawioną ma wartość MAX na pierwszy dzień 2021 roku. W związku z tym powyższy rekord nie może trafić do żadnej z partycji.

Jednym z rozwiązań tej sytuacji jest ustawienie automatycznego tworzenia partycji poprzez klauzul INTERVAL wraz z ustawionym interwałem dodawania nowych rekordach. Polecenie może wyglądać jak poniżej:

ALTER TABLE TEST_TABLE_PARTITIONED SET INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'));

Powyższa opcja tworzy nowe partycje z zakresem co rok. Spróbujmy więc dodać poprzedni rekord do tabeli:

INSERT INTO TEST_TABLE_PARTITIONED (FOO, BAR, COL1) VALUES ('FOO', 1, TO_DATE('2021-10-10', 'YYYY-MM-DD')); --1 row inserted.

Rekord został dodany i w tabeli utworzona została nowa partycja. Zobaczmy więc teraz jak wyglądają partycje w widoku: USER_TAB_PARTITIONS:

SELECT 
    *
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME IN ('TEST_TABLE', 'TEST_TABLE_PARTITIONED');

Widzimy, że baza utworzyła nową partycję o nazwie “SYS_P768” z zakresem do pierwszego dnia 2022 roku.

MAXVALUE

Inną metodą umożliwiającą dodawanie rekordów spoza zakresów partycji jest utworzenie partycji nieograniczonej zakresem. Do tego celu służy zakres MAXVALUE. Jednak MAXVALUE nie można łączyć z partycjami INTERVAL. Dlatego, wpierw wyłączmy na tabeli TEST_TABLE_PARTITIONED automatyczne tworzenie nowych partycji poleceniem:

ALTER TABLE TEST_TABLE_PARTITIONED SET INTERVAL();

Następne utwórzmy partycję z zakresem MAXVALUE:

ALTER TABLE TEST_TABLE_PARTITIONED ADD PARTITION END_OF_WORLD VALUES LESS THAN ( MAXVALUE );

Teraz gdy będziemy tworzyć rekordy z wartością w COL1 większą niż 2022 rok to będą trafiać właśnie do tej partycji. Zobaczmy też jak prezentuje się nowa partycja w widoku USER_TAB_PARTITIONS:

W powyższym widoku widzimy, że nowa partycja END_OF_WORLD ma ustawioną wartość HIGH_VALUE na MAXVALUE. Teraz wszystkie rekordy po 2022 roku trafiać będą do tej partycji.

Podsumowanie

  • Tabela partycjonowana to zbiór partycji
  • Każda partycja to osobny segment
  • Tabela partycjonowana po zakresie przyjmuje HIGH_VALUE który określa górną granicę partycji
  • W tabeli partycjonowanej po zakresie kolumna klucza musi być typu NUMBER lub DATE
  • Aby umożliwić przenoszenie rekordów między partycjami należy włączyć opcję ENABLE ROW MOVEMENT
  • Aby automatycznie tworzyć nowe partycje należy wykorzystać opcję INTERVAL
  • Aby utworzyć partycję nieograniczoną wartością można wykorzystać parametr MAXVALUE

  • Jeżeli chcesz znaleźć więcej kursów dla średniozaawansowanych oraz poznać zasady optymalizacji zapytań zapoznaj się z pozostałymi kursami, sprawdź: Kurs Oracle SQL
  • Jeżeli chcesz poznać podstawy baz danych i kurs dla początkujących w SQL odwiedź Kurs SQL

2 komentarze

  1. Wszystko fajnie.
    Tylko co w przypadku jak mamy założony na tabeli local index domenowy i chcemy usunać najniższą partycje a potem dodać kolejną?

    W moim przypadku index się rozsypuje i jest:

    ORA-14523: Nie mo?na powi?za? [pod]partycji local index z [pod]partycj? tabeli poniewa? rozmiar bloku local index [8192] nie jest zgodny z rozmiarem bloku tabeli [32768]
    14523. 00000 – “Cannot co-locate [sub]partition of %s %s with table [sub]partition because %s block size [%s] does not match table block size [%s]”
    *Cause: A DDL statement was issued that would require
    a partition/subpartition of a local index/LOB column to
    be co-located with the corresponding partition/subpartition
    of the base table. However, this is not possible because
    the block sizes of the table and the LOB column/local
    index are different.
    *Action: Either
    (1) Specify an object-level default tablespace (or
    partition-level default tablespace for the
    appropriate partition, if composite partitioning is
    used) for the partitioned local index/LOB column
    and then retry the DDL command, OR
    (2) Ensure that tablespaces of the correct block size are
    specified for all new partitions/subpartitions being
    created.
    Also ensure that neither of TABLESPACE DEFAULT and
    STORE IN (DEFAULT) is specified for a local index whose
    block size does not match that of the base table.

    Dawid

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *