Bitmap Based Count Distinct SQL Function

در محیط های Data Warehouse، استفاده از کوئری هایی که در آنها count(distinct) به کار برده شده است، متداول میباشد. همانطور که میدانید، یکی از راههای بالا بردن پرفرمنس کوئری هایی که در آنها از count(distinct) استفاده شده است، استفاده از Query Rewrite در Materialized Viewها است. به دو کوئری زیر توجه کنید:

SELECT promo_id, COUNT(DISTINCT prod_id)

  FROM sh.sales

GROUP BY promo_id

ORDER BY promo_id;

 

SELECT channel_id, COUNT(DISTINCT prod_id)

  FROM sh.sales

GROUP BY channel_id

ORDER BY channel_id;

 

 

 

 

 

 

 

 

 

 

اگر بخواهیم سرعت این کوئری ها را با Materialized View (Query Rewrite) افزایش دهیم، باید برای هر کدام از این کوئری ها یک Materialized View جداگانه ایجاد کنیم. ولی هدف ما در واقع باید این باشد که تعداد کمی Materialized View ایجاد کنیم و تا حد امکان از آنها در کوئری های مختلف استفاده کنیم. ولی تا قبل از اوراکل 19c این امکان برای ما وجود نداشت.

از Oracle 19c یک سری SQL functionها در رابطه با عملیات مرتبط با bitmapها اضافه شده اند، که این فانکشن ها، سرعت اجرایCOUNT(DISTINCT)  را در کوئری ها افزایش میدهند. طبق مطلبی که در داکیومنتهای اوراکل به آن اشاره شده است، تمرکز اصلی این فانکشن ها بر روی Materialized Viewهایی است که در آنها از aggregate data استفاده شده است.

این فانکشنها عبارتند از:

• BITMAP_BIT_POSITION
• BITMAP_BUCKET_NUMBER
• BITMAP_CONSTRUCT_AGG
• BITMAP_COUNT
• BITMAP_OR_AGG

از این فانکشنها به صورتهای مختلفی میتوان استفاده کرد، یکی از موارد استفاده آن همانطور که در بالا هم به آن اشاره شد در  Materialized View (Query Rewrite) است که در زیر با ارائه یک مثال کاربرد آن توضیح داده شده است:

CREATE MATERIALIZED VIEW sh.mv_sales

ENABLE QUERY REWRITE

AS

      SELECT promo_id,

                    channel_id,

                    BITMAP_BUCKET_NUMBER (prod_id),

                    BITMAP_CONSTRUCT_AGG (BITMAP_BIT_POSITION (prod_id))

        FROM sh.sales

    GROUP BY promo_id, channel_id, BITMAP_BUCKET_NUMBER (prod_id);

 

 

 

 

 

 

 

 

 

 

حتما یک log برای Materialized View بالا ایجاد کنید:

CREATE MATERIALIZED VIEW LOG ON sh.sales

TABLESPACE users

WITH rowid;

 

 

 

 

در زیر کاربرد Materialized View بالا را در کوئری هایی که روی جدول sales زده میشود، مشاهده میکنیم:

  SELECT promo_id, COUNT (DISTINCT prod_id)

    FROM sh.sales

GROUP BY promo_id

ORDER BY promo_id;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  SELECT channel_id, COUNT (DISTINCT prod_id)

    FROM sh.sales

GROUP BY channel_id

ORDER BY channel_id;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Document:

https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-9DBB57A5-9F40-49F1-80D7-78F1DFD62924

https://docs.oracle.com/pls/topic/lookup?ctx=en/database/oracle/oracle-database/19/newft&id=DWHSG-GUID-03107B3C-72D3-4B6B-A922-F13FF0CF2B6D

https://danischnider.wordpress.com/2019/04/20/bitmap-based-countdistinct-functions-in-oracle-19c/