پیچیدہ ERP سسٹمز میں بہت سے اداروں کی درجہ بندی کی نوعیت ہوتی ہے۔جب یکساں اشیاء قطار میں لگ جاتی ہیں۔ آباؤ اجداد کے رشتوں کا درخت - یہ انٹرپرائز کا تنظیمی ڈھانچہ ہے (یہ تمام شاخیں، محکمے اور ورک گروپ)، اور سامان کا کیٹلاگ، اور کام کے شعبوں، اور سیلز پوائنٹس کا جغرافیہ،...
اصل میں، کوئی نہیں ہے
ایسے درخت کو DBMS میں ذخیرہ کرنے کے بہت سے طریقے ہیں، لیکن آج ہم صرف ایک آپشن پر توجہ مرکوز کریں گے:
CREATE TABLE hier(
id
integer
PRIMARY KEY
, pid
integer
REFERENCES hier
, data
json
);
CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK
اور جب آپ درجہ بندی کی گہرائیوں میں جھانک رہے ہیں، تو یہ صبر کے ساتھ یہ دیکھنے کا انتظار کر رہا ہے کہ اس طرح کے ڈھانچے کے ساتھ کام کرنے کے آپ کے "بے ہودہ" طریقے کتنے مؤثر ہوں گے۔
آئیے عام مسائل کو دیکھتے ہیں جو پیدا ہوتے ہیں، ایس کیو ایل میں ان کے نفاذ، اور ان کی کارکردگی کو بہتر بنانے کی کوشش کرتے ہیں۔
#1 خرگوش کا سوراخ کتنا گہرا ہے؟
آئیے، یقینی طور پر، قبول کرتے ہیں کہ یہ ڈھانچہ تنظیم کے ڈھانچے میں محکموں کی ماتحتی کی عکاسی کرے گا: محکمے، ڈویژن، شعبے، شاخیں، ورکنگ گروپس... - جو بھی آپ انہیں کہتے ہیں۔
سب سے پہلے، آئیے 10K عناصر کا اپنا 'درخت' بنائیں
INSERT INTO hier
WITH RECURSIVE T AS (
SELECT
1::integer id
, '{1}'::integer[] pids
UNION ALL
SELECT
id + 1
, pids[1:(random() * array_length(pids, 1))::integer] || (id + 1)
FROM
T
WHERE
id < 10000
)
SELECT
pids[array_length(pids, 1)] id
, pids[array_length(pids, 1) - 1] pid
FROM
T;
آئیے سب سے آسان کام کے ساتھ شروع کرتے ہیں - تمام ملازمین کو تلاش کرنا جو ایک مخصوص شعبے میں کام کرتے ہیں، یا درجہ بندی کے لحاظ سے - نوڈ کے تمام بچوں کو تلاش کریں۔. اولاد کی "گہرائی" حاصل کرنا بھی اچھا ہو گا... یہ سب کچھ ضروری ہو سکتا ہے، مثال کے طور پر، کسی قسم کی تعمیر کے لیے
اگر ان اولادوں کے صرف دو درجے ہوں اور تعداد درجن کے اندر ہو تو سب کچھ ٹھیک ہو جائے گا، لیکن اگر 5 سے زیادہ درجے ہوں، اور پہلے ہی درجنوں اولادیں ہوں تو مسائل ہو سکتے ہیں۔ آئیے دیکھتے ہیں کہ روایتی نیچے درخت تلاش کے اختیارات کیسے لکھے جاتے ہیں (اور کام کرتے ہیں)۔ لیکن پہلے، آئیے اس بات کا تعین کریں کہ کون سے نوڈس ہماری تحقیق کے لیے سب سے زیادہ دلچسپ ہوں گے۔
سب سے زیادہ "گہرا" ذیلی درخت:
WITH RECURSIVE T AS (
SELECT
id
, pid
, ARRAY[id] path
FROM
hier
WHERE
pid IS NULL
UNION ALL
SELECT
hier.id
, hier.pid
, T.path || hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T ORDER BY array_length(path, 1) DESC;
id | pid | path
---------------------------------------------
7624 | 7623 | {7615,7620,7621,7622,7623,7624}
4995 | 4994 | {4983,4985,4988,4993,4994,4995}
4991 | 4990 | {4983,4985,4988,4989,4990,4991}
...
سب سے زیادہ "وسیع" ذیلی درخت:
...
SELECT
path[1] id
, count(*)
FROM
T
GROUP BY
1
ORDER BY
2 DESC;
id | count
------------
5300 | 30
450 | 28
1239 | 27
1573 | 25
ان سوالات کے لیے ہم نے عام استعمال کیا۔ تکراری شمولیت:
ظاہر ہے، اس درخواست کے ماڈل کے ساتھ تکرار کی تعداد اولاد کی کل تعداد سے مماثل ہوگی۔ (اور ان میں سے کئی درجن ہیں)، اور اس میں کافی اہم وسائل لگ سکتے ہیں، اور، نتیجے کے طور پر، وقت لگ سکتا ہے۔
آئیے "وسیع ترین" ذیلی درخت کو دیکھیں:
WITH RECURSIVE T AS (
SELECT
id
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
hier.id
FROM
T
JOIN
hier
ON hier.pid = T.id
)
TABLE T;
جیسا کہ توقع کی گئی تھی، ہمیں تمام 30 ریکارڈ مل گئے۔ لیکن انہوں نے اس پر کل وقت کا 60% صرف کیا - کیونکہ انہوں نے انڈیکس میں 30 تلاشیں بھی کیں۔ کیا کم کرنا ممکن ہے؟
انڈیکس کے لحاظ سے بلک پروف ریڈنگ
کیا ہمیں ہر نوڈ کے لیے الگ انڈیکس استفسار کرنے کی ضرورت ہے؟ یہ پتہ چلتا ہے کہ نہیں - ہم انڈیکس سے پڑھ سکتے ہیں۔ ایک کال میں ایک ساتھ کئی کلیدیں استعمال کرنا مدد کے ساتھ = ANY(array)
.
اور شناخت کنندگان کے ایسے ہر گروپ میں ہم پچھلے مرحلے میں پائی جانے والی تمام IDs کو "نوڈز" کے ذریعے لے سکتے ہیں۔ یعنی ہر اگلے قدم پر ہم کریں گے۔ ایک بار میں ایک مخصوص سطح کے تمام اولادوں کو تلاش کریں۔.
بس، یہاں مسئلہ ہے، بار بار آنے والے انتخاب میں، آپ خود کو نیسٹڈ استفسار تک رسائی حاصل نہیں کر سکتے, لیکن ہمیں کسی نہ کسی طرح صرف وہی منتخب کرنے کی ضرورت ہے جو پچھلی سطح پر پایا گیا تھا... یہ پتہ چلتا ہے کہ پورے انتخاب کے لیے نیسٹڈ استفسار کرنا ناممکن ہے، لیکن اس کے مخصوص فیلڈ کے لیے یہ ممکن ہے۔ اور یہ فیلڈ ایک صف بھی ہو سکتی ہے - جو ہمیں استعمال کرنے کی ضرورت ہے۔ ANY
.
یہ تھوڑا سا پاگل لگتا ہے، لیکن خاکہ میں سب کچھ آسان ہے۔
WITH RECURSIVE T AS (
SELECT
ARRAY[id] id$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
ARRAY(
SELECT
id
FROM
hier
WHERE
pid = ANY(T.id$)
) id$
FROM
T
WHERE
coalesce(id$, '{}') <> '{}' -- условие выхода из цикла - пустой массив
)
SELECT
unnest(id$) id
FROM
T;
اور یہاں سب سے اہم بات یہ بھی نہیں ہے۔ وقت میں 1.5 بار جیتیں۔، اور یہ کہ ہم نے کم بفرز کو منہا کیا، کیونکہ ہمارے پاس 5 کی بجائے صرف 30 کالیں ہیں۔
ایک اضافی بونس یہ حقیقت ہے کہ حتمی انسٹ کے بعد، شناخت کنندگان کو "سطحوں" کے مطابق ترتیب دیا جائے گا۔
نوڈ کا نشان
اگلا غور جو کارکردگی کو بہتر بنانے میں مدد کرے گا وہ ہے - "پتے" کے بچے نہیں ہو سکتےیعنی، ان کے لیے "نیچے" دیکھنے کی ضرورت نہیں ہے۔ اپنے کام کی تشکیل میں، اس کا مطلب یہ ہے کہ اگر ہم محکموں کے سلسلے کی پیروی کرتے ہیں اور ایک ملازم تک پہنچ جاتے ہیں، تو اس شاخ کے ساتھ مزید دیکھنے کی ضرورت نہیں ہے.
آئیے اپنے ٹیبل میں داخل ہوں۔ اضافی boolean
-میدان، جو ہمیں فوری طور پر بتائے گا کہ آیا ہمارے درخت میں یہ مخصوص اندراج ایک "نوڈ" ہے - یعنی، کیا اس کی اولاد بالکل بھی ہو سکتی ہے۔
ALTER TABLE hier
ADD COLUMN branch boolean;
UPDATE
hier T
SET
branch = TRUE
WHERE
EXISTS(
SELECT
NULL
FROM
hier
WHERE
pid = T.id
LIMIT 1
);
-- Запрос успешно выполнен: 3033 строк изменено за 42 мс.
زبردست! یہ پتہ چلتا ہے کہ تمام درختوں کے عناصر میں سے صرف 30٪ سے کچھ زیادہ کی اولاد ہوتی ہے۔
اب ذرا مختلف مکینک کا استعمال کرتے ہیں - بار بار آنے والے حصے سے کنکشن LATERAL
، جو ہمیں ریکسریو "ٹیبل" کے فیلڈز تک فوری رسائی کی اجازت دے گا، اور کلیدوں کے سیٹ کو کم کرنے کے لیے نوڈ پر مبنی فلٹرنگ کنڈیشن کے ساتھ ایک مجموعی فنکشن استعمال کرے گا:
WITH RECURSIVE T AS (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
id = 5300
UNION ALL
SELECT
X.*
FROM
T
JOIN LATERAL (
SELECT
array_agg(id) id$
, array_agg(id) FILTER(WHERE branch) ns$
FROM
hier
WHERE
pid = ANY(T.ns$)
) X
ON coalesce(T.ns$, '{}') <> '{}'
)
SELECT
unnest(id$) id
FROM
T;
ہم ایک اور انڈیکس کال کو کم کرنے کے قابل تھے اور والیوم میں 2 بار سے زیادہ جیتا۔ پروف ریڈ
#2 آئیے جڑوں کی طرف واپس چلتے ہیں۔
یہ الگورتھم مفید ہو گا اگر آپ کو تمام عناصر کے لیے "درخت کے اوپر" کے ریکارڈز جمع کرنے کی ضرورت ہو، جبکہ اس بارے میں معلومات کو برقرار رکھتے ہوئے کہ کون سی سورس شیٹ (اور کن اشارے کے ساتھ) اسے نمونے میں شامل کرنے کا سبب بنی ہے - مثال کے طور پر، خلاصہ رپورٹ تیار کرنے کے لیے۔ نوڈس میں جمع ہونے کے ساتھ۔
مندرجہ ذیل چیزوں کو مکمل طور پر تصور کے ثبوت کے طور پر لیا جانا چاہئے، کیونکہ درخواست بہت بوجھل نکلتی ہے۔ لیکن اگر یہ آپ کے ڈیٹا بیس پر حاوی ہے، تو آپ کو اسی طرح کی تکنیکوں کے استعمال کے بارے میں سوچنا چاہیے۔
آئیے کچھ آسان بیانات کے ساتھ شروع کریں:
- ڈیٹا بیس سے ایک ہی ریکارڈ اسے صرف ایک بار پڑھنا بہتر ہے۔.
- ڈیٹا بیس سے ریکارڈز بیچوں میں پڑھنا زیادہ کارآمد ہے۔اکیلے سے.
اب آئیے اس درخواست کو بنانے کی کوشش کریں جس کی ہمیں ضرورت ہے۔
1 مرحلہ
ظاہر ہے، تکرار شروع کرتے وقت (اس کے بغیر ہم کہاں ہوں گے!) ہمیں ابتدائی شناخت کنندگان کے سیٹ کی بنیاد پر خود پتیوں کے ریکارڈ کو منہا کرنا پڑے گا:
WITH RECURSIVE tree AS (
SELECT
rec -- это цельная запись таблицы
, id::text chld -- это "набор" приведших сюда исходных листьев
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
...
اگر کسی کو یہ عجیب لگتا ہے کہ "سیٹ" ایک سٹرنگ کے طور پر محفوظ ہے نہ کہ ایک صف کے، تو اس کے لیے ایک سادہ سی وضاحت ہے۔ تاروں کے لیے بلٹ ان ایگریگیٹنگ "گلونگ" فنکشن موجود ہے۔ string_agg
، لیکن صفوں کے لیے نہیں۔ اگرچہ وہ
2 مرحلہ
اب ہمیں سیکشن آئی ڈیز کا ایک سیٹ ملے گا جسے مزید پڑھنے کی ضرورت ہوگی۔ تقریبا ہمیشہ وہ اصل سیٹ کے مختلف ریکارڈوں میں نقل کیے جائیں گے - تو ہم کریں گے ان کا گروپ بنائیں, ماخذ کی پتیوں کے بارے میں معلومات کو محفوظ کرتے ہوئے
لیکن یہاں تین مصیبتیں ہمارے منتظر ہیں:
- استفسار کا "سبریکورسیو" حصہ اس کے ساتھ مجموعی افعال پر مشتمل نہیں ہو سکتا
GROUP BY
. - تکراری "ٹیبل" کا حوالہ کسی نیسٹڈ ذیلی سوال میں نہیں ہو سکتا۔
- تکراری حصے میں درخواست میں CTE شامل نہیں ہو سکتا۔
خوش قسمتی سے، ان تمام مسائل کے ارد گرد کام کرنے کے لئے بہت آسان ہیں. آئیے آخر سے شروع کرتے ہیں۔
تکراری حصے میں CTE
یہاں تو کوئی کام:
WITH RECURSIVE tree AS (
...
UNION ALL
WITH T (...)
SELECT ...
)
اور اس طرح یہ کام کرتا ہے، قوسین سے فرق پڑتا ہے!
WITH RECURSIVE tree AS (
...
UNION ALL
(
WITH T (...)
SELECT ...
)
)
ایک بار بار آنے والی "ٹیبل" کے خلاف نیسٹڈ استفسار
ہمم... ایک بار بار آنے والے CTE تک کسی ذیلی سوال میں رسائی نہیں کی جا سکتی۔ لیکن یہ CTE کے اندر ہو سکتا ہے! اور نیسٹڈ درخواست پہلے ہی اس CTE تک رسائی حاصل کر سکتی ہے!
GROUP BY اندر کی تکرار
یہ ناخوشگوار ہے، لیکن... ہمارے پاس GROUP BY استعمال کرنے کا ایک آسان طریقہ ہے۔ DISTINCT ON
اور ونڈو کے افعال!
SELECT
(rec).pid id
, string_agg(chld::text, ',') chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
GROUP BY 1 -- не работает!
اور یہ اس طرح کام کرتا ہے!
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
اب ہم دیکھتے ہیں کہ عددی ID کو متن میں کیوں تبدیل کیا گیا - تاکہ وہ کوما سے الگ کرکے ایک ساتھ جوڑ سکیں!
3 مرحلہ
فائنل کے لیے ہمارے پاس کچھ نہیں بچا:
- ہم گروپ شدہ IDs کے سیٹ پر مبنی "سیکشن" ریکارڈ پڑھتے ہیں۔
- ہم منہا شدہ حصوں کا موازنہ اصل شیٹس کے "سیٹ" سے کرتے ہیں۔
- استعمال کرتے ہوئے سیٹ سٹرنگ کو "توسیع" کریں۔
unnest(string_to_array(chld, ',')::integer[])
WITH RECURSIVE tree AS (
SELECT
rec
, id::text chld
FROM
hier rec
WHERE
id = ANY('{1,2,4,8,16,32,64,128,256,512,1024,2048,4096,8192}'::integer[])
UNION ALL
(
WITH prnt AS (
SELECT DISTINCT ON((rec).pid)
(rec).pid id
, string_agg(chld::text, ',') OVER(PARTITION BY (rec).pid) chld
FROM
tree
WHERE
(rec).pid IS NOT NULL
)
, nodes AS (
SELECT
rec
FROM
hier rec
WHERE
id = ANY(ARRAY(
SELECT
id
FROM
prnt
))
)
SELECT
nodes.rec
, prnt.chld
FROM
prnt
JOIN
nodes
ON (nodes.rec).id = prnt.id
)
)
SELECT
unnest(string_to_array(chld, ',')::integer[]) leaf
, (rec).*
FROM
tree;