در متن “JSON و دیتابیس اوراکل” توضیح دادیم که چگونه اوراکل در نسخه 12c امکان ذخیره و کنترل اطلاعات JSON را در قالب دیتاتایپ CLOB، varchar و … فراهم کرده است و همچنین مطالبی را در مورد نحوه ایندکس گذاری کلیدهای JSON ارائه کرده ایم(برای مطالعه). در نسخه 21c بهبودهای دیگری نظیر نوع داده JSON، تابع JSON_TRANSFORM و … هم به این مجموعه قابلیتها اضافه شد که قبلا بعضی از آنها را مستند کرده ایم و در این متن قصد داریم در مورد یکی دیگر از این قابلیتها که JSON Multi value index است، نکاتی را بنویسیم.

می دانیم که قرار نیست ما به ازای هر کلید در JSON، صرفا یک مقدار ذخیره شود و در مواردی ممکن است مقدار یک کلید، یک آرایه(یا همان لیست) باشد. در این شرایط استفاده از روشهای قبلی ایندکس گذاری برای جستجو چندان کارآمد نخواهند بود و باید به دنبال روش جدیدی برای این حالت باشیم. در ادامه با ارائه مثالی، بیشتر این موضوع را توضیح خواهیم داد.

فرض کنید قرار است داکیومنت JSON شامل اطلاعاتی در مورد بازیکنان فوتبال باشد،(نام بازیکن و تیمی که در آن بازی کرده است(Team)). همانطور که می دانیم، یک بازیکن ممکن است در تیمهای مختلفی بازی کرده باشد بنابرین کلید Team در داکیومنت JSON ممکن است شامل چند مقدار باشد. برای مثال، اطلاعات زیر که در مورد دیوید بکام است را به صورت یک نمونه مشاهده می کنید.

{
  “Fname” : “David”,
  “Lname” : “Beckham”,
  “Team” :
  [
    “Manchester United”,
    “Real Madrid”,
    “LA Galaxy”,
    “Paris Saint-Germain”
  ]
}

قصد داریم لیست تمامی بازیکنانی که در Real Madrid بازی کردند را نمایش دهیم این کار با کوئری زیر قابل انجام است:

SQL> select json_serialize(player_data pretty)
  from PLAYER
 where json_exists(player_data, '$.Team[*]?( @ == "Real Madrid" )');

execution plan این کوئری به صورت زیر است:

و زمان اجرای کوئری حدودا دو ثانیه است:

SQL> select json_serialize(player_data pretty) player_data from PLAYER where json_exists(player_data, '$.Team[*]?( @ == "Real Madrid" )');

PLAYER_DATA
----------------------------
{
  "Fname" : "Zinedine",
  "Lname" : "Zidane",
  "Team" :
  [
    "Cannes",
    "Bordeaux",
    "Juventus",
    "Real Madrid"
  ]
}
{
  "Fname" : "David",
  "Lname" : "Beckham",
  "Team" :
  [
    "Manchester United",
    "Real Madrid",
    "LA Galaxy",
    "Paris Saint-Germain"
  ]
}
Executed in 2/032 seconds

مطابق با نسخه های قبل از 21c می توانیم به صورت زیر بر روی ستون Team ایندکس گذاری کنیم تا سرعت این پرس و جو را افزایش دهیم:

SQL> create index ind1 on PLAYER (player_data.Team.string());
Index created
SQL> exec  dbms_stats.gather_table_stats('USEF','PLAYER');
PL/SQL procedure successfully completed
SQL> select json_serialize(player_data pretty)
  from PLAYER
 where json_exists(player_data, '$.Team[*]?( @ == "Real Madrid" )');

اما به دلیل استفاده از آرایه، این روش پاسخگو نخواهد بود:

این مسئله در اوراکل نسخه 21c حل شده است! و در این نسخه با استفاده از قابلیت JSON Multi value index می توان سرعت این قبیل کوئری ها را افزایش داد:

SQL> create multivalue index ind2 on PLAYER (player_data.Team.string());
Index created
SQL> exec  dbms_stats.gather_table_stats('USEF','PLAYER');
PL/SQL procedure successfully completed
SQL> select json_serialize(player_data pretty)
  from PLAYER
 where json_exists(player_data, '$.Team[*]?( @ == "Real Madrid" )');

زمان اجرای کوئری بسیار کاهش یافته است:

SQL> set timing on
SQL> select json_serialize(player_data pretty) player_data from PLAYER where json_exists(player_data, '$.Team[*]?( @ == "Real Madrid" )');
PLAYER_DATA
---------------------------
{
  "Fname" : "Zinedine",
  "Lname" : "Zidane",
  "Team" :
  [
    "Cannes",
    "Bordeaux",
    "Juventus",
    "Real Madrid"
  ]
}
{
  "Fname" : "David",
  "Lname" : "Beckham",
  "Team" :
  [
    "Manchester United",
    "Real Madrid",
    "LA Galaxy",
    "Paris Saint-Germain"
  ]
}
Executed in 0/134 seconds