CTAS w Oracle SQL - składnia, przykłady i pułapki

Gdy trzeba szybko przygotować kopię danych, tabelę stagingową albo wynik złożonego raportu, ręczne definiowanie kolumn i osobne ładowanie rekordów jest zwykle stratą czasu. Technika CREATE TABLE AS SELECT, znana jako CTAS, pozwala utworzyć tabelę i od razu wypełnić ją wynikami zapytania. Pokażę składnię, praktyczne przykłady, ograniczenia, różnice względem INSERT INTO ... SELECT oraz pułapki związane z indeksami, constraintami i wydajnością.

Na czym polega CREATE TABLE AS SELECT w Oracle

CTAS łączy dwa działania. Oracle analizuje zapytanie SELECT, na jego podstawie tworzy strukturę nowej tabeli, a później zapisuje w niej zwrócone wiersze. To wygodne rozwiązanie do tworzenia kopii roboczych, tabel raportowych, danych tymczasowych i uproszczonych wycinków dużych zbiorów.

CREATE TABLE pracownicy_kopia AS
SELECT *
FROM pracownicy;

Powyższe polecenie utworzy tabelę pracownicy_kopia z kolumnami i danymi zwróconymi przez zapytanie. Jeśli tabela o tej nazwie już istnieje, Oracle zwróci błąd, dlatego przed ponownym uruchomieniem skryptu trzeba usunąć obiekt albo użyć innej nazwy. CTAS nie dopisuje danych do istniejącej tabeli.

W praktyce częściej wybieram jawne kolumny niż SELECT *. Dzięki temu zmiana struktury tabeli źródłowej nie rozsypie nagle procesu ETL lub raportu.

CREATE TABLE aktywne_konta AS
SELECT
    account_id,
    customer_id,
    status,
    created_at
FROM accounts
WHERE status = 'ACTIVE';

Nowa tabela zawiera tylko wskazane kolumny i rekordy spełniające warunek. Oracle wyprowadzi nazwy kolumn z zapytania, a dla wyrażeń bez aliasu może nadać im mniej czytelną nazwę. Alias dla każdej kolumny obliczanej to prosty sposób na uniknięcie późniejszych problemów.

Jak wybierać, filtrować i przekształcać dane

Największa zaleta CTAS nie polega na samym kopiowaniu tabeli, lecz na tym, że wynik może pochodzić z dowolnego poprawnego zapytania. Można filtrować rekordy, łączyć tabele, zmieniać nazwy kolumn, obliczać wartości i agregować dane.

Wybór kolumn i zmiana nazw

CREATE TABLE klienci_kontakt AS
SELECT
    customer_id AS id_klienta,
    first_name || ' ' || last_name AS imie_nazwisko,
    email AS adres_email
FROM customers
WHERE email IS NOT NULL;

Wynikowa tabela będzie miała trzy kolumny o nazwach nadanych przez aliasy. Oracle dobierze typy danych na podstawie wyrażeń, dlatego przy bardziej złożonych operacjach warto sprawdzić długości i precyzję kolumn w słowniku danych.

Łączenie tabel i agregowanie

CREATE TABLE sprzedaz_miesieczna AS
SELECT
    EXTRACT(YEAR FROM order_date) AS rok,
    EXTRACT(MONTH FROM order_date) AS miesiac,
    customer_id,
    SUM(total_amount) AS suma_sprzedazy,
    COUNT(*) AS liczba_zamowien
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date),
    customer_id;

To dobry przykład tabeli pod raport lub dashboard. Zamiast za każdym razem liczyć agregaty na dużej tabeli transakcyjnej, można przygotować mniejszy zbiór wynikowy. Trzeba jednak pamiętać, że taka tabela nie aktualizuje się automatycznie, więc proces zasilania należy uruchamiać ponownie zgodnie z potrzebą.

Utworzenie pustej tabeli

CTAS może utworzyć również samą strukturę, bez kopiowania rekordów. Najprostszy wariant wykorzystuje warunek, który nigdy nie jest spełniony.

CREATE TABLE orders_staging AS
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE 1 = 0;

Takie rozwiązanie jest przydatne przy przygotowywaniu tabel stagingowych, ale nie zastępuje pełnego projektu modelu danych. Otrzymasz kolumny i typy wynikające z zapytania, lecz nadal musisz samodzielnie dodać indeksy, klucze i inne reguły integralności.

Co zostaje skopiowane, a czego Oracle nie przenosi

CTAS kopiuje przede wszystkim rezultat zapytania, a nie kompletną definicję tabeli źródłowej. To różnica, o której łatwo zapomnieć podczas tworzenia kopii produkcyjnych danych.

Jeżeli nowa tabela ma działać jak tabela aplikacyjna, po CTAS trzeba dodać brakujące elementy jawnie.

ALTER TABLE sprzedaz_miesieczna
ADD CONSTRAINT pk_sprzedaz_miesieczna
PRIMARY KEY (rok, miesiac, customer_id);

CREATE INDEX ix_sprzedaz_miesieczna_klient
ON sprzedaz_miesieczna (customer_id);

Nie zakładałbym też, że właściwości kolumn obliczanych będą takie same jak w źródle. Na przykład wyrażenie może zmienić długość tekstu albo precyzję liczby. Rzutowanie przez CAST daje wtedy większą kontrolę.

CREATE TABLE raport_kwot AS
SELECT
    order_id,
    CAST(total_amount AS NUMBER(12, 2)) AS kwota
FROM orders;

Kiedy użyć CTAS, a kiedy INSERT SELECT

Obie konstrukcje wykorzystują zapytanie SELECT, ale rozwiązują inny problem. CTAS tworzy nowy obiekt, natomiast INSERT INTO ... SELECT ładuje dane do tabeli, która już istnieje.

Jeśli tabela ma być częścią aplikacji i musi mieć precyzyjnie określone typy, domyślne wartości, klucze oraz komentarze, zwykle wybieram klasyczne CREATE TABLE, a potem INSERT. CTAS wygrywa szybkością przygotowania, lecz nie zawsze jest najlepszym narzędziem do finalnego modelu danych.

Wydajność, transakcje i bezpieczeństwo

Przy dużych zbiorach CTAS może być znacznie wygodniejszy niż wieloetapowe kopiowanie rekordów. Oracle potrafi wykonać część tworzenia i zapytania równolegle, jeśli użyjesz odpowiednich ustawień.

CREATE TABLE podsumowanie_sprzedazy
PARALLEL 4
NOLOGGING
AS
SELECT
    product_id,
    SUM(quantity) AS suma_sztuk
FROM order_items
GROUP BY product_id;

PARALLEL może skrócić czas pracy na dużych tabelach, ale zwiększa zużycie CPU, pamięci i operacji wejścia-wyjścia. Po zakończeniu ładowania warto sprawdzić, czy tabela ma pozostać równoległa, czy lepiej przywrócić ustawienie NOPARALLEL.

NOLOGGING bywa użyteczne przy odtwarzalnych tabelach pośrednich, lecz zmniejsza poziom ochrony przed awarią podczas operacji. Nie stosowałbym go bezrefleksyjnie dla danych, których nie można łatwo odtworzyć. Szybkość ładowania nie jest ważniejsza od możliwości odzyskania danych.

Trzeba też pamiętać, że CREATE TABLE jest poleceniem DDL. Oracle wykonuje automatyczne zatwierdzenie transakcji przed i po operacji DDL, więc zwykły ROLLBACK nie zachowa się tutaj tak jak przy modyfikacji danych przez UPDATE lub INSERT. W skrypcie wdrożeniowym sprawdzam dlatego nazwę tabeli, uprawnienia oraz ilość wolnego miejsca jeszcze przed uruchomieniem CTAS.

Do utworzenia tabeli w swoim schemacie potrzebujesz uprawnienia CREATE TABLE. Jeżeli zapytanie odczytuje dane z innych schematów, użytkownik musi mieć również odpowiednie uprawnienia do obiektów źródłowych. Tabela utworzona przez CTAS jest standardowo trwałym obiektem, a nie automatycznie tabelą tymczasową.

Typowe błędy przy tworzeniu tabeli z SELECT

Użycie SELECT *

To działa, ale wiąże wynik z aktualnym kształtem tabeli źródłowej. Dodanie kolumny może zmienić strukturę tabeli docelowej albo spowodować konflikt z dalszym etapem procesu. W kodzie produkcyjnym lepiej wypisać kolumny jawnie.

Oczekiwanie na skopiowanie indeksów

Nowa tabela może wyglądać tak samo w wynikach zapytania, ale bez indeksów zachowywać się zupełnie inaczej. Po utworzeniu dużej tabeli sprawdzam plan zapytań i tworzę tylko te indeksy, które odpowiadają rzeczywistym filtrom oraz joinom.

Brak aliasu dla wyrażenia

CREATE TABLE nieczytelny_raport AS
SELECT price * quantity
FROM order_items;

Oracle utworzy kolumnę na podstawie wyrażenia, ale jej nazwa może być niewygodna w dalszym użyciu. Czytelniejszy zapis wygląda tak:

CREATE TABLE czytelny_raport AS
SELECT price * quantity AS wartosc_pozycji
FROM order_items;

Przeczytaj również: CONVERT w SQL Server - daty, TRY_CONVERT i wydajność

Próba dodania typu danych w definicji CTAS

Przy CTAS typy wynikają z zapytania. Jeśli podajesz własne nazwy kolumn w części definicyjnej, Oracle nadal wymaga pominięcia typów danych. Gdy potrzebujesz pełnej kontroli, rozdziel operację na dwa polecenia.

CREATE TABLE raport_sprzedazy (
    order_id      NUMBER(12) NOT NULL,
    total_amount  NUMBER(12, 2),
    report_date   DATE
);

INSERT INTO raport_sprzedazy (order_id, total_amount, report_date)
SELECT order_id, total_amount, order_date
FROM orders;

Najlepszy sposób na bezpieczne użycie CTAS

Przed uruchomieniem zapytania sprawdzam najpierw sam SELECT, liczbę wynikowych rekordów i typy kolumn. Potem oceniam, czy tabela ma być tylko etapem pośrednim, czy pełnoprawnym obiektem aplikacyjnym z kluczami, indeksami i uprawnieniami.

• Używaj jawnej listy kolumn w procesach produkcyjnych.
• Nadaj aliasy wszystkim wyrażeniom i kolumnom obliczanym.
• Dodaj indeksy oraz constrainty po utworzeniu tabeli.
• Przy dużych danych zmierz wpływ PARALLEL na inne procesy.
• Stosuj NOLOGGING tylko wtedy, gdy dane można bezpiecznie odtworzyć.
• Pamiętaj, że CTAS tworzy tabelę trwałą, chyba że świadomie wybierzesz mechanizm tabel tymczasowych.

Dobrze użyte CTAS jest jednym z najbardziej praktycznych poleceń Oracle SQL. Pozwala szybko zbudować wycinek danych, tabelę raportową albo etap pośredni przetwarzania, ale nie powinno być mylone z pełnym klonowaniem tabeli. Najważniejsza zasada jest prosta: CTAS kopiuje wynik zapytania, a nie cały projekt tabeli, dlatego wszystkie elementy potrzebne aplikacji trzeba zaplanować i dodać osobno.