Az IN operátor hatékonysága Oracle adatbázisban

Sziasztok! Rébeli-Szabó Tamás vagyok, és 4 éve dolgozom DBA munkakörben. A ZTH csapattal közösen úgy gondoltuk, hogy SQL témájú írások is érdekesek lehetnek a számotokra. Örömmel jelentem be, hogy megszületett az első blogbejegyzés SQL témakörben, fogadjátok szeretettel.


Adatbázis-fejlesztők körében gyakran fölmerül a kérdés, hogy adott SQL-lekérdezés milyen gyors. Egyik ezzel kapcsolatos hiedelem a beágyazott lekérdezésre vonatkozik, miszerint a beágyazott lekérdezés vagy más néven allekérdezés hatékonysága rossz, és ezért kerülendő. Való igaz, hogy ha a SELECT utasításban egy másik SELECT utasítás szerepel, az sokszor nem kellően hatékony, lassabban ad eredményt.

Ám ez általánosságban nem igaz! Bizonyos esetekben nyugodtan alkalmazhatjuk az egyszerűbben olvasható (és írható) allekérdezést. Az IN operátort gyakran használjuk allekérdezéssel együtt. Megmutatjuk, hogy ez is működhet hatékonyan Oracle-ben.

Az alábbi lekérdezésben IN operátort és allekérdezést használtunk:

SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)

Az adatbázis egy SQL-utasítás végrehajtásához ún. végrehajtási tervet készít. A végrehajtási terv azt adja meg, hogy pontosan milyen lépésekben, milyen műveletek használatával fogja elérni az adatokat az adatbázis. Az egyes tervek hatékonysága igen különböző lehet.

A fenti lekérdezés végrehajtási terve a következő:

SQL_ID 0q21nk9mqbpa9, child number 0
-------------------------------------
SELECT last_name, department_id, job_id FROM employees WHERE
department_id IN (SELECT department_id FROM departments WHERE
location_id = 1700)

Plan hash value: 3865462772

-------------------------------------------------------------------------------------------------
| Id| Operation                    | Name             | Rows   | Bytes  | Cost (%CPU)|   Time   |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                  |        |        |    5 (100) |          |
| 1 |  HASH JOIN                   |                  |    106 |   2862 |    5   (0) | 00:00:01 |
| 2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |     21 |    147 |    2   (0) | 00:00:01 |
| 3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |     21 |        |    1   (0) | 00:00:01 |
| 4 |   TABLE ACCESS FULL          | EMPLOYEES        |    107 |   2140 |    3   (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------

A terv részleteit nem kell most megértenünk. Csak azért van rá szükségünk, hogy összehasonlíthassuk egy másik lekérdezés tervével, és így a két lekérdezés hatékonyságát összemérjük.

Nézzük a másik lekérdezést, amely ekvivalens az elsővel olyan értelemben, hogy ugyanazt az eredményhalmazt adja. Ebben a lekérdezésben beágyazott lekérdezés helyett JOIN operátort használtunk:

SELECT last_name, e.department_id, job_id
FROM employees e JOIN departments d ON e.department_id =d.department_id
WHERE location_id = 1700;

A második lekérdezés végrehajtási terve a következő:

SQL_ID ckzz2us9h1mus, child number 0
-------------------------------------
SELECT last_name, e.department_id, job_id FROM employees e JOIN
departments d ON e.department_id =d.department_id WHERE location_id =
1700

Plan hash value: 3865462772

-------------------------------------------------------------------------------------------------
| Id| Operation                    | Name             | Rows   | Bytes  | Cost (%CPU)|   Time   |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT             |                  |        |        |    5 (100) |          |
| 1 |  HASH JOIN                   |                  |    106 |   2862 |    5   (0) | 00:00:01 |
| 2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |     21 |    147 |    2   (0) | 00:00:01 |
| 3 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |     21 |        |    1   (0) | 00:00:01 |
| 4 |   TABLE ACCESS FULL          | EMPLOYEES        |    107 |   2140 |    3   (0) | 00:00:01 |
------------------------------------------------------------------------------------------------

Számunkra elegendő most csak annyit észrevenni, hogy ez a végrehajtási terv pontosan ugyanaz, mint az előző. Egy tervet a Plan hash value azonosít egyértelműen. Ebből tudhatjuk biztosan, hogy ugyanarról a tervről van szó.

Az Oracle adatbázis a háttérben úgy transzformálja át az első, IN operátort és allekérdezést használó lekérdezést, hogy annak futásakor ugyanaz a műveletsor hajtódjon végre (lényegében egy JOIN), mint a második lekérdezés futásakor, vagyis a két lekérdezés hatékonysága megegyezik.


E cikk alapjául Lczinkoc’s Blog egy bejegyzése szolgált. Czinkóczki László, tapasztalt munkatársunk bocsátotta rendelkezésünkre angol nyelvű blogját, amit ezúton is köszönünk neki! A vele készült interjúsorozatot itt és itt olvashatjátok.