برای نمایش تعداد محدودی رکورد از خروجی یک پرس و جو، می توان از توابعی چون ROW_NUMBER، rank و همچنین Pseudo columnای بنام rownum استفاده کرد. البته در اوراکل نسخه 12c هم بهبودهای در این زمینه ایجاد شد و در این نسخه با استفاده از عبارت FETCH هم می توان به این هدف رسید. در ادامه متن با هر سه این روشها آشنا خواهیم شد.

 

ROWNUM

همانطور که اشاره شد، Rownum به عنوان یک Pseudo column قرار است به هر رکوردی که توسط پرس و جو برگردانده می شود، عددی را تخصیص دهد:

select file#,bytes/1024/1024 SIZE_MB,rownum from v$datafile;

یکی از کاربردهای rownum، محدود کردن تعداد رکوردهای قابل نمایش در خروجی است، برای مثال، دستور زیر، صرفا دو دیتافایلی که بیشترین حجم را دارند، در خروجی نمایش می دهد:

select file#,bytes/1024/1024 SIZE_MB,rownum from v$datafile  where rownum<=3 order by SIZE_MB desc;

همانطور که می بینید، دستور فوق در محدود کردن تعداد رکوردها موفق بوده است اما نتوانسته حجیم ترین فایلها را به ترتیب نمایش دهد این مسئله به استفاده همزمان order by و rownum برمیگردد.

در صورت استفاده توامان order by و rownum، ابتدا به هر رکورد در خروجی، یک عدد تخصیص داده می شود و بر اساس محدودیت ذکر شده، صرفا دو رکورد برای نمایش در خروجی انتخاب می شوند و نهایتا خروجی بر اساس عبارت order by مرتب خواهد شد.

بنابرین با اندکی تغییر در پرس و جوی فوق، به این مطلوب هم خواهیم رسید:

select * from (select file#,bytes/1024/1024 SIZE_MB,rownum from v$datafile  order by SIZE_MB desc) where rownum<=3;

توجه: rownum بعد از اعمال where clause و قبل از اعمال order by اجرا می شود.

 

ROW_NUMBER

به جای ROWNUM(که یک pseudocolumn است)، می توان از ROW_NUMBER که یک analytic function است، استفاده کرد. ROW_NUMBER هم با اعمال شرایطی، به هر رکورد عددی را تخصیص خواهد داد.

Select file#,bytes/1024/1024 SIZE_MB,row_number() over(order by null) as  rwn from v$datafile;

با این تابع هم می توان تعداد رکورد خروجی یک پرس و جو را محدود کرد. برای مثال، این بار سعی می کنیم با کمک row_number سه دیتافایلی که بیشترین حجم را دارند، در خروجی نمایش دهیم:

select * from(select file#,bytes/1024/1024 SIZE_MB,row_number() over(order by null) as  rwn from v$datafile order by SIZE_MB desc) where rwn<=3;

همانطور که می بینید، ابتدا خروجی بر اساس عبارت order by مرتب شده و سپس به هر کدام از رکوردها یک رتبه تخصیص داده می شود.

در پرس و جوی فوق، می توان عبارت order by SIZE_MB desc را حذف کرد و مرتب سازی را به کمک تابع row_number انجام داد:

select * from(select file#,bytes/1024/1024 SIZE_MB,row_number() over(order by bytes desc) as  rwn from v$datafile ) where rwn<=3;

علاوه بر کاربرد فوق، این تابع کاربردهای دیگری هم دارد برای مثال، با کمک تابع ROW_NUMBER می توان به هر رکورد بر اساس گروه مشخصی رتبه ای را تخصیص داد. به عنوان مثال، دستور زیر، رتبه ای را برای هر دیتافایل در هر tablespace تعیین می کند(بر اساس حجم تخصیصی دیتافایل):

select file#,ts#,bytes/1024/1024 SIZE_MB,row_number() over(PARTITION by ts# order by bytes desc) as  rwn from v$datafile

 

عبارت FETCH  و OFFSET

در کنار rownum و ROW_NUMBER، اوراکل در نسخه 12c، عبارتهای OFFSET و FETCH را که در بسیاری از دیتابیسهای relational دیگر هم موجود هستند ارائه کرده است. این عبارتها کار محدود کردن خروجی را بسیار ساده تر کرده اند.

ساختار کلی:

[ OFFSET offset { ROW | ROWS } ]

[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]

    { ROW | ROWS } { ONLY | WITH TIES } ]

در ادامه با چند مثال با این عبارت آشنا خواهیم شد.

مثال 1: در پرس و جوی زیر، عبارت FETCH سبب خواهد شد تا صرفا مشخصات دو دیتافایل حجیم دیتابیس در خروجی نمایش داده شوند:

select file#, bytes / 1024 / 1024 SIZE_MB

  from v$datafile

 order by bytes desc

 FETCH FIRST 2 ROWS ONLY

 

مثال 2: اگر سه دیتافایل حجیم دیتابیس اندازه یکسانی داشته باشند، پرس و جوی فوق به صورت تصادفی مشخصات دو دیتافایل را در خروجی نمایش خواهد داد. در این شرایط با کمک عبارت WITH TIES می توانیم همه دیتافایلهای که حجم یکسانی را دارند(با در نظر گرفتن شرط)، در خروجی نمایش دهیم:

select file#, round(bytes / 1024 / 1024/1024) SIZE_GB

  from v$datafile

 order by round(bytes / 1024 / 1024/1024) desc

FETCH FIRST 2 ROWS WITH TIES

مثال 3:اگر عبارت FETCH بدون تعیین عدد استفاده شود، پرس و جو صرفا یک رکورد را برخواهد گرداند:

select file#, bytes / 1024 / 1024 SIZE_MB

  from v$datafile

 order by bytes desc

FETCH NEXT ROW ONLY

مثال 4: با کمک عبارت FETCH می توان درصدی از رکورد را در خروجی نمایش داد:

select file#, bytes / 1024 / 1024 SIZE_MB

  from v$datafile

 order by bytes desc

FETCH FIRST 30 PERCENT ROWS ONLY

در مثال زیر، با کمک عبارت offset نحوه انجام paging را نمایش خواهیم داد.

مثال 5: در این مثال،با استفاده از عبارت offset، پنج رکورد ابتدایی نادیده گرفته شده و صرفا رکوردهای که در رتبه 6 تا 11 هستند به کاربر نمایش داده خواهد شد:

select rownum,status

from v$datafile

offset 5 rows

fetch first 6 rows only

توجه: در صورتی که offset تنظیم نشود، مقدار آن 0 در نظر گرفته خواهد شد.

 

FETCH همان ROW_NUMBER() و یا RANK() است!

زمانی که از عبارت FETCH استفاده می کنیم، اوراکل در پس زمینه با انجام Query Transformation، از ROW_NUMBER استفاده می کند. برای اثبات این مسئله، کافیست تا از event شماره 10053 استفاده کنیم:

SQL> SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE  s.sid =(SELECT sid FROM v$mystat WHERE ROWNUM = 1);

/19c/home/log/diag/rdbms/cdb19c/cdb19c/trace/cdb19c_ora_15532.trc

SQL> select file#, bytes / 1024 / 1024 SIZE_MB from v$datafile order by bytes desc FETCH FIRST 2 ROWS ONLY;

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

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT “from$_subquery$_002”.”FILE#” “FILE#”,”from$_subquery$_002”.”SIZE_MB” “SIZE_MB” FROM  (SELECT “VW_DATAFILE”.”FILE#” “FILE#”,”VW_DATAFILE”.”BYTES”/1024/1024 “SIZE_MB”,”VW_DATAFILE”.”BYTES” “rowlimit_$_0”,ROW_NUMBER() OVER ( ORDER BY “VW_DATAFILE”.”BYTES” DESC ) “rowlimit_$$_rownumber” FROM “SYS”.”VW_DATAFILE” “VW_DATAFILE”) “from$_subquery$_002” WHERE “from$_subquery$_002”.”rowlimit_$$_rownumber”<=2 ORDER BY “from$_subquery$_002”.”rowlimit_$_0” DESC

همچنین در زمان استفاده از عبارت WITH TIES، از تابع ()RANK برای query transformation استفاده می شود:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT “from$_subquery$_002″.”FILE#” “FILE#”,”from$_subquery$_002″.”SIZE_MB” “SIZE_MB” FROM  (SELECT “DF”.”DF_FENUM” “FILE#”,”DF”.”DF_FHFSZ”*”DF”.”DF_FEBSZ”/1024/1024 “SIZE_MB”,”DF”.”DF_FHFSZ”*”DF”.”DF_FEBSZ” “rowlimit_$_0”,RANK() OVER ( ORDER BY “DF”.”DF_FHFSZ”*”DF”.”DF_FEBSZ” DESC ) “rowlimit_$$_rank” FROM “SYS”.”X$KCVDF” “DF” WHERE “DF”.”INST_ID”=USERENV(‘INSTANCE’)) “from$_subquery$_002” WHERE “from$_subquery$_002”.”rowlimit_$$_rank”<=2 ORDER BY “from$_subquery$_002”.”rowlimit_$_0″ DESC