سوال: عددی که دستور زیر در خروجی بر می گرداند، چند در صد احتمال خطا خواهد داشت؟
SQL> set timing on
SQL> SELECT COUNT(DISTINCT name) name FROM tbl;
6000
Elapsed: 00:00:07.32
پاسخ این سوال بسیار روشن است که این دستور بسیار دقیق و فاقد هرگونه خطاست و دقیقا تعداد nameها را با اعمال تابع distinct نشان خواهد داد.البته بدیهی است که این دقت، نیاز به زمان بیشتری هم خواهد داشت.
حال اگر سرعت اجرای این دستور بسیار مهمتر از دقت خروجی ان باشد و همچنین درصد مشخصی از خطا در خروجی قابل اغماض باشد، می توان از تابع APPROX_COUNT_DISTINCT به جای (COUNT(DISTINCT استفاده کرد.
برای مثال، خروجی اجرای دستور قبلی با استفاده از این تابع، برابر خواهد بود با:
SQL> SELECT APPROX_COUNT_DISTINCT(name) name FROM tbl;
NAME
————
5750
Elapsed: 00:00:03.93
همانطور که قابل مشاهده است، دستور اول با زمانی حدود 7 ثانیه اجرا شد در صورتی که اجرای دستور دوم، حدودا 4 ثانیه زمان برد.
نکته: برای یکسان بودن شرایط اجرای این دو دستور، قبل از اجرا، بافرکش را خالی کردیم:
SQL> alter system flush BUFFER_CACHE;
System altered.
در صورتی که قصد دارید بدون تغییر کد، این تابع تخمینی همیشه به جای تابع (COUNT(DISTINCT استفاده شود(به عبارت دقیق تر، همیشه query transformation بین این دو تابع صورت پذیرد)، می توانید از پارامتر approx_for_count_distinct که از اوراکل نسخه 12cR2 ارائه شد، استفاده کنید در این صورت، اجرای دستور قبلی به هر دو شکل مذکور، عدد تخمینی و همراه با خطای 5750 را برمی گرداند:
SQL> alter system set approx_for_count_distinct=true;
System altered.
SQL> SELECT COUNT(DISTINCT name) name FROM tbl;
5750
مجدد تاکید می شود که با تنظیم این پارامتر، نیازی به تغییر کد نخواهیم داشت و همچنین تنظیم این پارامتر در سطح session هم ممکن می باشد.
در ادامه با دو روش نشان خواهیم داد که تنظیم پارامتر approx_for_count_distinct به مقدار true، سبب اجرای دستورات به صورت تخمینی خواهد شد.
روش اول: استفاده از تریس10053 و مشاهده query transformation
SQL> ALTER SESSION SET EVENTS ‘10053 trace name context forever’;
Session altered.
SQL> SELECT COUNT(DISTINCT name) name FROM usef.tbl;
5750
با مشاهده فایل تریس، تغییر شکل دستور قبلی قابل مشاهده است:
AAT: Considering Approximate Aggregate Transformation on query block SEL$1 (#0)
***************************************
Approximate Aggregate Transformation (AAT)
***************************************
AAT: transformed final query
*** UNPARSED QUERY IS ***
SELECT APPROX_COUNT_DISTINCT(“TBL”.”NAME”) “NAME” FROM “USEF”.”TBL” “TBL”
SQE: Trying SQ elimination.
SU: Considering subquery unnesting in query block SEL$1 (#0)
****************
روش دوم: مشاهده نقشه اجرایی دستور:
همچنین نقشه اجرای این دستور هم نشان می دهد که SORT AGGREGATE APPROX قسمتی از operation می باشد:
SELECT COUNT(DISTINCT name) name FROM tbl;
Id Operation Name Rows Bytes Cost Time
1 . SORT AGGREGATE APPROX 1 4
توابع و پارامترهای دیگری هم در این زمینه وجود دارند که به شرط توفیق، در اینده در مورد انها مطالبی را ارائه خواهیم کرد.
