#top Przydatne informacje¶

#top Timeout¶

#top Database Storage Layout¶

#top Tablespace¶

#top SQL JOIN Visual Explanation¶

• [INNER] JOIN - słowo kluczowe INNER jest opcjonalne, złączenia JOIN i INNER JOIN są równoważne,
• LEFT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia LEFT JOIN i LET OUTER JOIN są równoważne,
• RIGHT [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia RIGHT JOIN i RIGHT OUTER JOIN są równoważne,
• FULL [OUTER] JOIN - słowo kluczowe OUTER jest opcjonalne, złączenia FULL JOIN i FULL OUTER JOIN są równoważne,
• CROSS JOIN

-- utworzenie tabel
CREATE TABLE join01 (
idjoin number(9) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
)

CREATE TABLE join02 (
idjoin number(9) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY(idjoin)
)

-- dodanie przykładowych danych
INSERT INTO join01(idjoin,name) VALUES(1,'t01name01')
INSERT INTO join01(idjoin,name) VALUES(2,'t01name02')
INSERT INTO join01(idjoin,name) VALUES(3,'t01name03')
INSERT INTO join01(idjoin,name) VALUES(4,'t01name04')
INSERT INTO join01(idjoin,name) VALUES(5,'t01name05')

INSERT INTO join02(idjoin,name) VALUES(3,'t02name03')
INSERT INTO join02(idjoin,name) VALUES(4,'t02name04')
INSERT INTO join02(idjoin,name) VALUES(5,'t02name05')
INSERT INTO join02(idjoin,name) VALUES(6,'t02name06')
INSERT INTO join02(idjoin,name) VALUES(7,'t02name07')

#top SQL JOIN INNER JOIN¶

SELECT * FROM join01 JOIN join02 ON (join01.idjoin=join02.idjoin)
SELECT * FROM join01 INNER JOIN join02 ON (join01.idjoin=join02.idjoin)

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
Retrieved 3 rows in 0.010 seconds.

#top SQL JOIN LEFT JOIN¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin)
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin)

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
|1     |t01name01|NULL  |NULL     |
|2     |t01name02|NULL  |NULL     |
Retrieved 5 rows in 0.007 seconds.

#top SQL JOIN RIGHT JOIN¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin)
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin)

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
|NULL  |NULL     |6     |t02name06|
|NULL  |NULL     |7     |t02name07|
Retrieved 5 rows in 0.006 seconds.

#top SQL JOIN FULL JOIN¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin)
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin)

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|3     |t01name03|3     |t02name03|
|4     |t01name04|4     |t02name04|
|5     |t01name05|5     |t02name05|
|NULL  |NULL     |6     |t02name06|
|NULL  |NULL     |7     |t02name07|
|1     |t01name01|NULL  |NULL     |
|2     |t01name02|NULL  |NULL     |
Retrieved 7 rows in 0.006 seconds.

#top SQL JOIN LEFT JOIN IS NULL¶

SELECT * FROM join01 LEFT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL
SELECT * FROM join01 LEFT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join02.idjoin IS NULL

|IDJOIN|NAME     |
+------+---------+
|1     |t01name01|
|2     |t01name02|
Retrieved 2 rows in 0.224 seconds.

#top SQL JOIN RIGHT JOIN IS NULL¶

SELECT * FROM join01 RIGHT JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL
SELECT * FROM join01 RIGHT OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL

|IDJOIN|NAME     |
+------+---------+
|6     |t02name06|
|7     |t02name07|
Retrieved 2 rows in 0.006 seconds.

#top SQL JOIN FULL JOIN IS NULL¶

SELECT * FROM join01 FULL JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL
SELECT * FROM join01 FULL OUTER JOIN join02 ON (join01.idjoin=join02.idjoin) WHERE join01.idjoin IS NULL OR join02.idjoin IS NULL

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|6     |t02name06|NULL  |NULL     |
|7     |t02name07|NULL  |NULL     |
|NULL  |NULL     |1     |t01name01|
|NULL  |NULL     |2     |t01name02|
Retrieved 4 rows in 0.007 seconds.

#top SQL JOIN CROSS JOIN¶

SELECT * FROM join01 CROSS JOIN join02

|IDJOIN|NAME     |IDJOIN|NAME     |
+------+---------+------+---------+
|1     |t01name01|3     |t02name03|
|1     |t01name01|4     |t02name04|
|1     |t01name01|5     |t02name05|
|1     |t01name01|6     |t02name06|
|1     |t01name01|7     |t02name07|
|2     |t01name02|3     |t02name03|
|2     |t01name02|4     |t02name04|
|2     |t01name02|5     |t02name05|
|2     |t01name02|6     |t02name06|
|2     |t01name02|7     |t02name07|
|3     |t01name03|3     |t02name03|
|3     |t01name03|4     |t02name04|
|3     |t01name03|5     |t02name05|
|3     |t01name03|6     |t02name06|
|3     |t01name03|7     |t02name07|
|4     |t01name04|3     |t02name03|
|4     |t01name04|4     |t02name04|
|4     |t01name04|5     |t02name05|
|4     |t01name04|6     |t02name06|
|4     |t01name04|7     |t02name07|
|5     |t01name05|3     |t02name03|
|5     |t01name05|4     |t02name04|
|5     |t01name05|5     |t02name05|
|5     |t01name05|6     |t02name06|
|5     |t01name05|7     |t02name07|
Retrieved 25 rows in 0.005 seconds.

#top Database Objects Size¶

#top Find Multiple Indexes¶

#top Find Duplicate Indexes¶

#top Find Unused Indexes¶

#top Performance Tuning¶