Š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:
CROSS JOIN rezultāts būtu šāds:
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
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
- The power of the Cross Join – kā Dekarta reizinājumu izmantot atskaitēs.
- Retrieval: Multiple Tables and Aggregation – Part 1 – nodaļa no grāmatas Mastering Oracle SQL and SQL *Plus, ko uzrakstījis Lex de Haan.
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).
Š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ā.
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’
Ī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).