Dekarta reizinājums (Cross Join)

Šajā rakstā detalizēti paskatīsimies uz vienu no tabulu kombinēšanas veidiem – CROSS JOIN.

For english speaking only check SQL join types. 

Kas ir Dekarta reizinājums (tas pats arī Cross Join, Cartesian Join, Cartesian Product)?

Iedomājamies, ka mums ir 2 tabulas A un B. Grafiski to ieraksti ir attēloti šādi:
Tabula A un B

CROSS JOIN rezultāts būtu šāds:

Cross Join

Tātad kā to viegli noprast katrs ieraksts tabulā A tiek kombinēts ar katru ierakstu tabulā B. Vispārīgā gadījumā tas, protams, var rezultēties diezgan mežonīgā ierakstu skaitā, piemēram, ja tabulā A ir 100 000 (simttūkstoš) ieraksti (kas ir pietiekami parasts skaits), tabulā B tikpat, tad Dekarta reizinājums būs 10 000 000 000 (desmit miljardi), kas jau varētu būt krietni virs vidējā🙂

Sintakse

SELECT <kolonu saraksts>
FROM <kreisās puses tabula>
CROSS JOIN <labās puses tabula>

SELECT un FROM klauzas ir aprakstītas iepriekšējā tēmā Vienkāršs SQL Select teikums. Par CROSS JOIN sintaksi pat īsti nav ko atsevišķi vairāk piebilst. Ir arī otrs veids, kā panākt tieši to pašu – vienkārši FROM klauzā rakstot iekšā abas tabulas un neizmantojot nekādus WHERE nosacījumus:

SELECT <kolonu saraksts>
FROM <kreisās puses tabula>, <labās puses tabula>

Saturiski šāds tabulu kombinēšanas veids ir nepieciešams diezgan reti, daži no izmantošanas gadījumiem varētu būt šādi:

  • Lai vienkārši ģenerētu lielu skaitu ierakstu. Kā jau redzējām no relatīvi mazām tabulām, var iegūt diezgan monstrozus skaitļus. Tiesa gan jāskatās vai šāds ierakstu ģenerēšanas veids ir pats efektīvākais, pat tad ja daudzie ieraksti ir nepieciešami.
  • Lai atrastu visu ierakstu kombinācijas. Tas visbiežāk ir nepieciešams dažādām atskaitēm, lai iegūtu visas iespējamās raksturojošo klasifikatoru kombinācijas, piemēram, personām dzimumi × tautības.
  • Lai piekombinētu klāt tabulu ar tieši vienu ierakstu. Var noderēt, lai iegūtu dažādus konfigurēšanas parametrus.

Taču visbiežāk Dekarta reizinājums sanāk vinkārši kļūdas dēļ, kad ir aizmirsts pievienot JOIN nosacījums WHERE klauzai.

Kas jāatceras papildus:

  • Jebkurš no visiem pārējiem tabulu kombinēšanas veidiem var degradēties par Dekarta reizinājumu, ja JOIN nosacījums vienmēr ir patiess, tas attiecas, gan uz NATURAL JOIN sintaksi, gan arī INNER un OUTER JOIN sintaksi. Skat piemērus 3, 4, 5.
  • Veidojot CROSS JOIN, kas ir arī SELF JOIN (tabulas kombinēšanas pati ar sevi), obligāti ir jālieto tabulu aizstājējvārdi, lai DBVS saprastu, no kuras tabulas instances kolonu ņemt. Skat piemēru 6.
  • Tāpat kā pārsvarā visos citos SQL teikumos tabulu vietā patiesībā var būt cits apakšvaicājums, skatījums vai jebkāda cita konstrukcija, kas atgriež ierakstus.

Piemēri

Izmantosim šādas tabulas.

CREATE TABLE adreses (
  adr_id INTEGER NOT NULL PRIMARY KEY,
  adr_pilseta VARCHAR2(40),
  adr_teksts VARCHAR2(40) NOT NULL);
CREATE TABLE personas (
  prs_id INTEGER NOT NULL PRIMARY KEY,
  prs_tevs_id INTEGER,
  prs_mate_id INTEGER,
  prs_adr_id INTEGER,
  prs_personas_kods VARCHAR2(11) NOT NULL,
  prs_vards VARCHAR2(40),
  prs_uzvards VARCHAR2(40),
  CONSTRAINT prs_prs_tevs_fk FOREIGN KEY (prs_tevs_id)
    REFERENCES personas(prs_id),
  CONSTRAINT prs_prs_mate_fk FOREIGN KEY (prs_mate_id)
    REFERENCES personas(prs_id),
  CONSTRAINT prs_adr_fk FOREIGN KEY (prs_adr_id)
    REFERENCES adreses(adr_id));
INSERT INTO adreses VALUES (1, 'RĪGA', 'BRĪVĪBAS IELA 123, dz. 456');
INSERT INTO adreses VALUES (2, 'TALSI', 'LIELĀ IELA 12, dz. 34');
INSERT INTO adreses VALUES (3, 'VALMIERA', 'CĒSU IELA 1, dz. 2');
INSERT INTO personas VALUES (1, NULL, NULL, NULL, '12345678910',
  'JĀNIS', 'BĒRZIŅŠ');
INSERT INTO personas VALUES (2, 1, NULL, 2, '23456789012',
  'PĒTERIS', 'BĒRZIŅŠ');
INSERT INTO personas VALUES (3, NULL, NULL, 2, '34567890123',
  'ANNA', 'BĒRZIŅA');
INSERT INTO personas VALUES (4, 2, 3, 2, '45678901234',
  'KĀRLIS', 'BĒRZIŅŠ');
COMMIT;

Piemērs 1. Atlasa personas kodu, vārdu un adreses pilsētu no personu un adrešu tabulu Dekarta reizinājuma izmantojot CROSS JOIN sintaksi.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas
  3  CROSS JOIN adreses;
PRS_PERSONA PRS_VARDS                                ADR_PILSETA
----------- ---------------------------------------- -----------
12345678910 JĀNIS                                    RĪGA
23456789012 PĒTERIS                                  RĪGA
34567890123 ANNA                                     RĪGA
45678901234 KĀRLIS                                   RĪGA
12345678910 JĀNIS                                    TALSI
23456789012 PĒTERIS                                  TALSI
34567890123 ANNA                                     TALSI
45678901234 KĀRLIS                                   TALSI
12345678910 JĀNIS                                    VALMIERA
23456789012 PĒTERIS                                  VALMIERA
34567890123 ANNA                                     VALMIERA
45678901234 KĀRLIS                                   VALMIERA
12 rows selected.

Piemērs 2. Atlasa personas kodu, vārdu un adreses pilsētu no personu un adrešu tabulu Dekarta reizinājuma rakstot abas tabulas FROM klauzā.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas, adreses;
PRS_PERSONA PRS_VARDS                                ADR_PILSETA
----------- ---------------------------------------- -----------
12345678910 JĀNIS                                    RĪGA
23456789012 PĒTERIS                                  RĪGA
34567890123 ANNA                                     RĪGA
45678901234 KĀRLIS                                   RĪGA
12345678910 JĀNIS                                    TALSI
23456789012 PĒTERIS                                  TALSI
34567890123 ANNA                                     TALSI
45678901234 KĀRLIS                                   TALSI
12345678910 JĀNIS                                    VALMIERA
23456789012 PĒTERIS                                  VALMIERA
34567890123 ANNA                                     VALMIERA
45678901234 KĀRLIS                                   VALMIERA
12 rows selected.

Piemērs 3. Atlasa personas kodu, vārdu un adreses pilsētu no personu un adrešu tabulu Dabiskā savienojuma izmantojot NATURAL INNER JOIN. Tā kā šīm tabulām nav kopēju kolonu ar vienādu nosaukumu, tad rezultāts ir Dekarta reizinājums, tāds pats kā iepriekšējos piemēros.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas
  3  NATURAL INNER JOIN adreses;

Piemērs 4. Atlasa personas kodu, vārdu un adreses pilsētu no personu un adrešu tabulu Dabiskā savienojuma izmantojot NATURAL FULL OUTER JOIN. Tā kā šīm tabulām nav kopēju kolonu ar vienādu nosaukumu, tad rezultāts ir Dekarta reizinājums.Kā redzams ir mainījusies rindu attēlošanas kārtība, bet rindu skaits un saturs nav mainījies.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas
  3  NATURAL FULL OUTER JOIN adreses;
PRS_PERSONA PRS_VARDS                                ADR_PILSETA
----------- ---------------------------------------- -----------
12345678910 JĀNIS                                    RĪGA
12345678910 JĀNIS                                    TALSI
12345678910 JĀNIS                                    VALMIERA
23456789012 PĒTERIS                                  RĪGA
23456789012 PĒTERIS                                  TALSI
23456789012 PĒTERIS                                  VALMIERA
34567890123 ANNA                                     RĪGA
34567890123 ANNA                                     TALSI
34567890123 ANNA                                     VALMIERA
45678901234 KĀRLIS                                   RĪGA
45678901234 KĀRLIS                                   TALSI
45678901234 KĀRLIS                                   VALMIERA
12 rows selected.

Piemērs 5. Atlasa personas kodu, vārdu un adreses pilsētu no personu un adrešu tabulu kombinēšanas izmantojot vienkārši INNER JOIN. Tā kā kombinēšanas nosacījums ir 1=1 un tas vienmēr ir patiess, tad rezultāts atkal ir Dekarta reizinājums.
SQL> SELECT prs_personas_kods, prs_vards, adr_pilseta
  2  FROM personas
  3  INNER JOIN adreses ON (1=1);
PRS_PERSONA PRS_VARDS                                ADR_PILSETA
----------- ---------------------------------------- --------------
12345678910 JĀNIS                                    RĪGA
23456789012 PĒTERIS                                  RĪGA
34567890123 ANNA                                     RĪGA
45678901234 KĀRLIS                                   RĪGA
12345678910 JĀNIS                                    TALSI
23456789012 PĒTERIS                                  TALSI
34567890123 ANNA                                     TALSI
45678901234 KĀRLIS                                   TALSI
12345678910 JĀNIS                                    VALMIERA
23456789012 PĒTERIS                                  VALMIERA
34567890123 ANNA                                     VALMIERA
45678901234 KĀRLIS                                   VALMIERA
12 rows selected.

Piemērs 6. Atlasa personas kodu un vārdu no personas tabulas kombinēšanas pašai ar sevi (SELF JOIN). Tā kā kolonas ir abās tabulās, tad tabulām obligāti jālieto aizstājējvārds. Tā kā personu tabulā ir 4 ieraksti, tad Dekarta reizinājums pašai ar sevi dod 16 ierakstus.
SQL> SELECT p1.prs_personas_kods, p2.prs_vards
  2  FROM personas p1
  3  CROSS JOIN personas p2;
PRS_PERSONA PRS_VARDS
----------- ----------
12345678910 JĀNIS
23456789012 JĀNIS
34567890123 JĀNIS
45678901234 JĀNIS
12345678910 PĒTERIS
23456789012 PĒTERIS
34567890123 PĒTERIS
45678901234 PĒTERIS
12345678910 ANNA
23456789012 ANNA
34567890123 ANNA
45678901234 ANNA
12345678910 KĀRLIS
23456789012 KĀRLIS
34567890123 KĀRLIS
45678901234 KĀRLIS
16 rows selected.

Turpmākā lasāmviela

4 Responses to Dekarta reizinājums (Cross Join)

  1. MārtiņšŠ says:

    No prakses – Cartesian ir gadījies ieraudzīt arī situācijā, kad divas GLOBAL TEMPORARY tabulas JOINotas ar parastu tabulu:
    CREATE TABLE t1 (id NUMBER(10));
    CREATE GLOBAL TEMPORARY TABLE t2 (id NUMBER(10), t1_id NUMBER(10));
    CREATE GLOBAL TEMPORARY TABLE t3 (id NUMBER(10), t1_id NUMBER(10));

    Tad …
    SELECT t1.id, t2.id, t3.id
    FROM t1, t2, t3
    WHERE t2.t1_id = t1.id
    AND t3.t1_id = t1.id
    ;
    … explain plānā ir gadījies ieraudzīt, ka pa priekšu ar CARTESIAN tiek apvienotas abas GLOBAL TEMPORARY tabulas (t2 un t3), un tikai pēc tam rezultātu apvieno ar parasto tabulu (t1).

  2. Gints Plivna says:

    Šai rakstā galvenokārt es biju fokusējies uz to, ka mēs paši SQLā prasam veidot cartesian join. Tas, ka Oracle optimizeris reizēm patvaļīgi izvēlas lietot operāciju MERGE JOIN (CARTESIAN), lai fiziski izpildītu mūs vaicājumu, kaut gan tur nemaz mēs tādu neprasam, ir pavisam cita lieta.
    Un gadījumos, kad tā ir kļūda, tas lielākoties ir tāpēc, ka ir nekorektas statistikas. Atceramies, ka ar GLOBAL TEMPORARY TABLE ir grūtāk savākt reprezentatīvu statistiku vairāku iemeslu dēļ:
    1) iespējams, ka mūsu bizness ir tāds, ka vienreiz šai tabulā ir 1 ieraksts otrreiz 10K, līdz ar to permanenti reprezentatīvu statistiku savākt vienkārši nav iespējams;
    2) ja temp tabulā vienmēr ir puslīdz vienāds ierakstu skaits un GLOBAL temporary table ir veidota ar opciju on commit preserve rows, tad normāli būtu ielādēt reprezentatīvu skaitu rindu temp tabulā un izrēķināt statistiku ar dbms_stats.gather_table_stats;
    3) ja temp tabulā vienmēr ir puslīdz vienāds ierakstu skaits un GLOBAL temporary table ir veidota ar opciju on commit delete rows, tad ar dbms_stats.gather_table_stats vienkārši nevar savākt korektu statistiku, jo tās rezultāts vienmēr ir ieraktsu skaits = 0. Tādā gadījumā var izmantot procedūru dbms_stats.set_table_stats un uzlikt statistikas manuāli.
    4) Ja negribas cīnīties ar (3) iespēju, tad statistikas nevajag permanenti rēķnāt, bet var izmantot dynamic sampling (atkarībā no Oracle versijas defaultais optimizer_dynamic_sampling ir atšķirīgs:
    If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
    If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
    If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0)
    Šeit var redzēt, ko katrs līmenis nozīmē.
    Ar hintu dynamic_sampling konkrētā vaicājumā var uzlikt vēlamo līmeni un tas tiks ņemts vērā vispirms, nevis noklusētais parametrs optimizer_dynamic_sampling.

    Vissliktākā situācija varētu veidoties tad, ja statistikas temporary tabulām ir izrēķinātas, kad tās ir tukšas, tad optmizeris vaicājuma plāna veidošanas laikā domā, ka tabulas ir tukšas, patiesībā tā nemaz nav, un var sanākt šis MERGE JOIN (CARTESIAN) pilnīgi nevietā.

  3. mr.numb says:

    SELECT prs_personas_kods, prs_vards, adr_pilseta FROM personas cross JOIN adreses ON personas.prs_adr_id = adreses.adr_id

    ‘23456789012’, ‘PĒTERIS’, ‘TALSI’
    ‘34567890123’, ‘ANNA’, ‘TALSI’
    ‘45678901234’, ‘KĀRLIS’, ‘TALSI’

  4. Gints Plivna says:

    Īsti nav skaidrs, kas ar šo komentāru domāts, bet CROSS JOIN sintakse nepieļauj nekādas ON klauzas, tas ir Dekarta reizinājumā tiek ņemtas visu ierakstu kombinācijas un viss. Tā kā šāda komentārā sniegtā sintakse ir nekorekta un vismaz Oraclē ir kļūdas paziņojums. Pēc rezultāta spriežot vēlme ir iegūt iekšējo savienojumu (inner join).

Komentēt

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Mainīt )

Twitter picture

You are commenting using your Twitter account. Log Out / Mainīt )

Facebook photo

You are commenting using your Facebook account. Log Out / Mainīt )

Google+ photo

You are commenting using your Google+ account. Log Out / Mainīt )

Connecting to %s

%d bloggers like this: