ٹھنڈی SQL آپٹیمائزیشنز جو لاگت کے ماڈل پر منحصر نہیں ہیں۔ حصہ 2

ٹھنڈی SQL آپٹیمائزیشنز جو لاگت کے ماڈل پر منحصر نہیں ہیں۔ حصہ 1

4. "بے معنی" پیشین گوئیوں کا خاتمہ

یکساں طور پر بے معنی پیشین گوئیاں ہیں جو (تقریباً) ہمیشہ سچ ہوتی ہیں۔ جیسا کہ آپ تصور کر سکتے ہیں، اگر آپ پوچھ رہے ہیں:

SELECT * FROM actor WHERE 1 = 1;

پھر ڈیٹا بیس اس پر عمل درآمد نہیں کریں گے، بلکہ اسے نظر انداز کر دیں گے۔ میں نے ایک بار اسٹیک اوور فلو پر اس کے بارے میں ایک سوال کا جواب دیا تھا اور اسی وجہ سے میں نے یہ مضمون لکھنے کا فیصلہ کیا۔ میں قارئین کے لیے ایک مشق کے طور پر اس کی جانچ چھوڑ دوں گا، لیکن اگر پیشین گوئی تھوڑی کم "بے معنی" ہو تو کیا ہوگا؟ مثال کے طور پر:

SELECT * FROM film WHERE release_year = release_year;

کیا آپ کو واقعی ہر قطار کے لیے قدر کا خود سے موازنہ کرنے کی ضرورت ہے؟ نہیں، ایسی کوئی قیمت نہیں ہے جس کے لیے یہ پیشین گوئی FALSE ہو ، ٹھیک ہے؟ لیکن ہمیں اب بھی اسے چیک کرنے کی ضرورت ہے۔ اگرچہ پیش گوئی FALSE کے برابر نہیں ہوسکتی ہے، لیکن یہ تین قدر والی منطق کی وجہ سے، ہر جگہ NULL کے برابر ہوسکتی ہے ۔ RELEASE_YEAR کالم کالعدم ہے، اور اگر کسی قطار میں RELEASE_YEAR IS NULL ہے ، تو NULL = NULL کا نتیجہ NULL میں آتا ہے اور قطار کو ختم کرنا ضروری ہے۔ تو درخواست مندرجہ ذیل بن جاتی ہے:

SELECT * FROM film WHERE release_year IS NOT NULL;

کون سے ڈیٹا بیس ایسا کرتے ہیں؟

ڈی بی 2

جی ہاں!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

مائی ایس کیو ایل

یہ ایک شرم کی بات ہے، لیکن MySQL، ایک بار پھر، عملدرآمد کے منصوبوں میں پیشین گوئیوں کا نقشہ نہیں بناتا، لہذا یہ معلوم کرنا کہ آیا MySQL اس مخصوص اصلاح کو نافذ کرتا ہے، تھوڑا مشکل ہے۔ آپ کارکردگی کا جائزہ لے سکتے ہیں اور دیکھ سکتے ہیں کہ آیا کوئی بڑے پیمانے پر موازنہ کیا جا رہا ہے۔ یا آپ انڈیکس شامل کر سکتے ہیں:

CREATE INDEX i_release_year ON film (release_year);

اور بدلے میں درج ذیل درخواستوں کے منصوبے حاصل کریں:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

اگر اصلاح کام کرتی ہے، تو دونوں سوالات کے منصوبے تقریباً ایک جیسے ہونے چاہئیں۔ لیکن اس معاملے میں ایسا نہیں ہے:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

جیسا کہ آپ دیکھ سکتے ہیں، ہمارے دو سوالات POSSIBLE_KEYS اور FILTERED کالمز کی قدروں میں نمایاں طور پر مختلف ہیں ۔ لہذا میں ایک معقول اندازہ لگاؤں گا کہ MySQL اس کو بہتر نہیں کرتا ہے۔

اوریکل

جی ہاں!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

پوسٹگری ایس کیو ایل

بد قسمتی سے نہیں!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

منصوبے اور اخراجات مختلف ہوتے ہیں۔ یعنی، کارڈنالٹی کی تشخیص پر نظر ڈالیں، جو بالکل ٹھیک نہیں ہے، جبکہ یہ پیشین گوئی:

SELECT * FROM film WHERE release_year IS NOT NULL;

بہت بہتر نتائج دیتا ہے:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

بومر!

ایس کیو ایل سرور

عجیب بات یہ ہے کہ ایس کیو ایل سرور ایسا نہیں کرتا ہے:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

تاہم، منصوبہ کی ظاہری شکل کی بنیاد پر، کارڈنالٹی کا اندازہ درست ہے، جیسا کہ قیمت ہے۔ لیکن ایس کیو ایل سرور کے ساتھ میرے تجربے میں، میں یہ کہوں گا کہ اس معاملے میں، کوئی اصلاح نہیں ہوتی ہے، کیونکہ ایس کیو ایل سرور پلان میں اصل میں انجام پانے والی پیش گوئی کو ظاہر کرے گا (یہ دیکھنے کے لیے کہ کیوں، ذیل میں چیک رکاوٹ کی مثالوں پر ایک نظر ڈالیں)۔ NOT NULL کالموں پر "بے معنی" پیشین گوئی کے بارے میں کیا خیال ہے ؟ مندرجہ بالا تبدیلی صرف ضروری تھی کیونکہ RELEASE_YEAR کی وضاحت نہیں کی جا سکتی ہے۔ کیا ہوتا ہے اگر آپ وہی بے معنی سوال چلاتے ہیں، مثال کے طور پر، FILM_ID کالم ؟

SELECT * FROM film WHERE film_id = film_id

کیا اب یہ بالکل بھی کسی پیش گوئی کے مساوی نہیں ہے؟ یا کم از کم ایسا ہی ہونا چاہیے۔ لیکن یہ ہے؟

ڈی بی 2

جی ہاں!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

کوئی پیش گوئی بالکل بھی لاگو نہیں کی جاتی ہے اور ہم تمام فلموں کو منتخب کرتے ہیں۔

مائی ایس کیو ایل

جی ہاں! (دوبارہ، تعلیم یافتہ اندازہ)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

دھیان دیں کہ EXTRA کالم اب خالی ہے، گویا ہمارے پاس کوئی WHERE شق ہی نہیں ہے!

اوریکل

جی ہاں!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

ایک بار پھر، کوئی پیش گوئیاں لاگو نہیں ہوتی ہیں۔

پوسٹگری ایس کیو ایل

واہ، دوبارہ نہیں!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

فلٹر لگا دیا گیا ہے اور کارڈینیلیٹی سکور ابھی بھی 5 ہے۔

ایس کیو ایل سرور

اور یہاں پھر نہیں!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

خلاصہ

یہ ایک سادہ اصلاح کی طرح لگتا ہے، لیکن یہ تمام DBMSs میں استعمال نہیں ہوتا ہے؛ خاص طور پر، عجیب بات یہ ہے کہ، یہ SQL سرور میں استعمال نہیں ہوتا ہے!

5. موجود ذیلی سوالات میں تخمینہ

دلچسپ بات یہ ہے کہ میری ماسٹر کلاس میں ہر وقت مجھ سے ان کے بارے میں پوچھا جاتا ہے، جہاں میں اس نقطہ نظر کا دفاع کرتا ہوں کہ SELECT * عام طور پر کوئی اچھائی کا باعث نہیں بنتا۔ سوال یہ ہے کہ: کیا SELECT * کو EXISTS ذیلی سوال میں استعمال کرنا ممکن ہے ؟ مثال کے طور پر، اگر ہمیں فلموں میں اداکاری کرنے والے اداکاروں کو تلاش کرنے کی ضرورت ہے...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

اور جواب ہے... ہاں۔ کر سکتے ہیں۔ ستارہ درخواست کو متاثر نہیں کرتا ہے۔ آپ اس بات کا یقین کیسے کر سکتے ہیں؟ درج ذیل استفسار پر غور کریں:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

یہ تمام ڈیٹا بیس صفر کی غلطی سے تقسیم کی اطلاع دیتے ہیں۔ ایک دلچسپ حقیقت نوٹ کریں: MySQL میں، جب ہم صفر سے تقسیم کرتے ہیں، تو ہمیں غلطی کی بجائے NULL مل جاتا ہے، اس لیے ہمیں ایک اور غیر قانونی عمل کرنا پڑتا ہے۔ اب، کیا ہوتا ہے اگر ہم مندرجہ بالا کے بجائے، درج ذیل سوالات کو انجام دیں؟

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

اب ڈیٹا بیس میں سے کوئی بھی غلطی واپس نہیں کرتا ہے۔ وہ سب TRUE یا 1 واپس کرتے ہیں ۔ اس کا مطلب یہ ہے کہ ہمارا کوئی بھی ڈیٹا بیس اصل میں EXISTS subquery کے پروجیکشن (یعنی SELECT شق ) کا اندازہ نہیں لگاتا ۔ SQL سرور، مثال کے طور پر، مندرجہ ذیل منصوبہ کو ظاہر کرتا ہے:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

جیسا کہ آپ دیکھ سکتے ہیں، CASE اظہار کو مستقل میں تبدیل کر دیا گیا ہے اور ذیلی سوال کو ختم کر دیا گیا ہے۔ دوسرے ڈیٹا بیس ذیلی استفسار کو پلان میں محفوظ کرتے ہیں اور پروجیکشن کے بارے میں کچھ بھی نہیں بتاتے ہیں، تو آئیے اوریکل میں اصل استفسار کے منصوبے پر ایک اور نظر ڈالیں:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

مندرجہ بالا سوال کا منصوبہ اس طرح لگتا ہے:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

ہم Id=3 پر پروجیکشن کے بارے میں معلومات کا مشاہدہ کرتے ہیں ۔ درحقیقت، ہم FILM_ACTOR ٹیبل تک بھی رسائی نہیں رکھتے کیونکہ ہمیں اس کی ضرورت نہیں ہے۔ EXISTS predicate کو ایک ہی ACTOR_ID کالم پر غیر ملکی کلیدی انڈیکس کا استعمال کرتے ہوئے انجام دیا جا سکتا ہے - اس استفسار کے لیے ضروری ہے - حالانکہ ہم نے SELECT * لکھا ہے ۔

خلاصہ

خوش قسمتی سے، ہمارے تمام ڈیٹا بیس EXISTS ذیلی سوالات سے پروجیکشن کو ہٹاتے ہیں : حصہ 3 کے لیے دیکھتے رہیں ، جہاں ہم دیگر بہترین SQL آپٹیمائزیشن پر بات کریں گے۔