جداول بسیار حجیم در Very Large Databaseها (انواع پارتیشن‌بندی)

به index گذاری که روی سطح لایه فیزیکی دیتابیس باشه اصطلاحاً پارتیشن‌بندی گفته می‌شود.

partition by range

به مثال زیر دقت کنید، در این مثال جدولی با پارتیشن بر مبنای فیلدهای id, age ساخته‌ایم:

SQL> create table sal (id number,age number,name varchar2(20))
partition by range(id,age)
(partition s1 values less than (10,20) tablespace ts1,
partition s2 values less than(20,30) tablespace ts2,
partition s3 VALUES LESS THAN (MAXVALUE) tablespace ts3);

در ادامه گفته‌ایم که مقدارهای ورودی در پارتیشن s1 باید کمتر از 10,20 باشند و این پارتیشن در tablespace ts1 قرار دارد(یعنی idهای کمتر از ۱۰ و ageهای کمتر از ۲۰)

در ادامه پارتیشن s2 را داریم که مقدارهای کمتر از 20,30 را در خود می‌گیرد و در tablespace ts2 قرار دارد.

و در ادامه پارتیشن s3 را داریم که اگر رکوردی وارد شد که تو بازه‌ی پارتیشن‌بندی ما نبود اون رکورد که حالا معلوم نیست بزرگه یا کوچیکه وارد tablespace ts3 شود.

نکته: اسم پارتیشن‌ها زیاد اهمیتی برای ما ندارند.

نکته: اگر عملیات update انجام شود و مقدار عوض شود جابه‌جایی در پارتیشن نیز رخ خواهد داد.

نکته: تو partition by range بهتره که تمام فیلدها مشخص شوند، یعنی چیز نامعلومی که کاربر بخواد وارد کنه و به مشکل بخوره نباشه

DataDictionaryهای مربوط به TableSpaceها

دیتادیکشنری جداولی هستند که به صورت پیش‌فرض در اوراکل وجود دارند و ساختار دیتابیس رو نگه‌داری می‌کنند. اونایی که اولشون DBA است جدول هستند و اونایی که اولشون V است VIEW هستند.

یه وضعیتی روی TableSpaceها وجود داره که شما می‌تونید به وسیله دیتا دیکشنری اسم همه TableSpaceهاتون رو پیدا کنید:

نکته: با دستور DESCRIB می‌توانید ساختار یک جدول را ببینید. وقتی می‌خوایم ساختار جداول دیتادیکشنری رو ببینیم از این دستور پر کاربرد استفاده می‌کنیم.

SQL> DESCRIB DBA_TABLESPACES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 TABLESPACE_NAME                            NOT NULL  VARCHAR2(30)
 BLOCK_SIZE                                 NOT NULL  NUMBER
 INITIAL_EXTENT                                       NUMBER
 NEXT_EXTENT                                          NUMBER
 MIN_EXTENTS                                NOT NULL  NUMBER
 MAX_EXTENTS                                          NUMBER
 MAX_SIZE                                             NUMBER
 PCT_INCREASE                                         NUMBER
 MIN_EXTLEN                                           NUMBER
 STATUS                                               VARCHAR2(9)
 CONTENTS                                             VARCHAR2(9)
 LOGGING                                              VARCHAR2(9)
 FORCE_LOGGING                                        VARCHAR2(3)
 EXTENT_MANAGEMENT                                    VARCHAR2(10)
 ALLOCATION_TYPE                                      VARCHAR2(9)
 PLUGGED_IN                                           VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                             VARCHAR2(6)
 DEF_TAB_COMPRESSION                                  VARCHAR2(8)
 RETENTION                                            VARCHAR2(11)
 BIGFILE                                              VARCHAR2(3)
 PREDICATE_EVALUATION                                 VARCHAR2(7)
 ENCRYPTED                                            VARCHAR2(3)
 COMPRESS_FOR                                         VARCHAR2(30)

TableSpaceهای حجیم

هر دیتافایل در اوراکل تا حجم 8 اگزابایت معادل 8 میلیون ترابایت می‌تواند مقدار‌دهی شود. و توجه داشته باشید که یک دیتابیس از یک مخزن می‌تواند به طور نامحدود دیتافایل داشته باشد.

برای ایجاد TableSpaceهای حجیم بهتره در اوراکل از دستور زیر استفاده کنید، چون مکانیزم index گذاری DataFileهای حجیم فرق میکنه و دیگه snapshot بیس نیست:

Create bigfile tablespace ts datafile ‘d:\root\a.dbf’ size 100g;

نکته: بعد از زدن دستور ایجاد دیتافایل با سایز زیاد مدت زمانی طول می‌کشد تا initialize انجام شود. اندازه ۱۰۰ گیگ Gap رو ایجاد می‌کنه ته فایل رو می‌بنده.

نکته: اگر تمام فضای پارتیشن شما با دیتافایل پر شود نه بخشی از آن تمام آن بخش به دیتافایل اختصاص می‌یابد و دیگر قابل recovery نیز نیست.

نکته: معمولاً از حجم ۱ تا ۱۰ گیگ به بالا وقتی شما دارید دیتافایل ایجاد می‌کنید تا سقف ۸ اگزابایت tablespace ای که ایجاد می‌کنید بهتره bigfile‌ باشه

m = megabyte
g = gigabyte
t = terabyte
p = petabyte
e = exabyte

س: ۵ اگزابایت چند بایت است؟

ج: 5.0 × 10¹⁵ kilobytes به سرچ زیر در گوگل دقت کنید:

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=5+exabyte+convert+to+kilobyte

فقط خواندنی و فقط نوشتنی کردن یک TableSpace

برای اینکه یک TableSpace را به حالت read only ببریم از دستور زیر استفاده می‌کنیم، در این حالت از جداولی که روی TableSpace ما هستند فقط میشه select گرفت و نمیشه ورود داده کرد:

نکته: وقتی می‌خواهیم در یک بازه زمانی اصلاً ورود و یا تغییر داده روی فیلدها انجام نشود، از این حالت استفاده می‌کنیم.

alter tablespace ts read only;

برای اینکه TableSpace را به حالت اول برگردونیم از دستور زیر استفاده می‌کنیم، در این حالت هم اجازه خواندن را می‌دهیم و هم اجازه نوشتن را:

alter tablespace ts read write;

جلوگیری از log کردن داده‌های تغیر پیدا کرده در TableSpace

برای این کار از دستور زیر موقع ایجاد TableSpace استفاده می‌کنیم:

create tablespace ts datafile 'd:\root\a.dbf' size 100m nologging;

مخفی‌کردن TableSpcae برای کاربران

در طول کارتان شاید به این نیاز پیدا کنید که یکسری از TableSpaceها را از دسترس خارج کنید تا هیچ عملیاتی نتوان روی TableSpace مورد نظر انجام داد(نه میشه دیتا رو ازش خوند نه میشه دیتا روش نوشت پس جداولی که روی اون TableSpace هستند کلاً از کار می‌افتند):

ممکنه تو یه محدوده زمانی خاص نخواین روی این TABLESPACE عملی انجام بشه، مثلاً یکجور EXPORT خاص از دیتابیس می‌خواین بگیرین اونوقت باید آفلاین کنید یا وقتی می‌خواین دیتافایلهای یک TABLESPACE رو REPAIR کنید.

نکته: اگر تراکنشی با جدولی در ارتباط باشه که شما می‌خواهید TABLESPACE اون جدول را آفلاین کنید بلافاصله کل تراکنش ROLLBACK میشه.

SQL> CREATE TABLESPACE TS DATAFILE 'E:\A.DBF' SIZE 100M ;
Tablespace created.

SQL> ALTER TABLESPACE TS OFFLINE;
Tablespace altered.

SQL> ALTER TABLESPACE TS ONLINE;
Tablespace altered.

ساخت TableSpace با رشد خودکار فضا

فضایی بسازید که تا 200mb به طور خودکار 10m 10m رشد می کند: (در صورتی که maxsize مشخص نشود فضا می‌گیرد)

SQL> create tablespace ts datafile 'd:\root\a.dbf' size 100m reuse autoextend on
 next 10m  maxsize 200m;

در دستور بالا 100mb سایز اولیه TableSpaceامون است اگر این مقدار فضای اولیه پر شود به طور خودکار 10m 10m به فضا اضافه می‌شود تا به 200m برسد. در صورتی که فضای maxsize مشخص نگردد تا جایی که مدیا ذخیره‌سازی اجازه دهد به DataFile ما 10m 10m اضافه می‌شود.

نکته: عددی که برای رشد DataFile مشخص می‌کنیم بسیار مهم است. اگر این عدد را زیاد بگیریم فضای مدیای‌ذخیره‌سازی ما از بین می‌رود چون این فضا به طور کامل اشغال و توسط اوراکل رزرو می‌شود و ممکن است دیتای ما آنقدر فضا نیاز نداشته باشد. اگر این عدد را کم بگیریم (مثلا ۱ کیلوبایت) اینجوری مدیای ذخیره‌سازی ما فضای هدر شده ندارد ولی تو این مدت که بخواد به 200m برسه بار بسیار وحشتناکی رو CPU سرور ما داره چون به ازای هر کیلوبایت می‌خواد فایل رو extend کنه پس performance سیستم به شدت پایین می‌آید.

نکته: من ترجیح می‌دم فضای از دست رفته‌ام زیاد باشه نه اینکه بار CPU ام زیاد بشه و performance سیستم پایین بیاد

س: آیا با اضافه کردن به دیتا‌فایل در مدیای ذخیره‌سازی ما Gap ایجاد می‌شود؟

ج: این بستگی به سیستم‌عامل و سیستم‌فایلتان دارد و به اوراکل به صورت معمولی در حالت FileSystem ربطی ندارد. مگر در حالت ASM باشید که در این حالت اوراکل هیچ Gap ای در مدیای ذخیره‌سازی شما ایجاد نمی‌کند.

ساخت یک TableSpace با ۲ دیتافایل

برای ایجاد یک TableSpace با ۲ دیتافایل در زمان ایجاد TableSpace به صورت زیر عمل می‌کنیم:

نکته: من معمولاً‌این کار رو انجام نمیدوم و به صورت معمولی یک TableSpace‌می‌سازم و دیتافایل‌هایی را در آینده به آن وصل می‌کنم.

create tablespace homeworkts
datafile 'D:\oradata\orcl\df1.dbf' size 4m ,
    'D:\oradata\orcl\df2.dbf' size 4m;

اضافه‌کردن فضا به TableSpace

برای انجام اینکار می‌توان به فرم کلی زیر اینکار را انجام داد:

alter tablespace <tablespace_name>  add datafile <url> size <size>;

مثال:

alter tablespace ts  add datafile 'd:\root\b.dbf' size 100m;

نکته: مسیر tablespace شما می‌تواند در شبکه نیز باشد. حتی می‌تونه تو درایوهای دیگر و هر نوع مدیای ذخیره‌سازی نظیر: هارد اکسترنال، فلش، رم و ... باشد.

نکته: اگر هر کدوم از DataFileهای ما از شبکه خارج شوند یا از حالت mount بیرون بیایند موقع start کردن اوراکل به مشکل جدی می‌خورید و اورکل تا همه DataFileهای مد نظر را نداشته باشد دیتابیس را  startup open نمی‌کند.(به صورت کلی باید DataFile همیشه باشد و بدون نبود DataFile به هیچ‌وجه نمی‌توان دیتابیس را startup open کرد برای همین پیشنهاد می‌کنم که همیشه Backup داشته باشید)

نکته: در این موارد بهترین کار flash recovery است.

نکته: اگر بخواهید یک table‌ را روی چند tablespace پراکنده کنیم باید حتماً partitioning کنید و جالبه بدونید که قبل از ورژن 11g اوراکل اجازه اینکار را اصلاً نمی‌داد چون اصلاً بحث partitioning مطرح نبود.

س: چرا وقتی می‌توان چند DBF را به یک TableSpace اضافه کرد باید از چند TableSpace برای ذخیره‌سازی یک جدول استفاده کرد؟

ج: به خاطر سرعت index گذاری و cache اولیه - مثلاً وقتی می‌گیم select * from person where age =20 اوراکل به صورت پیش‌فرض اصلاً به where شما گوش نمی‌دهد اما حالا فرض کنید فضای SGA فضای کافی نباشه که بتونه کل جدول رو لود کنه (این مثال در مورد جداول بسیار بسیار حجیم صحت دارد مثلاً جدول ۱ اگزابایتی) خب شما نمی‌تونید رم ۱ اگزابایتی پیدا کنید که کش رو تو فضای SGA انجام بده پس کش کامل انجام نمیشه و همونطور که قبلاً گفتم اوراکل در این مورد خورد خورد عمل میکنه و تیکه تیکه دیتا رو برای کش میاره و در این حالت وقتی میخواد بره تیکه تیکه بیاره وقتی میبینه اندازه کش کافی نیست به سرعت میره و فقط اون قسمت where رو پیدا میکنه و کش میکنه و شما اگه partitioning اتون رو بر مبنای age کرده باشید اوراکل به سرعت اون DataFile‌رو پیدا میکنه و کش میکنه به این حالت میگن (کش اولیه یعنی زمانی که میخواد برای بار اول کش رو انجام بده)

یعنی زمانی که برای کش فضای خالی به اندازه کافی نباشد اوراکل به where شما توجه می‌کند و از partitioning به نفع خودش استفاده می‌کند تا به دیتا فایلی برسد که داده ما درون آن است.(این فقط در مورد tableهای وحشتناک سنگین صدق می‌کند)

TableSpace

یکی از مباحث خیلی مهم در سطح دیتابیس Tablespaceها هستند.

گفتیم رکوردهای ما رو سطح DataFile ذخیره می‌شوند، حال اگر بخواهیم DataFile را در سطح اوراکل ایجاد کنیم علاوه بر DataFile ای که قبلاً رو سطح دیتابیس وجود داشته باید از سک دستور SQL استفاده کنیم.

توی اوراکل مدیریت DataFileها و دسته‌بندی DataFileها توسط یک موجودیت منطقی مدیریت میشه به نام موجودیت Tablespace پس وقتی ما یک DataFile ایجاد می‌کنیم بعد از ایجاد DataFile مورد نظرمون اونو داخل یک tablespace می‌ذاریم بعد جدولمون رو به tablespaceامون وصل می‌کنیم.

پس DataFile رو ایجاد می‌کنیم DataFile یک ساختار فیزیکی داره به یک tablespace لینکش می‌کنیم بعد جداولمون رو به tablespaceامون وصل می‌کنیم. جدول ما از لحظه‌ای که به tablespaceامون وصل میشه اطلاعاتش رو میریزه در داخل DataFile ای که به Tablespace مدنظر وصل شده است.

به این ترتیب مدیریت DataFileها توسط Tablespace انجام می‌شود و شما هیچوقت یک جدول رو به طور مستقیم به یک DataFile وصل نمی‌کنید همیشه یک جدول رو به یک Tablespace وصل می‌کنید.

Tablespace در اصل واسط شما و DataFileتون است.