یادآوری
یسری قواعد جامعیتی در بانک‌اطلاعاتی اوراکل وجود داره که شما می‌تونید از اونها در این بانک استفاده کنید کار این قواعد اینه که شما هرچی خواستید رو نتونید به عنوان داده در بانک وارد کنید.
مثال:
CRAETE TABLE T2(ID NUMBER PRIMARY KEY, NAME VARCHAR2(20));
به چند چیز باید توجه کنیم(چون ID به عنوان PRIMARY KEY) تعریف شده):
- ID نمی‌تونه NULL باشه
- ID نمی‌تونه تکراری باشه
INSERT INTO T2(ID, NAME) VALUES(1, 'MAHDI');
همونطور که در زیر می‌بینید با یکسان قرار دادن ID پایگاه داده خطایی به ما برمی‌گرداند:
INSERT INTO T2(ID, NAME) VALUES(1, 'EHSAN');
*
ERROR at line 1:
ORA-00001: unique constraint (MGHAFFARI.SYS_C0010911) violated
حالا ID احسان رو می‌ذاریم ۲ و INSERT رو انجام می‌دیم:
INSERT INTO T2(ID, NAME) VALUES(1, 'EHSAN');
*
ERROR at line 1:
ORA-00001: unique constraint (MGHAFFARI.SYS_C0010911) violated
وحالا می‌خوایم ID رو NULL رد کنیم:
INSERT INTO T2(NAME) VALUES('EHSAN');
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (MGHAFFARI.SYS_C0010911) violated
همونطور که می‌بینید نمی‌تونیم مقدار NULL رد کنیم.
قواعد جامعیتی کلید خارجی
۲ نوع دید در سطح بانک‌اطلاعاتی موجود بوده و خواهد بود:
- عده‌ای جز PRIMARY KEY چیز دیگری را در بانک‌اطلاعاتی در نظر نمی‌گیرند.
- عده‌ای ساختار RELATION را نیز در سطح دیتابیس (یعنی ارتباط جداول باهم) تعریف می‌کنند.
هر کدوم از این ساختارها ارزش خودش رو داره و نمیشه منکر هیچکدوم شد و بسته به نوع پروژه باید انتخاب بشه
کسانی که میان این دید رو اعمال می‌کنند که اصلاً از FOREIGN KEY استفاده نکنند و در سطح دیتابیس تعریف نمی‌کنند با این تفکر کار می‌کنن که می‌گن بار بانک‌اطلاعاتی به اندازه کافی زیاد است، و زمانی که ما FOREIGN KEY تعریف می‌کنیم یه باری برای چک کردن FOREIGN KEY باید صرف بشه و برای همین میان این بار رو می]وان از سطح دیتابیس حذف کنن و می‌برن رو سطح اپلیکیشن.
یعنی زمانی که ما می‌خوایم درج اطلاعات وابسته کنیم تو جدول ثانویه از سطح اپلیکیشن قبلش چک می‌کنیم که رکورد وجود داره یا نه اگه وجود داره درج اطلاعات رو انجام می‌دیم و اگر وجود نداره درج اطلاعات رو انجام نمی‌دیم.
به عنوان مثال:
نکته: توجه کنید همیشه ما ID رو در سطح جداول ایجاد می‌کنیم.
CREATE TABLE PERSON(ID NUMBER, NAME VARCHAR2(20), ID_INFO NUMBER);
ّنکته: همونطور که می‌بینید در این جدول هیچ RELATION ای تعریف نشده است.
حالا یک جدول جدید می‌سازیم به اسم INFO با فیلدهای ID, ADDRESS:
CREATE TABLE INFO(ID NUMBER, ADDRESS VARCHAR2(20));
حالا اتفاق بدی که برای این دوستانی که می‌خوان از سطح اپلیکیشن ریلیشن رو برقرار کنن و نمی‌خوان از سطح دیتابیس ریلیشن رو برقرار کنن چیه؟ برای فهمیدن این موضوع داده وارد می‌کنیم:
نکته: اگر به همان ترتیبی که فیلدهای جدول را ایجاد کردیم بیایم ورود اطلاعات رو انجام بدیم نیازی به آوردن اسم فیلدها در INSERT نیست.
INSERT INTO PERSON VALUES(1, 'MAHDI', 1);
INSERT INTO PERSON VALUES(2, 'EHSAN', 2);
INSERT INTO INFO VALUES(1, 'TEHRAN');
INSERT INTO INFO VALUES(2, 'KARAJ');
خب ما ۲ جدول در بالا ایجاد و ورود اطلاعات رو انجام می‌دیم.
در ۲ جدول بالا ID جدول INFO با ID جدول PERSON از لحاظ منطقی در ارتباط اند. خونه مهدی تهران و خونه احسان کرج است.
حالا می‌خوایم یه حرکتی کنیم به اسم 'ضرب دکارتی به شرط تساوی'
ضرب دکارتی
می‌خوایم اطلاعات ۲ جدول بالا رو باهم از طریق ضرب دکارتی ترکیب کنیم.
NAME رو از جدول PERSON و ADDRESS رو از جدول INFO می‌گیریم:
SELECT PERSON.NAME, INFO.ADDRESS FROM PERSON, INFO;
NAME ADDRESS
-------------------- --------------------
MAHDI TEHRAN
MAHDI KARAJ
EHSAN TEHRAN
EHSAN KARAJ
به اینکار می‌گن ضرب دکارتی همونطور که می‌بینید اتفاق جالبی نیوفتاده چون همه‌ی رکوردها در هم ضرب شده یعین هر رکورد از جدول سمت چپ با رکورد جدول سمت راست داره ضرب میشه. مهدی یه دفعه خونه‌اش تهران‌ه یه دفعه خونه‌اش کرج‌ه احسان یه دفعه خونه‌اش تهران‌ه یه دفعه خونه‌اش کرج‌ه و به همین ترتیب اطلاعات صحیحی رو به ما نشون نداده
ضرب دکارتی به شرط تساوی
با شرط WHERE در ضرب دکارتی ما ممانعت می‌کنیم از ضرب دکارتی:
SQL> SELECT PERSON.NAME, INFO.ADDRESS FROM PERSON, INFO WHERE PERSON.ID_INFO = INFO.ID;
NAME ADDRESS
-------------------- --------------------
MAHDI TEHRAN
EHSAN KARAJ
همونطور که می‌بیند الان داده صحیح نمایش داده شده و همونطور که می‌بینید مهدی خونه‌اش تهران‌ه و احسان خونه‌اش کرج‌ه
نکته: در اصل داریم می‌گیم FOREIGN KEY جدول PERSON با FOREIGN KEY جدول INFO یکی باشه و در اصل ضرب دکارتی انجام میشه ولی ما به دسترو WHERE خروجی رو فیلتر می‌کنیم.
خب بهتره بدونید روش‌های دیگه‌ای هم برای کسب اطلاعات از این نوع جداول وجود داره که اصطلاحاً به آنها JOIN گفته میشه (انواع JOIN در حقیقت LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER JOIN)
دلیل استفاده نکردن از JOIN
یه چیزی رو باید بدونید که در huge dataها عمل join انجام نباید بشه، join یه اتفاق وحشتناکی تو سطح دیتابیس ایجاد میکنه که ۱۰۰ درصد performance databse رو در اوراکل پایین میاره
نکته: منظورمون از عمل join دقیقاً خود کلمه join است نظیر LEFT JOIN, RIGHT JOIN, INNER JOIN, FULL OUTER JOIN و ضرب دکارتی منظورمون به عنوان Join در اینجا نیست
نکته: تمام فناوری‌های جاوا نطیر hibernate هیچکدوم از کلمه join استفاده نمی‌کنند
در بحث join اوراکل از هر ۲ جدول شما snapshot میگیره (هم از جدول چپ و هم از جدول راست) و این snapshotرو میاره تو محفظه‌ای به نام temp table می‌ریزد. این جدول هربار با وارد شدن کاربر به سیستم ساخته شده و با خروج کاربر(قطع ارتباط با دیتابیس) حذف می‌شود. اما هزینه ایجاد TEMP TABLE(باری که روی CPU, RAM میوفته) دقیقاً برابر با هزینه ساخت TABLE و DROP TABLE هنگام قطع ارتباط کاربر است. خب این یه چیز بسیار بسیار وحشتناکیه که بیایم به دلیل راحت کردن کار خودمون و استفاده از JOIN همچین هزینه‌ای رو بندازیم رو سطح دیتابیس که یک TABLE موقت ایجاد بشه و اون TABLEموقت از بین بره
نکته: به طور قطع این نکته فقط برای ORACLE, DB2 صادقه و تفکر ما تماماً اینه که JOIN رو از بین ببریم درباره ساختار دیتابیس‌های دیگه و صحت داشتن این مطلب اطلاعاتی ندارم.
البته ORDBBMSها یک راهکاری دیگری هم برای نگه‌داری این RELATIONها دارند، و در اصل ORDBMSها کار مدیریت این RELATIONها را انجام می‌دهند. به طور کل ضرب‌دکارتی سخته ولی بهتر از JOIN ه، JOIN هم که آسونه ولی نباید استفاده کنیم.
استفاده از VIEW برای ضرب دکارتی
حالا یکی از بحث‌هایی که می‌تونیم راجع بهش صحبت کنیم اینه که ما ترجیح می‌دیم ضرب دکارتی رو تو محفظه‌ای به نام VIEW نگه‌داری کنیم.
نکته: VIEW چیزی جز یک دستور SQL نیست. VIEW در حقیقت از یک دستور SQL مشتق میشه و شما می‌تونید باهاش مثل یک جدول رفتار کنید.
مثال:
CREATE VIEW HASELE_ZARB AS SELECT PERSON.NAME, INFO.ADDRESS FROM PERSON, INFO WHERE PERSON.ID_INFO = INFO.ID;
SQL> SELECT * FROM HASELE_ZARB;
NAME ADDRESS
-------------------- --------------------
MAHDI TEHRAN
EHSAN KARAJ
همچنین خروجی دستور INNER JOIN:
SQL> SELECT * FROM PERSON INNER JOIN INFO ON PERSON.ID_INFO=INFO.ID;
ID NAME ID_INFO ID ADDRESS
---------- -------------------- ---------- ---------- --------------------
1 MAHDI 1 1 TEHRAN
2 EHSAN 2 2 KARAJ
نزدیکتر کردن ضرب دکارتی به شرط تساوی به خروجی INNER JOIN:
SQL> SELECT PERSON.ID, PERSON.NAME,PERSON.ID_INFO, INFO.ID, INFO.ADDRESS FROM PERSON, INFO WHERE PERSON.ID_INFO = INFO.ID;
ID NAME ID_INFO ID ADDRESS
---------- -------------------- ---------- ---------- --------------------
1 MAHDI 1 1 TEHRAN
2 EHSAN 2 2 KARAJ
یکبار که VIEW رو اجرا می‌کنیم VIEW در SGA کش میشه و ضرب دکارتی به این صورت کش شده و دیگه برای محاسبه آن باری در دیتابیس صرف نمی‌شود.
توجه داشته باشید ساختار اوراکل ساختار هوشمندیه پس اگر insert یا update روی جداول اصلی بیاد اون تغییرات روی view نیز تغییر می‌کنند. به این صورت سرعت پرس و جو نیز بالا می‌رود.
یکی از مسائل خیلی پیچیده در اوراکل همین موضوع است
همونطور که گفتیم view از جدول مشتق میشه، و گفتیم view رو می‌سازیم که هربار بار ضرب دکارتی رو سطح دیتابیس نباشه خب شاید به نظر شما این در صورتی صحیحه که ما تغییری روی جداول نداشته باشیم و اگر تغییری در جداول انجام بشه view دوباره باید ضرب دکارتی براش انجام بشه چون view مشتق از جدول ولی اینطور نیست
توجه داشته باشید اگر تغییری رو جداول اصلی رخ دهد ضرب دکارتی دوباره انجام نمی‌شود و incremental مستقیم بر روی view ما انجام می‌شود (اوراکل مستقیماً روی view اون تغییر رو اتچ میکنه و دوباره انجام به ضرب دکارتی نمی‌کنه(دقیقاً همون یه دونه رکورد رو اتچ میکنه رو سطح view نه کل رکوردها را)چون اوراکل ساختار VIEW رو بعد از اولین اجرای VIEW می‌فهمه)
نکته: توجه داشته باشید که ما به هیچ عنوان DYNAMIC TABLE نداریم
س: اگر ساختار VIEW ما تغییر کند چه اتفاقی رخ می‌دهد؟
ج: اگر VIEW ما یک VIEW مشتق نباشه تغییر میکنه ولی اگه VIEW ما یک VIEWمشتق باشه اونوقت INSERT به همین راحتی‌ها انجام نمی‌گیرد
س: اگر روی جداول ما تغییری صورت گیرد آیا این تغییر بلافاصله در VIEW رخ می‌دهد؟
ج: بله - در حقیقت ۲ تا THREAD روی INSERT باز می‌شود و تغییرات به طور همزمان هم روی جدول و هم روی کش VIEWانجام می‌گیرد.
نکته: هر تغییری روی CTL فایل‌ها صورت گیرد تمام ها باید دوباره کش شوند. در حقیقت هر وقت دستور SQL عوض بشه چون RECOMPILE انجام میشه کش باید تغییر کنه و کل DEPENDENCEهای مربوطه نیز باید دوباره کش شوند.
مشکلات ضرب دکارتی
خب میایم یه رکورد جدید در جدول PERSON با ID ای که وجود نداره وارد می‌کنیم:
SQL> INSERT INTO PERSON VALUES (3, 'HOSSEIN', 16);
1 row created.
SQL> SELECT * FROM HASELE_ZARB;
NAME ADDRESS
-------------------- --------------------
MAHDI TEHRAN
EHSAN KARAJ
همونطور که می‌بینید HOSSEIN وجود نداره، چون رکورد وابسته در جدول INFO وجود نداره این یکی از چیزهایی که تو LEFT JOIN حل شده است:
SQL> SELECT PERSON.NAME, INFO.ADDRESS FROM PERSON LEFT JOIN INFO ON PERSON.ID_INFO=INFO.ID;
NAME ADDRESS
-------------------- --------------------
MAHDI TEHRAN
EHSAN KARAJ
HOSSEIN
نکته: به دلیل همین مشکلات در ضرب دکارتی ما در پروژه‌های عملی دچار مشکلاتی برای نمایش صحیح خروجی می‌شویم و کارمون سختتر میشه.
برای حل این مشکل باید یک select جدا از جدول person بگیریم و یک select جدا هم از جدول info و ضرب دکارتی رو دیگه تو سطح دیتابیس انجام ندیم و بیایم تو سطح اپلیکیشن ضرب رو انجام بدیم.
همچنین اگر ما در سطح اپلیکیشن مطمئن شویم که هر ۲ جدول باهم رابطه صحیحی دارند و transaction بدون مشکل در سطح اپلیکیشن باشه در نتیجه ما مشکلی در ضرب دکارتی نداریم.
نکته: select تو در تو حتی از ضرب دکارتی نیز در performance بهتر عمل می‌کند، چون در اصل اون select تودرتو ضرب دکارتی ما را انجام می‌دهد(همچنین hibernate و اکثر فریم‌ورکهای جاوا نیز به همین روش کار می‌کنند در واقع پیچیدگی زمانی این روش (O(log n است)