برای ذخیره داده های بدون ساختار در بانک اطلاعاتی اوراکل، ابتدا نوع داده LONG و LONG RAW ارائه شد که LONG برای ذخیره کاراکترهای با حجم زیاد و LONG RAW برای فایلهای باینری(صوت، تصویر و ..) استفاده می شد(و البته می شود) این نوع داده دارای محدودیتهای بسیاری بودند که شاید به همین دلیل اوراکل در نسخه 8i، نوع داده (large object)LOB را معرفی کرد که بسیاری از محدودیتهای LONG و LONG RAW را برطرف می کند از جمله این رفع محدودیتها می توان به موراد زیر اشاره کرد:

1.گنجایش مصرفی نوع داده LOB برابر با 4 گیگابایت می باشد در صورتی که LONG و LONG RAW بیشتر از 2 گیگابایت را پشتیبانی نمی کنند.

2.در هر جدول می توان از چندین ستون با نوع داده LOB استفاده کرد در صورتی که نوع داده ایLONG RAW و LONG نمی توانند بیشتر از یک ستون در یک جدول داشته باشند.

3.نوع داده ای LOB دسترسی تصادفی را پشتیبانی می کند ولی نوع داده LONG تنها دسترسی ترتیبی را پشتیبانی می کند.

4.استفاده از LONG و LONG RAW در بسیاری از عملیات از قبیل replication میسور نیست در صورتی که این عملیات برای LOBها ممکن می باشد.

5.با استفاده از نوع داده LOB کاربر می تواند نوع داده دیگری تعریف کند(user-defined datatype).

6.امکان ذخیره LOB segment در خارج از جدول و حتی در tablespace دیگر هم ممکن خواهد بود(البته LOB locator باید در جدول اصلی ذخیره شود). در صورتی که برای نوع داد long و long raw چنین قابلیتی وجود ندارد و داده های با این نوع، باید در خود جدول ذخیره شوند.

LOB و محدودیتها

صرف نظر از نوع داده LONG و LONG RAW، نوع داده LOB هم محدودیتهایی دارد که از جمله این محدودیتها می توان به موارد زیر اشاره کرد(محدودیت نه الزاما به معنی منفی آن):

1.این نوع از ستون نمی توانند کلید اصلی باشند.

2.بعضی از عملیاتی که از طریق dblink قابل انجام است، با این نوع داده ممکن نخواهد بود(البته بعضی از عملیات).

3.نوع داده LOB توسط جدول کلاستر(منظور object می باشد نه RAC) پشتیبانی نمی شود.

4.ستون از این نوع داده را نمی توان با عبارات و توابع زیر به کار برد:

ORDER BY، ORDER BY، aggregate function، SELECT… DISTINCT، SELECT… UNIQUE، ANALYZE

LOB و انواع آن

LOBها انواع مختلفی دارند که در ادامه در مورد هر یک از آنها، به اختصار مطالبی آورده شده است:

1. CLOB : این نوع از LOBها برای ذخیره متنهای با حجم بالا به کار می روند. همچنین کاراکترست در این نوع از LOBها، همان کاراکترست پیش فرض بانک می باشد.

2. NCLOB: همانند CLOB می باشد که برای ذخیره متن با فرمت national character set بانک، مورد استفاده قرار می گیرد.

3.BLOB: این نوع داده، اطلاعات باینری از قبیل  عکس، صوت، مستندات و .. را شامل می شود.

4.BFILE : در هنگام استفاده از BLOB، اطلاعات در درون بانک اطلاعاتی ذخیره می شوند حال اگر بخواهیم ذخیره اطلاعات در خارج از بانک اطلاعاتی و بر روی سیستم عامل انجام شوند، می توان از این نوع داده استفاده کرد. این نوع از داده محدودیتهای بسیار زیادی را در بر دارند به طور مثال، نمی توانند در بکاپ و ریکاوری سطح بانک شرکت کنند به همین دلیل معمولا داده هایی که اصلاح و بروزرسانی در مورد آنها در موارد شاذ و نادر اتفاق می افتد، از این نوع داده بهره می گیرند.

ساختار LOB

LOBها برای ذخیره و دسترسی به داده، ساختار مشخصی دارند، که برای فهم ساختار آن، نیاز است تا قبلا با اصطلاحاتی آشنا بود:

LOB Segment: حاوی داده اصلی می باشد(همان عکس، pdf، صوت و …).

LOB Locator: اشاره گری است که مشخص می کند LOB segment به صورت فیزیکی در کجا ذخیره شده است این اشاره گر در جدول اصلی(جدولی که مالک LOB می باشد) ذخیره می شود. همانطور که locator bfile به LOB بیرونی(بیرون از بانک ذخیره شده) اشاره دارد locator LOB به LOB درونی(درون بانک ذخیره شده) اشاره می کند حال زمانی که از locator به تنهایی استفاده شود، هر کدام از این دو می توانند مراد باشند.

اطلاعاتی که LOB locator در خود ذخیره می کند، شامل موارد زیر می شود:

– طول فایل

– تعیین اینکه فایل فشرده، رمز گذاری شده، و یا de-duplicated است یا نه(در مدل SecureFile).

LOB inode: کوچکترین واحد تخصیص فضا به lob segment، chunk می باشد(شاید با خانم چوچانگ نسبتی داشته باشند!!!) که هر chunk متشکل از یک یا چند بلاک می باشد حالLOB inode  همه chunkهای متعلق به یک نمونه از LOB را مشخص می کند.

LOB Index: شبیه به ایندکس ساختار b-tree دارد که در برگهایش اشاره گرهایی وجود دارد که به LOB chunk اشاره دارند همچنین اگر پارامتر STORAGE IN ROW غیرفعال شده باشد(که بعدا در مورد ان مطالبی ارائه خواهد شد)، LOB inode هم در درون LOB index ذخیره می شود. البته این نوع از ایندکس بر خلاف ایندکسهای معمولی، قابلیت پشتیبانی از ALTER INDEX، DROP INDEX و Rebuild را ندارد و برای کار با آن، باید از ALTER TABLE استفاده شود. LOB index را می توان در tablsepace مجزا از جدول اصلی و LOB data ذخیره کرد هر چند معمولا لزومی به این کار دیده نمی شود.

در نگاهی کلی تر، برای دسترسی به داده موجود در LOB segment، ابتدا باید به LOB locator که در جدول اصلی موجود است، رجوع شود و از طریق آن به LOB index مربوط به segment رسید و در نهایت هم با توجه به LOB inodeای که در درون LOB index ذخیره شده است، تمامی chunkهای متعلق به LOB مربوطه شناسایی و مورد دستیابی قرار می گیرند.

نکته 1: اسامی LOB segmentها توسط اوراکل ایجاد می شوند(مگر اینکه صریح بیان شوند) و معمولا ساختاری به صورت زیر دارند:

SYS_LOB0000360319C00004$$

SYS_LOB : اسامی LOB segment با این عبارت شروع می شود.

0000360319: برابر با object_id می باشد.

00004: شماره ستون را برای این LOB segment مشخص می کند که در اینجا چهارمین ستون می باشد.

نکته 2: LOB indexها هم در نامگذاری ساختاری مشابه دارند با این تفاوت که اسامی آنها با SYS_IL شروع می شود.

مهاجرت از LONG به LOB:

با توجه به مزیتهایی که برای نوع داده LOB نسبت به LONG و LONG RAW ذکر شد، تغییر نوع داده به LOB تقریبا ضروری به نظر می رسد هر چند امروزه به ندرت می توان در بانک های اطلاعاتی نوع داده LONG و LONG RAW را یافت ولی برای درک دقیق تر تفاوتها، در ادامه به چند روش از نحوه انجام این مهاجرت اشاره خواهد شد.

نکته 1: نوع داده LONG  هنوز در بعضی از جداول data dictionary مورد استفاده قرار می گیرد از قبیل:

COL$, TABPART$, INDPART$, TRIGGER$, VIEW$ and CDEF$

نکته 2: dba_TAB_COLS ویویی است که می توان با آن اسامی همه جداولی که از این نوع داده(LONG، LONG RAW) استفاده می کنند را مشخص کرد:

select * from dba_TAB_COLS  where data_type in (‘LONG RAW’,’LONG’);

روش اول(TO_LOB):

با استفاده از تابع to_lob می توان نوع داده یک ستون را از LONG  یا LONG RAW به LOB تغییر دارد. این کار از طریق create table as و یا insert into …select ممکن خواهد بود. این روش در هنگام استفاده از dblink کاربرد ندارد و همچنین در بلاک PL/SQL قابل استفاده نیست.

insert into usef_convert select name ,family ,TO_LOB(CV) from base;

create table usef_convert as select name ,family ,TO_LOB(CV) from base;

روش دوم(expdp):

از دیگر روشهای تغییر نوع داده، expdp می باشد که باید ابتدا جدول مورد نظر را بدون داده در بانک ساخت و در نهایت داده را به ان جدول اضافه کرد:

expdp directory=us  dumpfile=usef_dp.dmp tables=long_aks

create table aks_convert(ID INTEGER not null,IMG BLOB);

impdp  directory=us  dumpfile=usef_dp.dmp remap_table= long_aks:aks_convert full=y TABLE_EXISTS_ACTION=append

با روش مذکور، تبدیل LOB به LONG هم ممکن خواهد بود.

روش سوم(alter table):

این روش نیاز به فضای موقت دارد و همچنین باید در نظر داشت که بعد از مهاجرت، تریگرهایی که روی این جدول تعریف شدند، ممکن است unusable شوند.

ALTER TABLE oms.TEST  MODIFY (img BLOB ) ;

SecureFile و BasicFile

از اوراکل 11g ویژگی جدیدی به نام SecureFile ارائه شد که برخی از کمبودهای LOB را جبران می کند این ویژگی جدید، نسبت به مدل قبلی LOB که بعدا BasicFile نام گرفت، تفاوتهایی را دارد و بعضی از پارامترهایی که BasicFile  پشتیبانی می کند، توسط SecureFile پشتیبانی نمی شود و بلاعکس. به همین دلیل در ادامه پارامترها و ویژگی های هر کدام از این دو را به طور مجزا بیان می شود.

BasicFile

همانطور که گفته شد، این اسم با ارائه ویژگی جدید برای LOB segment(SecureFile) برای تمییز دادن دو مدل از LOBها، به مدل قدیمی LOB اختصاص یافته است به طوری که در نسخ قبل از 11g، با وجود استفاده از این نوع LOB segment، اصطلاح BasicFile رایج نبود.

مثال از ایجاد جدول با این نوع از LOB segment:

create table usef_tbl( id  number,   img lob) tablespace users lob (img) store as BasicFile (tablespace users  enable storage in row  chunk 4096  pctversion 20  nocache   nologging);

در ادامه هر یک از پارامترهایی که در مدل BasicFile قابل استفاده هستند را به اختصار توضیح خواهیم داد:

— tablespace : تعیین اسم tablespaceای که قرار است Lob segment در آن ذخیره شود.

–– STORAGE IN ROW: در صورتی که storage in row برابر با disable باشد، همیشه lobها با هر اندازه ای که باشند، در خارج از جدول( در lob segment) ذخیره می شوند ولی در صورت enable بودن این پارامتر، اگر حجم lob کمتر از 3964بایت باشد، در جدول اصلی ذخیره می شود و در غیر این صورت، بیرون از جدول ذخیره خواهد شد.

بعد از ایجاد LOB segment، امکان تغییر این ویژگی به تنهایی ممکن نخواهد بود. همچنین مقدار پیش فرض برای این پارامتر،enable  می باشد.

نکته 1: در صورتی که lobای با اندازه کمتر از 3K که در خارج از جدول اصلی اش ذخیره شده(Out-of-Line) را به جدولی که ENABLE STORAGE IN ROW می باشد منتقل کنیم، کماکان حالت قبلی اش را حفظ می کند(یعنی به صورت Out-of-Line ذخیره خواهد شد).

نکته 2: معمولا برای حفظ کارایی بانک اطلاعاتی، پیشنهاد می شود که این ویژگی  فعال باشد.

— chunk : همانظور که قبلا گفته شد، کوچکترین واحد تخصیص فضا به lob segment، chunk می باشد که هر chunk متشکل از یک یا چند بلاک می باشد.

تعیین درست اندازه chunk می تواند بر روی کارایی LOB segment موثر باشد اندازه بهینه chunk با میانگین اندازه نمونه lobها رابطه نزدیکی دارند به طور مثال اگر اندازه هر chunk را برابر با32KB  باشد، در صورتی که متوسط اندازه هر lob برابر با 7KB باشد، هدر رفتگی فضا به اندازه 27KB به وجود خواهد آمد پس مقدار بهینه chunk برای این قبیل LOBها، برابر با 8KB می باشد. همین طور اگر حجم chunk کوچک در نظر گرفته شود، نیاز به چند chunk برای یک نمونه lob خواهیم داشت که باز هم بهینه نخواهد بود.

نکته 1: اندازه chunk برای lobای که در درون جدول اصلی ذخیره می شوند، اهمیتی ندارد.

نکته 2: حداکثر اندازه chunk در BasicFile برابر با 32K می تواند باشد و این مقدار در SecureFile متغیر می باشد و می تواند تا اندازه 64M هم برسد.

نکته 3: برای تغییر اندازه chunk مربوط به یک lob segment، می توان از دستور زیر استفاده کرد:

alter table usef_tbl move lob(TASVIR) store as TASVIR (chunk 32K);

— PCTVERSION :  زمانی که بروزرسانی بر روی large object انجام می شود، در LOB segment،  chunk جدیدی به نسخه بروزشده اختصاص داده می شود و نسخه قدیمی این LOB، به همان صورت در LOB segment حفظ می شود(برخلاف segmentهای دیگر که از undo استفاده می کنند) یعنی به chunk قدیمی تا مدت زمانی که هر یک از دو پارامتر pctversion یا retention مشخص کرده اند، دست زده نمی شود و تنها اشاره گر به chunk جدید اشاره می کند.

نتیجه اینکه، pctversion مشخص می کند که چند درصد از کل فضای LOB segment برای نگهداری نسخه های قدیمی LOBها استفاده شود که مقدار پیش فرض آن برابر با 10 می باشد.

نکته 1: در صورتی که مدیریت undo بانک به صورت manual باشد، pctversion گزینه پیش فرض برای این نوع از Lobها(BasicFile) خواهد بود.

نکته 2: برای LOB index page همیشه و تحت هر عنوانی از undo tablespace استفاده می شود که حجم بسیار کمی هم مصرف می کند ولی برای LOB data از خود lob برای مدیریت undo استفاده می شود.

نکته 3: برای LOBهایی که بروزرسانی بر روی انها به کررات انجام می شود، معمولا پیشنهاد می شود که این درصد به 20 افزایش یابد و در صورتی که تنها برای یکبار LOB بروز رسانی می شود، حتی 5 درصد هم کافی بنظر می رسد.

نکته 4: اگر کاربری بخواهد به داده های قدیمی یک lob دسترسی پیدا کند ولی آن داده به دلیل کمبود فضا بازنویسی شده باشد، کاربر با خطای snapshot too old مواجه می شود.

نکته 5: برای رفع مشکل snapshot too old، باید pctversion را افزایش داد:

alter table usef_tbl move lob(TASVIR)  store as TASVIR (pctversion 20);

— RETENTION : این پارامتر جایگزینی برای پارامتر PCTVERSION می باشد با این تفاوت که این پارامتر به جای تعیین درصدی از فضای LOB segment، مشخص می کند که چه مدت زمانی باید تصویر قبلی LOB segment حفظ شود. در صورتی که مدیریت undo بانک به صورت اتوماتیک مدیریت شود، retention گزینه پیش فرض برای Lobها خواهد بود(البته در SecureFile، تنها از پارامتر retention استفاده می شود). حال برای تعیین این که، کدام LOB segmentها از چه پارامتری استفاده می کنند، دستور زیر مفید می باشد:

select owner,table_name,segment_name,nvl(to_char(pctversion),’NULL’) pctversion,nvl(to_char(retention),’NULL’) retention from dba_lobs where owner=’OMS’;

مقدار پارامتر retention بر اساس undo_retention مشخص می شود.

نکته 1: برای تغییر این مقدار باید ابتدا یکبار pctversion را برای LOB segment تنظیم کرد و دوباره پارامتر retention را برای آن lob segment در نظر گرفت که البته مقدار آن برابر با مقدار جاری undo_retention خواهد شد:

alter table USEF_tbl modify lob(TASVIR) (retention);

— CACHE: در صورتی که دسترسی به LOB segmentای به کررات انجام می شود، با پارامتر cache می توان استفاده از cache را برای read و Write این LOB segment ممکن کرد.

alter table usef_tbl modify  lob(tasvir) (cache);

— NOCACHE : در این حالت lobها مستقیما از دیسک خوانده و مستقیما بر روی دیسک نوشته می شوند. یعنی بلاکهای مربوطه در بافرکش، کش نمی شوند. به همین دلیل شاید رویداد direct path read/write هم بیشتر در بانک مشاهده شود.

alter table usef modify  lob(tasvir) (nocache);

— CACHE READS : در این حالت lobها در هنگام خواندن به بافر آورده می شوند ولی در هنگام نوشتن مستقیم روی دیسک نوشته می شود. دستور زیر این پارامتر را برای LOB segment مورد نظر تنظیم می کند:

alter table usef_tbl modify  lob(tasvir) (cache reads);

— nologging: در صورتی که بخواهیم به ازای تغییرات lob segmentها، هیچگونه redoای ایجاد نشود، باید از دستور زیر استفاده کنیم:

alter table usef_tbl modify  lob(tasvir) (nocache nologging);

در این صورت اگر به ریکاوری اطلاعات مربوط به LOBها نیاز داشته باشیم، قسمتهایی از این LOB segment به عنوان corrupt علامت خواهند خورد.

زمانی که LOB segment به صورت nologging هست، کنترل فایل باید مرتبا بروزرسانی شود که منجر به رخ دادن رویداد انتظار controlfile parallel read and write در سیستم خواهد شد برای جلوگیری از بروزرسانی کنترل فایل، می توان از رویداد 10359 در سطح 1 استفاده کرد.

— logging: Inline LOB ها و LOB indexeها همیشه redo ایجاد می کنند و انتخاب تنها بر سرout-of-line LOBها می باشد که با این پارامتر می توان آنها را مدیریت کرد.

نکته 1: برای همه LOB segmentهایی که پارامتر cache آنها فعال است، لاگ گیری هم باید فعال باشد.

و برای فعال کردن logging باید:

alter table usef_tbl modify  lob(tasvir) (cache logging);

SecureFile

در ابتدا باید در نظر داشت که نسخه بانک باید حداقل برابر با 11.0.0.0 باشد تا بتوان از SecureFile استفاده کرد. ساختار ایجاد جدول با این نوع از LOB segment به صورت زیر می باشد:

CREATE TABLE <[schema_name.]<table_name> (<column_name> <data_type>,LOB (<lob_column_name>) STORE AS SECUREFILE <lob_column_name> (TABLESPACE <SecureFile_tablespace_name> <DISABLE | ENABLE> STORAGE IN ROW CHUNK <integer> RETENTION MIN <integer> [<DEDUPLICATE | KEEP_DUPLICATES>] [<COMPRESS <HIGH | MEDIUM | LOW> | NOCOMPRESS>] <[DECRYPT ENCRYPT>] [<CACHE READS | NOCACHE>]) TABLESPACE <tablespace_name>;

و در ادامه مثالی برای SecureFile آورده شده است:

CREATE TABLE secure_file_tab (id  NUMBER(5),img BLOB) LOB (img)  STORE AS SECUREFILE tasvir (TABLESPACE uwdata DISABLE  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 KEEP_DUPLICATES NOCOMPRESS DECRYPT CACHE READS);

در ادمه پارامترها و ویژگی های مربوط به SecureFile مورد بررسی قرار گرفته است(البته به پارامترهای مشترک آن با BasicFile اشاره ای نخواهد شد).

— Automatic Deduplication: با این ویژگی، به صورت اتوماتیک تمامی نمونه lobهایی که در یک segment تکراری هستند شناسایی می شوند و در نهایت تنها یک نسخه از آن در segment lob نگهداری می شود. این کار باعث کاهش حافظه مصرفی و تولید کمتر undo و redo می شود.

در صورت فعال بودن این ویژگی، در هنگام درج یک نمونه LOB جدید، ابتدا hash value آن محاسبه می شود و پس از محاسبه و مقایسه در صورت وجود، تنها hash value ثبت می شود و در غیر این صورت باید مقدار جدید هم به lob storage اضافه شود.

اگر سگمنتی در حالت deduplication نباشد، می توان با دستور زیر LOB segment را به این حالت برد:

alter table  usef  modify lob(TASVIR) (deduplication) ;

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

alter table usef modify lob(TASVIR) (keep_duplicates);

— Intelligent Compression: برای کاهش حجم مصرفی LOB segmentها، این ویژگی بسیار موثر  می باشد. این پارامتر دو سطح از فشرده سازی را ممکن می سازد: MEDIUM که مقدار پیش فرض می باشد و HIGH. همچنین منظور از LOW همان MEDIUM می باشد.

همانطور که از کلمه high بر می آید، این روش نیاز به مصرف cpu بیشتری دارد و در نتیجه LOB segment هم حجم مصرفی کمتری را به خود اختصاص می دهد.

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

alter table usef modify lob(TASVIR) (compress MEDIUM );

alter table usef modify lob(TASVIR) ( compress high);

alter table usef modify lob(TASVIR) ( nocompress);

همچنین فشرده سازی LOB segment با تبدیل BasicFile به SecureFile به صورت زیر قابل انجام می باشد(به صورت موازی و با چهار پروسس):

ALTER TABLE usef_tbl move lob (TASVIR) store AS  SECUREFILE TASVIR (TABLESPACE USERS COMPRESS MEDIUM) parallel 4;

—  Transparent Encryption: قابلیت رمزنگاری با استفاده از TDE را برای LOB segment هم ممکن می کند. الگوریتمهای AES128،  AES192، AES256  و  3DES168 با استفاده از این ویژگی قابل استفاده می باشند. دستور زیر رمزنگاری را برای lob segment مربوط به جدول usef_tbl ممکن خواهد کرد:

alter table usef_tbl modify lob(img)  (encrypt using ‘AES128′);

ALTER TABLE t1 REKEY USING ‘3DES168’;

همچنین برای استفاده از این ویژگی باید ابتدا یک wallet ساخته شود. برای استفاده از wallet باید فایل sqlnet.ora به شکل زیر تنظیم شود.

 ENCRYPTION_WALLET_LOCATION=

         (SOURCE=

             (METHOD=FILE)

             (METHOD_DATA=

                (DIRECTORY= /u01/oracle/wallet)

           )

      )

همچنین دو دستور زیر هم در این زمینه استفاده می شوند:

alter system set encryption key authenticated by “mypass”;

alter system set encryption wallet open identified by “mypass”

— MAXSIZE: حداکثر اندازه LOB segment را می توان با این پارامتر مشخص کرد.

—RETENTION: در SecureFile به این پارامتر می توان مقدارهای زیر را نسبت داد:

–MIN: نسخه های قدیمی تا حداقل مقدار زمانی که این پارامتر مشخص می کند، نگه داشته شوند(بر اساس ثانیه). به مثال زیر توجه کنید:

RETENTION MIN 3600

– AUTO: مدت زمان نگهداری نسخه های قدیمی بطور اتوماتیک مشخص شود(پیش فرض AUTO می باشد).

– none: بانک اطلاعاتی تا جایی که ممکن است، نسخه های قدیمی را نگهداری می کند.

–MAX: تنها در صورتی که اندازه segment به اندازه پارامتر maxsize برسد، نسخه قدیمی را استفاده مجدد می کند.

ایجاد جدول به صورت ترکیبی

در بانک اطلاعاتی نسخه 11g، جداولی که ستونی از نوع داده LOB را در خود دارند، به طور پیش فرض به صورت BasicFile ایجاد می شوند. حال برای پیش فرض شدن SecureFile، باید از پارامتری به نام db_securefile استفاده کرد، این پارامتر می تواند مقادیر زیر را بگیرد:

Permitted: با تنظیم این مقدار، lob segment به صورت BasicFile ذخیره می شود مگر اینکه SecureFile  علنا بیان شود. مقدار پیش فرض برای پارامتر db_securefile برابر با  Permittedمی باشد.

Always: همیشه SecureFile انتخاب می شود مگر اینکه BasicFile بیان شود.

Force: همیشه SecureFile انتخاب می شود.

Ignore: همیشه SecureFile انتخاب می شود ولی با آن همانند BasicFile برخورد می شود.

Never: در صورتی که SecureFile انتخاب شود، خطا رخ می دهد.

همچنین امکان استفاده همزمان از BasicFile و SecureFile در هنگام ایجاد یک جدول وجود دارد که در مثال زیر، این نکته در نظر گرفته شده است:

CREATE TABLE usef_tbl

   (id      NUMBER PRIMARY KEY,

    name      VARCHAR2(256),

    doc   CLOB DEFAULT EMPTY_CLOB(),

    cv    CLOB DEFAULT EMPTY_CLOB(),

    aks BLOB DEFAULT EMPTY_BLOB(),

    asare_angosht BFILE)

LOB(doc) STORE AS SecureFile doc_seg(

    TABLESPACE USERS

    ENABLE STORAGE IN ROW    

    CACHE)

LOB(cv) STORE AS SecureFile cv_seg(

    TABLESPACE  USERS

    DISABLE STORAGE IN ROW

    NOCACHE

    FILESYSTEM_LIKE_LOGGING)     

LOB(aks) STORE AS BasicFile aks_seg(

    TABLESPACE USERS

    DISABLE STORAGE IN ROW

    CHUNK 32768      

    CACHE READS);

مهاجرت به SecureFile:

روشهای مختلفی برای مهاجرت از BasicFile به SecureFile وجود دارد که در ادامه بعضی از این روشها مورد بررسی قرار خواهد گرفت.

1. ایجاد ستون جدید با نوع SecureFile و بروزرسانی این ستون با ستون از نوع BasicFile و در نهایت حذف ستون از نوع BasicFile.

alter table TEST6 add aks2 blob  LOB (AKS2)  STORE AS SECUREFILE aks2 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE COMPRESS high DECRYPT CACHE READS);

begin

 for i in(select id,aks from test6) loop

 update test6 set aks2=(select i.aks from test6 where id=i.id) where id=i.id ;

 end loop;

 commit;

 end;

2. Export/Import:

expdp directory=usef_dir dumpfile=convert.dmp tables=blob1

create table usef_tbl ( ID  NUMBER(9),  AKS BLOB default EMPTY_BLOB(),  RES CLOB default EMPTY_CLOB() )  LOB (AKS)  STORE AS SECUREFILE aks (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE    COMPRESS high DECRYPT CACHE READS), LOB (res)  STORE AS SECUREFILE res (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE   COMPRESS high DECRYPT CACHE READS) ;

impdp directory=usef_dir dumpfile=convert.dmp remap_table=blob1:usef_tbl TABLE_EXISTS_ACTION=APPEND

3.CATS:

create table usef_tbl2  LOB (AKS)  STORE AS SECUREFILE aks_2 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE    COMPRESS high DECRYPT CACHE READS), LOB (res)  STORE AS SECUREFILE res_2 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE   COMPRESS high DECRYPT CACHE READS)   as select * from blob1;

4.INSERT INTO … SELECT …:

create table usef_tbl3 ( ID  NUMBER(9),  AKS BLOB default EMPTY_BLOB(),  RES CLOB default EMPTY_CLOB() )  LOB (AKS)  STORE AS SECUREFILE aks_3 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE    COMPRESS high DECRYPT CACHE READS), LOB (res)  STORE AS SECUREFILE res_3 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE   COMPRESS high DECRYPT CACHE READS) ;

insert into usef_tbl3 select * from blob1;

5.Online table redefinition:

 این روش downtime را به حداقل می رساند و نیز نیاز به فضای اضافه دارد. در ادامه مراحل انجام این کار اورده شده است:

1.در ابتدا امکان سنجی انجام می شود:

exec dbms_redefinition.can_redef_table (‘USEF’, ‘BLOB1’);

2.جدولی با ساختار جدول موجود می سازیم:

create table usef_tbl5  LOB (AKS)  STORE AS SECUREFILE aks_5 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE    COMPRESS high DECRYPT CACHE READS), LOB (res)  STORE AS SECUREFILE res_5 (TABLESPACE users enable  STORAGE IN ROW CHUNK 8192 RETENTION MIN 3600 DEDUPLICATE   COMPRESS high DECRYPT CACHE READS)  

as select * from blob1 where 1=2;

3.با دستور زیر، اطلاعات وارد جدول جدید می شوند:

exec dbms_redefinition.start_redef_table (‘USEF’, ‘BLOB1’, ‘USEF_TBL5’);

4.در انتها برای سوییچ بین دو جدول و نیز برای همسان سازی نهایی، از دستور زیر استفاده می شود:

exec dbms_redefinition.finish_redef_table(‘USEF’, ‘BLOB1’, ‘USEF_TBL5’); 

همچنین جدول USEF_TBL5 در انتهای کار، قابل حذف می باشد.

6.alter table…move

روش دیگر برای مهاجرت به SecureFile، استفاده از alter table…move می باشد:

ALTER TABLE usef_tbl move lob (TASVIR) store AS  SECUREFILE TASVIR (TABLESPACE USERS COMPRESS MEDIUM) parallel 4;

ویژگی های جدید SecureFile در 12c

1. parallel DML: در نسخه 11g برای LOB segmentای که در جدول پارتیشن وجود داشته باشد، امکان parallel DML وجود دارد و در نسخه 12c، این امکان برای جداول پارتیشن نشده هم به وجود آمد(البته برای SecureFile).

alter session force parallel dml;

insert into usef.sec_lob select * from usef.aks;

2. پیش فرض شدن SecureFile برای ذخیره سازی LOB: با آمدن 12c پارامتر db_securefile مقدار پیشفرض PREFERRED به خود گرفت تا هر Lob segmentای که ساخته می شود، به صورت SecureFile باشد.

3. وقتی که جدولی بااستفاده ازdatapump به اوراکل 12cمنتقل می شود، lobsegment آن رامی توان به صورت SecureFile ایجاد کرد:

TRANSFORM=LOB_STORAGE:SECUREFILE|BASICFILE|DEFAULT|NO_CHANGE

با تنظیم این پارامتر به SecureFile می توان به این هدف رسید:

impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp TRANSFORM=LOB_STORAGE:SECUREFILE

عملیاتی بر روی LOBها

*کاهش فضا و یکپارچه سازی LOB segment

 برای LOB segmentها این امکان وجود دارد تا بتوان فضایی که مورد استفاده آنها قرار نمی گیرد را باز پس گرفت. قبل از این کار باید دو نکته را در نظر گرفت:

اولا نیاز به فعالسازی Row movement نیست.

ثانیا این کار باعث ایحاد آرشیولاگ بسیار زیادی می شود.

همچنین گزینه ای به نام CASCADE در اوراکل 10R2 اضافه شد که سبب می شود علاوه بر کاهش حجم جدول اصلی، حتی LOB segmentها هم shrink شوند.

نکته: دستورات مربوط به shrink برای secureFile قابل استفاده نمی باشند.

دستورات زیر در این زمینه مفید می باشند:

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(TASVIR))),0) AS BYTES FROM usef_TBL;

ALTER TABLE usef_TBL MODIFY LOB (TASVIR) (SHRINK SPACE CASCADE );

*تغییر نام و جابجایی LOB segment

برای تغییر نام LOB segment، می توان ان را جابجا کرد در حین این کار، می توان tablespace جدیدی را هم برای LOB segment انتخاب کرد:

alter table usef_TBL move lob(TASVIR) store as AKS;

alter table usef_TBL move lob(TASVIR)  store as AKS(tablespace USEF_TBS);

DBMS_LOB و کار با LOB segment

هر سلول از ستونی که نوع داده آن LOB باشد، می تواند در یکی از سه وضیعت زیر قرار داشته باشد:

— null: رکورد مربوطه(فیلدی که از نوع LOB می باشد)، هیچ locatorای ندارد و مقدار دهی اولیه نشده است.

—  EMPTY : با استفاده از توابع OCI  یا  DBMS_LOBامکان دستگاری و درج روی رکوردی که locator ندارد، ممکن نیست(چون این توابع تنها با locator کار می کنند) در این صورت می توان با استفاده از این ویژگی، به رکورد مربوطه locatorای داد که به جایی اشاره نمی کند و همچنین پتانسیل گرفتن مقدار را هم دارد. برای نوع داده CLOB از EMPTY_CLOB() و برای BLOB هم از EMPTY_BLOB() استفاده می شود.

insert into usef.blob1 values(1,EMPTY_BLOB(),EMPTY_CLOB());

در صورتی که دستور زیر را صادر کنیم، برای رکوردهای EMPTY جوابی را بر نمی گرداند:

SELECT COUNT (*) FROM usef.blob1 WHERE ad_graphic IS NULL;

دلیل این موضوع همانطور که گفته شد، به تفاوت null و empty برمی گردد. حداقل اندازه locator برابر با 36 بایت می باشد.

— populated: در این حالت، locatorای وجود دارد که در حال اشاره به داده واقعی با طولی غیرصفر می باشد.

زمانی که می خواهیم اطلاعاتی را  در LOB segment ثبت کنیم، می توانیم locator مربوطه را بگیریم و داده مورد نظر را ثبت کنیم این کار هم می تواند از طریق select انجام شود و هم امکان ان با استفاده از عبارت return ممکن خواهد بود:

  INSERT INTO usef.blob1(aks, res)  VALUES (empty_blob(), ‘test1’)  RETURN aks INTO l_blob;

در مثال بالا، ابتدا فیلد مورد نظر با EMPTY پر شده و locator مورد نظر به متغیری ارجاع داده شده است. در نهایت هم می توان با چنین دستوری این فیلد را با مقداری پر گرد:

  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));

هنگام استفاده از DBMS_LOB، برای کار با فایلها، باید ابتدا ان فایل را با تابع fileopen باز کرد حال اگر تعداد فایلهای باز با استفاده از این تابع از حدی خاص فراتر برود، با خطا مواجه خواهیم شد برای کنترل و تعیین حداکثر تعداد فایلهای باز، می توان از پارامتر SESSION_MAX_OPEN_FILES بهره گرفت معمولا مقدار این پارامتر برابر با 10 می باشد. برای جلوگیری از این خطا بهتر است هر فایل باز شده ای را در پایان کار با استفاده از تابع FILECLOSE بست.

برای حذف یک LOB ذخیره شده در بانک می توان از همان دستورهای رایج(DROP TABLE، DELETE،  TRUNCATE TABLE، DROP TABLESPACE) استفاده کرد:

ALTER TABLE <table_name> DROP COLUMN <column_name>;

ALTER TABLE lobtab DROP COLUMN lobcol;

افزودن فایل به BFILE LOB

فایلهای موجود در OS را می توان از طریق دایرکتوری(Directory) به اوراکل شناساند. برای ایجاد دایرکتور، از دستور زیر بهره می گیریم:

create directory usef_dir as ‘/u01/lob’;

همچنین جدولی را برای درج این فایل ایجاد می کنیم:

create table lob2(id number(9), aks  bfile);

با تابع BFILENAME می توان آدرس فایل موجود در سیستم عامل را به بانک شناساند.

insert into lob2 values(1,bfilename(‘usef_dir’,’aks_usef.jpg’));

همچنین برای بروز رسانی می توان از دستور زیر استفاده کرد:

update lob2 set aks=bfilename(‘usef_dir’,’aks_usef.jpg’) where id=1;

نکته 1: برای تعیین اندازه نمونه LOBها، از تابع getlength استفاده می شود:

SELECT id, DBMS_LOB.GETLENGTH(l.aks) Length FROM usef_tbl l;

نکته 2: بررسی شود که آیا فایل مورد نظر موجود است یا خیر؟

dbms_output.put_line(x.id||’    ‘||case dbms_lob.fileexists(a) when 1 then ‘FILE IS EXIST ‘ end);

درج  فایل در BLOB

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

در ابتدا جدولی با ساختار مورد نظر می سازیم:

create table usef.blob1(

id number(9),

aks blob DEFAULT  EMPTY_BLOB(),

res clob DEFAULT  EMPTY_CLOB()

)

سپس از طریق بلاک PL/sqlای زیر، فایل مورد نظر را از طریق DBMS_LOB به داخل جدول منتقل می کنیم:

create directory usef_dir as ‘/u01/lob’;

 

DECLARE

  l_bfile  BFILE;

  l_blob   BLOB;

BEGIN

  INSERT INTO usef.blob1(id,aks, res)  VALUES (1,empty_blob(), ‘test1’)  RETURN aks INTO l_blob;

  l_bfile := BFILENAME(‘USEF_DIR’, ‘USEF_AKS.gif’);   —     ==>اسم پوشه و نام فایل

  DBMS_LOB.fileopen(l_bfile, Dbms_Lob.File_Readonly);

  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));

  DBMS_LOB.fileclose(l_bfile);

  COMMIT;

END;

آپلود فایل از طریق SQL*loader:

با مثال زیر در چهار مرحله، انبوهی از فایلها را به صورت یکجا وارد بانک اطلاعاتی می کنیم:

مرحله یک) در ابتدا جدول زیر را می سازیم:

CREATE TABLE photoalbum (photolob BLOB);

مرحله دوم) اسامی همه فایلهایی که می خواهیم آپلود کنیم را در فایلی ثبت می کنیم(در لینوکس):

[oracle@db18 ~]$   ls   /u01/lob/ >>/home/oracle/photo.txt

مرحله سوم) کنترل فایلی را برای SQL*Loader می سازیم تا بتوان داده را به بانک اپلود کرد:

[oracle@db18 ~]$ vi  /home/oracle/usefphoto.ctl

load data

infile ‘/home/oracle/photo.txt’

 into table photoalbum

(ext_fname filler char(200),photolob lobfile(ext_fname)

terminated by EOF)

مرحله چهارم) اجرای SQL*Loader :

[oracle@db18 ~]$ cd /u01/lob/

[oracle@db18 lob]$ sqlldr usef/abc  control=/home/oracle/usefphoto.ctl

 استخراج فایل از BLOB

برای استخراج فایل از جدول(از طریق PL/sql)، ابتدا نیاز هست تا directoryای ساخته شود تا فایل از بانک اطلاعاتی به آنجا منتقل شود:

CREATE OR REPLACE DIRECTORY AKS AS ‘/u01/lob’;

سپس با استفاده از بلاک pl/sqlای زیر، می توان فایل را از بانک اطلاعاتی به سیستم عامل استخراج کرد:

DECLARE

  l_file      UTL_FILE.FILE_TYPE;

  l_buffer    RAW(32767);

  l_amount    BINARY_INTEGER := 32767;

  l_pos       INTEGER := 1;

  l_blob      BLOB;

  l_blob_len  INTEGER;

BEGIN

  — Get LOB locator

   SELECT tasvir  INTO l_blob    FROM usef_t    WHERE name =’1′;

  l_blob_len := DBMS_LOB.getlength(l_blob);

    — Open the destination file.

  l_file := UTL_FILE.fopen(‘AKS’,’USEF_AKS.jpg’,’w’, 32767);

  — Read chunks of the BLOB and write them to the file

  — until complete.

  WHILE l_pos < l_blob_len LOOP

    DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer);

    UTL_FILE.put_raw(l_file, l_buffer, TRUE);

    l_pos := l_pos + l_amount;

  END LOOP;

    — Close the file.

  UTL_FILE.fclose(l_file);

  EXCEPTION

  WHEN OTHERS THEN

    — Close the file if something goes wrong.

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE;

END;

ایندکس گذاری بر روی LOB

در جدولی که XML، word، HTML و … ذخیره شده است(بطور کلی LOB)، جستجو در متون این مستندات به روش معمول ممکن نخواهد بود برای جستجو در این نوع داده می توان از Context Index استفاده کرد(البته context index تنها به نوع داده LOB محدود نمی شود و می توان آن را بر روی نوع داده دیگر به مثل varchar2 هم ایجاد کرد ). البته قبل از استفاده از این قابلیت باید کامپوننت oracle text بر روی بانک موجود باشد که برای بررسی آن، می توان از dba_registry استفاده کرد. همچنین در صورتی که این کامپوننت موجود نبود، می توان آن را از طریق dbca و یا اسکریپتهای موجود در زیر پوشه $ORACLE_HOME/rdbms/admin به بانک اضافه کرد.

نکته 1: کامپوننت oracle text علاوه بر Context Index سه نوع دیگر از ایندکس به نامهای CTXCAT ، CTXRULE و Ctxxpath  را هم ارائه می کند.

نکته 2: بعضا استفاده از عبارات زیر، در دستور select، باعث ممانعت از استفاده ایندکس می شود که می توان از context index برای رفع این مشکل استفاده کرد تا مجبور به استفاده از این عملگرها نشد:

IS NULL ،  <> ،  !=   ، !>  ، !<  ، NOT،   NOT EXISTS  ، NOT IN  ، NOT LIKE،  LIKE ‘%usef’

برای مشاهده مشخصات ایندکسهای context ، می توان از پرس و جوی زیر استفاده کرد:

select * from   dba_indexes where ityp_owner = ‘CTXSYS’;

در ادامه با ذکر مثالی، این نوع از ایندکس مورد بررسی قرار می گیرد.

create table usef_tbl_ci(id number, doc clob);

با توجه به محدودیت های متن(منظور مقاله ای که در حال مطالعه آن هستید)، مجبور هستیم از PDF، word و فایلهای دیگر استفاده نکنیم و تنها به یک مثال ساده بسنده کنیم.

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

insert into usef_tbl_ci values(1,’من dba هستم ‘);

insert into usef_tbl_ci values(2,’پرسپوليس سرور استقلا است’);

insert into usef_tbl_ci values(3,’مسي از رونالدو بهتر است’);

insert into usef_tbl_ci values(4,’خدا بزرگ است’);

دستور زیر ایندکسی که با هر commit بروز می شود را بر روی جدول usef_tbl_ci ایجاد می کند:

create index usef_index1 on usef_tbl_ci(doc) indextype is ctxsys.context PARAMETERS (‘SYNC (ON COMMIT)’);

حال برای جستجو یک عبارت خاص، باید از عبارت contains در کنار where استفاده کرد:

select * from usef_tbl_ci  where contains (doc,’است‘)>0;

حتی می توان جستجوی ترکیبی هم داشت:

select * from usef_tbl_ci  where contains (doc,’است‘)>0 and id=2;

عبارت >0 به score و امتیازی اشاره دارد که عبارت مورد جستجو با توجه به متن موجود به خود اختصاص داده است یعنی بیان می کند که اگر امتیاز عبارت مورد جستجو بیشتر از صفر بود، رکوردهای حاوی این عبارت را برگرداند.

score در context index شماره ای بین صفر تا 100 می باشد که متناسب به تنوع یک عبارت در مستندات مختلف، به آن عبارت داده می شود به طور مثال اگر عبارتی در  یک مستند 34 بار تکرار شود، نمره 100 خواهد گرفت. همچنین تکرار یک عبارت در یک مستند خاص نمره بیشتری از تکرار آن در چند مستند خواهد داشت. مثال زیر را در نظر بگیرید:

insert into usef_tbl_ci values(1,‘i am hossein .my teacher is usef’,’hossein’);

insert into usef_tbl_ci values(2,‘usef is a oracle dba’,’usef’);

insert into usef_tbl_ci values(3,‘ali and usef are friend. ali is a boy.’,’ali’);

همانطور که در دستورات بالا مشخص است، کلمه usef در همه مستندات موجود است(یعنی سه بار تکرار شده است) و کلمه ali در یک مستند دو بار تکرار شده است و طبق الگوریتم باید از usef امتیاز بیشتری داشته باشد. پرس و جوی زیر این نکته را نشان خواهد داد:

select name, score(55)+score(66)+score(77) as emtiaz from usef_tbl_ci a  where contains(doc, ‘ali’, 55) >0 or contains(doc, ‘usef’, 66)>0 or contains(doc, ‘hossein’, 77)>0  order by emtiaz  desc;

NAME	EMTIAZ
ali	12
hossein	7
usef	3

جدول زیر نشان می دهد که با داشتن x مستند در یک ستون، چند بار تکرار برای یک عبارت، امتیاز را برای آن برابر با 100 خواهد کرد:

تعداد مستندات	تعداد رخداد
1	34
5	20
10	17
50	13
100	12
500	10
1,000	9
10,000	7
100,000	6
1,000,000	5

در ادامه با مثالی سعی بر تبین بهتر این جدول داریم.

ابتدا جدولی برای انجام تست ایجاد می کنیم:

create table usef_tbl_2(id number, doc clob);

با دستور زیر کلمه ‘usef’ را با 34 بار تکرار در جدول usef_tbl_2 درج می کنیم:

insert into usef_tbl_2 values(1,rpad(‘usef’,170,’ usef’));

سپس ایندکسی روی ستون doc ایجاد می کنیم:

create index new on  usef_tbl_2(doc) indextype is  ctxsys.context;

در نهایت جستجو را با دستور زیر انجام می دهیم تا امتیاز عبارت usef را با داشتن یک سند بیابیم:

select score(9), a.* from usef_tbl_2 a where contains (doc,’USEF’,9)>0; ==>score=100

همانطور که در خروجی قابل رویت است، با توجه به این که در یک مستند موجود، عبارت usef بیش از 34 بار تکرار شده است پس نمره آن برابر با 100 خواهد شد.

حال اگر تعداد  تکرار کلمه ‘usef’ را به 33 کاهش دهیم، score برابر با عدد دیگری خواهد شد و نمره دیگر صد نخواهد بود:

select score(8), a.* from usef_tbl_2 a where contains (doc,’USEF’,8)>0; ==>score=99

 

نکته 1: منظور از عدد 8 در دستور، تنها یک برچسب می باشد و در صورتی کاربرد دارد که چند بار عبارت contains در یک دستور بکار رفته باشد تا از این طریق، نمره هر کدام از عبارتها مشخص شود.

نکته 2:در صورتی که بر روی ستون LOB ایندکسی از نوع context ایجاد نشده باشد و بخواهیم از طریق contains جستجویی را روی آن ستون انجام دهیم، با خطای زیر مواجه خواهیم شد:

ORA-20000: Oracle Text error:

DRG-10599: column is not indexed

عملگرها و context index

در مثال اول، کلمه “است” مورد جستجو قرار گرفته است، حال اگر لازم باشد تا در کنار یافتن کلمه “است”، شرایط دیگری هم برقرار باشد، می توان از عملگرها بهرمند شد. برای مثال پرس و جوی زیر رکوردهایی که عبارت “است” و عبارت “بزرگ” را با هم دارند، برمی گرداند:

select * from usef_tbl_ci  where contains (doc,’است  and  بزرگ’)>0  ;

عملگرهای مختلفی در این زمینه وجود دارد که در ادامه به بعضی از آنها اشاره ای خواهیم کرد.

عملگر AND (&)

این عملگر هر سطری که همه عبارتها را در خود دارد، نمایش می دهد. از بین امتیازهایی که عبارتها به خود اختصاص داده اند، این عملگر، کمترین امتیاز را بر می گرداند:

insert into usef_tbl_ci values(1,’Oracle Database 11g Enterprise Edition Release Release’);

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release’,9)>0; ==> score=9

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Edition’,9)>0;==> score=4

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release  and Edition‘,9)>0;==> score=4

عملگر OR (|)

هر سطری که حداقل یکی از عبارتها را در خود داشته باشد، در خروجی دیده می شود. از بین امتیازهایی که عبارتها به خود اختصاص داده اند، این عملگر، بیشترین امتیاز را بر می گرداند:

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release  and Edition‘,9)>0;==> score=9

 عملگر NOT (~)

زمانی که در جستجوی عبارتی هستیم و در عین حال نمی خواهیم کلمه مشخصی هم در خروجی وجود داشته باشد، می توانیم از عملگر NOT (~) استفاده کنیم.

مثال زیر همه سطرهایی که دارای عبارت “weights” و فاقد عبارت “Release” هستند، را بر می گرداند:

select score(9),a.* from usef_tbl_ci a where contains (doc,’ weights ~ Release’,9)>0; ==> score=5

عملگر MINUS (-)

طبق مثال زیر، این عملگر رکوردهایی که کلمه “Release ” در آنها بیشتر از کلمه “Edition” امتیاز دارد، در خروجی نشان داده می دهد.

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release’,9)>0; ==> score=9

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Edition’,9)>0;==> score=4

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release  –  Edition‘,9)>0;==> score=4

عملگر EQUIValence (=)

کلمه قابل قبولی را برای جایگزینی با یک کلمه دیگر تعیین می کند و امتیاز نهایی، جمع  امتیاز بین این دو خواهد بود. در مثال زیر، کلمه Edition هم به جای کلمه release قابل قبول خواهد بود.

select score(9),a.* from usef_tbl_ci a where contains (doc,‘Release  =  Edition’,9)>0;

عملگر NEAR (;)

این عملگر بر اساس مجاورت و نزدیکی دو عبارت در متن، نمره و امتیاز می دهد به طور مثال اگر دو عبارت در متن به هم نزدیک باشند، امتیاز بیشتری می گیرند و اگر از هم دور باشند، امتیاز کمتری برخواهد گشت در مثال زیر، در مجاورت کلمه weights دو بار کلمه different  آمده است که سبب می شود تا امتیاز مجاورت آن به different نسبت به کلمه terms بیشتر شود:

You can assign different weights to different terms.

select score(9),a.* from usef_tbl_ci a where contains (doc,‘different ; weights ‘,9)>0; ==>score=28

select score(9),a.* from usef_tbl_ci a where contains (doc,‘different ; terms ‘,9)>0; ==> score=14

عملگر weight (*)

با این عملگر می توان امتیاز یک عبارتی را چند برابر کرد:

select score(9),a.* from usef_tbl_ci a where contains (doc,‘different ; terms ‘,9)>0; ==> score=14

select score(9),a.* from usef_tbl_ci a where contains (doc,‘different ; terms*5 ‘,9)>0; ==> score=71

عملگر ACCUMulate ( , )

هر سطری که حداقل یکی از عبارتها را در خود داشته باشد، در خروجی دیده می شود. همچنین مستندی که بیشترین کلمه ها را شامل می شود، بیشترین نمره را می گیرد. به مثال زیر توجه کنید:

it is a book book.                                                                 ==>id=1

You can assign different weights to different terms.  ==>id=2

select score(9),a.* from usef_tbl_ci a where contains (doc,‘ book,assign’,9)>0; ==> id1 =5 ,id 2=3

select score(9),a.* from usef_tbl_ci a where contains (doc,’ book,different,assign’,9)>0; ==> id1=4 ,id 2=36

اولویت عملگرها

اگر چند عملگر با هم در یک پرس و جو استفاده شوند، ابتدا به اولویت عملگر باید دقت شود و در صورتی که اولویت دو عملگر یکسان بود، ترتیب ظاهر شدن در دستور ملاک خواهد بود. اولویت عملگرها به ترتیب زیر می باشد:

EQUIValence (=)

NEAR (;)

weight (*), threshold (>)

MINUS (-)

NOT (~)

WITHIN

AND (&)

OR (|)

ACCUMulate ( , )

در ادامه چند مثال در مورد اولویت عملگرها آورده شده است:

مثال یک: در این مثال دو عملگر and و or وجود دارند که با توجه به اولویت عملگر and نسبت به or، ابتدا جستجو برای عبارت w2 & w3 انجام می شود و در نهایت حاصل آن با w1 جمع می شود.

w1 | w2 & w3  ==>             (w1) | (w2 & w3)

مثال دوم: در این مثال به ترتیب ظاهر شدن عملگرها، نتیجه محاسبه می شود:

w1 & w2 | w3  ==>             (w1 & w2) | w3

مثال سوم: عملگر مساوی از عملگر & اولویت بیشتری دارد:

abc = def ghi & jkl = mno ==>        ((abc = def) ghi) & (jkl=mno)

مثال چهارم: عملگر & اولویت کمتری نسبت به within دارد:

dog and cat WITHIN body ==>      dog and (cat WITHIN body)

مانیتورینگ ساخت ایندکس:

معمولا جنس داده هایی که برای context index استفاده می شود طوری است که ساخت ایندکس بر روی آنها نیاز به صرف زمان زیادی دارد این زمان معمولا قابل تخمین نیست ولی از طریقی می توان پیشرفت ساخت ایندکس را مانیتور کرد. در ادامه نحوه انجام این کار اورده شده است:

execute CTXSYS.CTX_ADM.SET_PARAMETER (‘LOG_DIRECTORY’,’/u01/lob’);

execute  CTXSYS.CTX_OUTPUT.START_LOG(‘log1.log’);

drop index usef_ind12 ;

create index usef_ind12 on usef_tbl_like(source) indextype is ctxsys.context;

در فایل خروجی، اطلاعاتی شبیه به اطلاعات زیر قابل مشاهده می باشد:

14:12:44 10/04/16 687000 documents indexed

14:12:44 10/04/16 Errors reading documents: 0

14:12:44 10/04/16 Index data for 43346 documents to be written to database

14:12:44 10/04/16    memory use: 8273078

14:12:44 10/04/16 Begin sorting the inverted list.

14:12:44 10/04/16 End sorting the inverted list.

همچنین می توان با دستور زیر، اجبار کرد تا ROWID در فایل خروجی ثبت شود:

exec ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID);

برای مشاهده همه خطاهایی که در هنگام ساخت ایندکس ایجاد می شوند می توان از ویوی ctx_user_index_errors استفاده کرد.

همسان سازی context index

موضوعی که در زمینه context index بسیار حائز اهمیت است، کنترل همسان سازی و بروزرسانی اطلاعات ایندکسها می باشد معمولا حجم این نوع از اطلاعات قابل توجه می باشد و بروزرسانی ایندکسها هم نسبتا زمان بر خواهد بود به همین دلیل، باید متناسب با برنامه و نیازمندی ها، بروزرسانی را مدیریت کرد. سهل انگاری در این موضوع ممکن است سبب شود تا حتی مدیر بانک از بهره گیری از این نوع ایندکسها صرف نظر کند. روشهای مختلفی که برای مدیریت بروزرسانی ایندکس وجود دارد بدین شرح می باشند:

1.اجرای دستی ctx_ddl.sync_index: این روش نسبتا قدیمی می باشد که می توان با اجرای دستی این پروسیجر و تعیین ایندکس مد نظر، بروزرسانی را انجام داد.

exec ctx_ddl.sync_index(‘USEF_INDX2’);

2.اجرای ctx_ddl.sync_index از طریق job: روش قبلی نیاز به توجه و مراقبت زیادی دارد به همین دلیل می توان آن را از طریق یک job انجام داد. این کار می تواند از طریق dbms_job و dbms_scheduler مدیریت شود.

3.Sync: این پارامتر می تواند مقادیر زیر را به همراه داشته باشد:

SYNC (MANUAL | EVERY “interval” | ON COMMIT)

— Manual: کماکان باید با استفاده از ctx_ddl.sync_index بروزرسانی را انجام داد.

— Every ‘interval’: در بازه زمانی که مشخص می شود، بروزرسانی انجام شود(البته از طریق job).

— on Commit: به محض اینکه commit انجام شد، بروزرسانی روی ایندکس هم انجام می شود.

4.Alter index: استفاده از این دستور عاقلانه به نظر نمی رسد ولی به عنوان یک گزینه موجود است. این دستور هم به طرز ناشیانه ای سبب بروزرسانی و بازسازی مجدد می شود.

کاهش فضا context index

برای کاهش فضای مصرفی context index، می توان از دستورات زیر بهره گرفت:

exec ctx_ddl.optimize_index(‘USEF_IND5’, ‘FULL’, 10);

alter table usef_tbl_like enable row movement;

alter table usef_tbl_like shrink space cascade;

منظور از عدد 10 در پروسیجر optimize_index، ده دقیقه می باشد که نشان می دهد این دستور باید در ده دقیقه اجرا شود همچنین به جای عبارت FULL می توان از FAST یا TOKEN هم استفاده کرد که میزان فضای کمتری را نسبت به FULL آزاد خواهند کرد.

جابجایی context index

برخلاف ایندکسهای نرمال، برای این نوع از ایندکسها امکان جابجایی وجود ندارد و باید ابتدا آن را drop کرد و سپس با استفاده از preference، آن را در tablespace مورد نظر ایجاد کرد.

برای انجام این کار ابتدا باید با مفهوم preference به صورت کلی آشنا بود. زمانی که ایندکسی از نوع context ایجاد می شود، می توان پارامترهایی را هم برای آن مشخص کرد تا این پارامترها بر پارامترهای پیش فرض رجحان(preference) و اولویت پیدا کنند.

برای مثال می توان از preferenceای به نامstorage  استفاده کرد تا tablespace پیش فرض مربوط به این نوع از ایندکس را مشخص کرد.

نکته: به صورت پیش فرض هر ایندکسی که ساخته می شود، tablespace آن همان tablespace پیش فرض user خواهد بود و BASIC_STORAGE به صورت پیش فرض تنظیم شده نیست.

حال در ادامه مثالی اورده شده که برای ساخت مجدد ایندکس، ابتدا preferenceای را تعریف شده و با کمک آن مشخص شده که این ایندکس در کدام tablespace ساخته شود:

drop index usef_ind5;

begin

   ctx_ddl.create_preference(‘USEF_TEXT’, ‘BASIC_STORAGE’);

   ctx_ddl.set_attribute(‘USEF_TEXT’, ‘I_TABLE_CLAUSE’,‘tablespace SYSTEM’);

end;

/

create index usef_ind5 on  usef_tbl_like(source) indextype is ctxsys.context parameters(‘storage USEF_TEXT’);