زبردست SQL اصلاحون جيڪي قيمت ماڊل تي منحصر نه آھن. حصو 2

زبردست SQL اصلاحون جيڪي قيمت ماڊل تي منحصر نه آھن. حصو 1

4. ”بي معنيٰ“ اڳڪٿين جو خاتمو

هڪجهڙائي بي معنيٰ اڳڪٿيون آهن جيڪي (تقريبا) هميشه سچا آهن. جئين توهان تصور ڪري سگهو ٿا، جيڪڏهن توهان پڇي رهيا آهيو:

SELECT * FROM actor WHERE 1 = 1;

... ته پوءِ ڊيٽابيس اصل ۾ ان تي عمل نه ڪندا، پر صرف ان کي نظرانداز ڪندا. مون هڪ دفعو ان بابت هڪ سوال جو جواب ڏنو اسٽيڪ اوور فلو ۽ اهو ئي سبب آهي ته مون هن مضمون لکڻ جو فيصلو ڪيو. مان هن کي پڙهندڙ لاءِ هڪ مشق جي طور تي جاچڻ ڇڏيندس، پر ڇا ٿيندو جيڪڏهن اڳڪٿي ٿورو گهٽ ”بي معنيٰ“ هجي؟ مثال طور:

SELECT * FROM film WHERE release_year = release_year;

ڇا توهان واقعي جي ضرورت آهي هر قطار لاءِ پاڻ وٽ قدر جو مقابلو ڪرڻ؟ نه، ڪا به قيمت نه آهي جنهن لاءِ هي اڳڪٿي غلط هجي ، صحيح؟ پر اسان کي اڃا تائين ان جي جانچ ڪرڻ جي ضرورت آهي. جيتوڻيڪ اڳڪٿي FALSE جي برابر نه ٿي ٿي سگهي، اهو هر جڳهه NULL جي برابر ٿي سگهي ٿو ، ٻيهر ٽن-قيمتي منطق جي ڪري. RELEASE_YEAR ڪالم nullable آھي، ۽ جيڪڏھن ڪنھن قطار ۾ 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، ٻيهر، نقشي تي نقش نه ٿو ٺاهي اڳڪٿين جي منصوبن ۾، تنهنڪري اهو معلوم ڪرڻ ته ڇا 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)

PostgreSQL

بدقسمتي سان نه!

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)

ڌماڪو!

SQL سرور

حيرت انگيز طور تي، SQL سرور اهو نٿو لڳي ته اهو يا ته:

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

بهرحال، منصوبي جي ظاهري بنياد تي، ڪارڊينلٽي جو اندازو صحيح آهي، جيئن قيمت آهي. پر SQL سرور سان منهنجي تجربي ۾، مان چوندس ته ان صورت ۾، ڪا به اصلاح نه ٿي ٿئي، ڇو ته SQL سرور منصوبي ۾ اصل ۾ جاري ڪيل اڳڪٿي ڏيکاريندو (ڏسڻ لاءِ ڇو، هيٺ ڏنل چيڪ رڪاوٽ مثالن تي هڪ نظر وٺو ). 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

ڪابه اڳڪٿي لاڳو نه ڪئي وئي آهي ۽ اسان سڀ فلمون چونڊون ٿا.

MySQL

ها! (ٻيهر، تعليم يافته اندازو)

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

نوٽ ڪريو ته EXTRA ڪالم هاڻي خالي آهي، ڄڻ ته اسان وٽ ڪٿي به شق نه آهي!

اوريڪل

ها!

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

ٻيهر، ڪابه اڳڪٿي لاڳو ناهي.

PostgreSQL

واهه، ٻيهر نه!

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

فلٽر لاڳو ڪيو ويو آهي ۽ ڪارڊينلٽي سکور اڃا تائين 5 آهي.

SQL سرور

۽ هتي وري نه!

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

خلاصو

اهو لڳي ٿو هڪ سادي اصلاح، پر اهو سڀني DBMSs ۾ استعمال نه ڪيو ويو آهي؛ خاص طور تي، عجيب طور تي ڪافي، اهو SQL سرور ۾ استعمال نه ڪيو ويو آهي!

5. EXISTS ذيلي سوالن ۾ پروجيڪشن

دلچسپ ڳالهه اها آهي ته، مون انهن بابت هر وقت منهنجي ماسٽر ڪلاس ۾ پڇيو، جتي آئون ان نقطي جو دفاع ڪريان ٿو ته 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 سبڪوري جي پروجيڪشن (جيڪو آهي، SELECT شق ) جو جائزو نٿو وٺي . SQL سرور، مثال طور، ھيٺ ڏنل منصوبو ڏيکاري ٿو:

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

جئين توهان ڏسي سگهو ٿا، CASE ايڪسپريس کي مستقل ۾ تبديل ڪيو ويو آهي ۽ ذيلي پڇاڙي ختم ڪئي وئي آهي. ٻيا ڊيٽابيس ذيلي ذيلي ذخيري کي منصوبي ۾ محفوظ ڪن ٿا ۽ پروجئشن جي باري ۾ ڪجھ به ذڪر نه ڪن، تنهنڪري اچو ته Oracle ۾ اصل سوال جي منصوبي تي هڪ ٻيو نظر وٺو:

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 اصلاحن تي بحث ڪنداسين.