title: ÐÑÑÐ»ÐµÐ´Ð¾Ð²Ð°Ð½Ð¸Ðµ ÑÐ¿Ð¾ÑÐ¾Ð±Ð¾Ð² Ð¸ Ð¼ÐµÑÐ¾Ð´Ð¾Ð² Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð² ÐºÐ¾ÑÐ¿Ð¾ÑÐ°ÑÐ¸Ð²Ð½Ð¾Ð³Ð¾ Ñ ÑÐ°Ð½Ð¸Ð»Ð¸ÑÐ° Ð´Ð°Ð½Ð½ÑÑ ÐºÐ¾Ð¼Ð¿Ð°Ð½Ð¸Ð¸ "ÐÐµÐ³Ð°ÑÐ¾Ð½"
title: Ð²ÑÐ¿ÑÑÐºÐ½Ð°Ñ ÐºÐ²Ð°Ð»Ð¸ÑÐ¸ÐºÐ°ÑÐ¸Ð¾Ð½Ð½Ð°Ñ ÑÐ°Ð±Ð¾ÑÐ° Ð¼Ð°Ð³Ð¸ÑÑÑÐ°
titleType: Subtitle
title: Study of methods and methods for optimizing software packages for the corporate data warehouse of the Megafon company
Creators: name: ÐÐ°ÑÑÐ¾ÑÐºÐ¸Ð½Ð°, ÐÐ»Ð¸Ð½Ð°
Publisher: name: Ð¡Ð°Ð½ÐºÑ-ÐÐµÑÐµÑÐ±ÑÑÐ³ÑÐºÐ¸Ð¹ Ð¿Ð¾Ð»Ð¸ÑÐµÑÐ½Ð¸ÑÐµÑÐºÐ¸Ð¹ ÑÐ½Ð¸Ð²ÐµÑÑÐ¸ÑÐµÑ ÐÐµÑÑÐ° ÐÐµÐ»Ð¸ÐºÐ¾Ð³Ð¾
Year: 2023
Subjects: SQL
Ð¡Ð£ÐÐ Oracle
PL/SQL
Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ñ Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð²
Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÐµ Ð¿Ð°ÐºÐµÑÑ Oracle
Ð±Ð°Ð·Ñ Ð´Ð°Ð½Ð½ÑÑ
Ð¼ÐµÑÐ¾Ð´Ñ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸
Oracle DBMS
query optimization
Oracle software packages
databases
optimization methods

description: Ð Ð°Ð±Ð¾ÑÐ° Ð¿Ð¾ÑÐ²ÑÑÐµÐ½Ð° Ð¸ÑÑÐ»ÐµÐ´Ð¾Ð²Ð°Ð½Ð¸Ñ Ð¸ ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÐºÐµ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ð¸ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð² Ð² ÐºÐ¾ÑÐ¿Ð¾ÑÐ°ÑÐ¸Ð²Ð½Ð¾Ð¼ Ð¾ÐºÑÑÐ¶ÐµÐ½Ð¸Ð¸. ÐÐ¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ñ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð² ÑÐ²Ð»ÑÐµÑÑÑ Ð²Ð°Ð¶Ð½Ð¾Ð¹ Ð·Ð°Ð´Ð°ÑÐµÐ¹ Ð´Ð»Ñ Ð¿Ð¾Ð²ÑÑÐµÐ½Ð¸Ñ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÐ¸ Ð¸ ÑÑÑÐµÐºÑÐ¸Ð²Ð½Ð¾ÑÑÐ¸ ÑÐ°Ð±Ð¾ÑÑ ÐºÐ¾ÑÐ¿Ð¾ÑÐ°ÑÐ¸Ð²Ð½ÑÑ ÑÑÐ°Ð½Ð¸Ð»Ð¸Ñ Ð´Ð°Ð½Ð½ÑÑ. Ð ÑÐ°Ð±Ð¾ÑÐµ Ð±ÑÐ»Ð¸ Ð¸Ð·ÑÑÐµÐ½Ñ ÑÐ°Ð·Ð»Ð¸ÑÐ½ÑÐµ ÑÐ¿Ð¾ÑÐ¾Ð±Ñ Ð¸ Ð¼ÐµÑÐ¾Ð´Ñ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð², Ð°ÑÑÐ¸ÑÐµÐºÑÑÑÑ ÑÐ°Ð±Ð»Ð¸Ñ Ð¸ ÑÑÐ¸Ð»Ñ Ð½Ð°Ð¿Ð¸ÑÐ°Ð½Ð¸Ñ ÐºÐ¾Ð´Ð°, ÐºÐ¾ÑÐ¾ÑÑÐµ ÑÐµÐºÐ¾Ð¼ÐµÐ½Ð´ÑÑÑÑÑ Ð² Ð»Ð¸ÑÐµÑÐ°ÑÑÑÐµ Ð¸ Ð½Ð°ÑÑÐ½ÑÑ ÑÑÐ°ÑÑÑÑ. ÐÑÐ» Ð¿ÑÐ¾Ð²ÐµÐ´ÐµÐ½ Ð°Ð½Ð°Ð»Ð¸Ð· ÑÑÑÐµÑÑÐ²ÑÑÑÐ¸Ñ Ð¼ÐµÑÐ¾Ð´Ð¾Ð² Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð¸ Ð¸Ñ Ð¿ÑÐ¸Ð¼ÐµÐ½Ð¸Ð¼Ð¾ÑÑÐ¸ Ð² ÐºÐ¾Ð½ÑÐµÐºÑÑÐµ ÐºÐ¾ÑÐ¿Ð¾ÑÐ°ÑÐ¸Ð²Ð½Ð¾Ð³Ð¾ ÑÑÐ°Ð½Ð¸Ð»Ð¸ÑÐ° Ð´Ð°Ð½Ð½ÑÑ ÐºÐ¾Ð¼Ð¿Ð°Ð½Ð¸Ð¸ "ÐÐµÐ³Ð°ÑÐ¾Ð½". ÐÑÐ¾Ð±Ð¾Ðµ Ð²Ð½Ð¸Ð¼Ð°Ð½Ð¸Ðµ Ð±ÑÐ»Ð¾ ÑÐ´ÐµÐ»ÐµÐ½Ð¾ ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÐºÐµ Ð¾Ð±ÑÐµÐ¹ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ð¸, ÐºÐ¾ÑÐ¾ÑÐ°Ñ Ð¼Ð¾Ð¶ÐµÑ Ð±ÑÑÑ Ð¿ÑÐ¸Ð¼ÐµÐ½ÐµÐ½Ð° ÐºÐ°Ðº Ð´Ð»Ñ ÑÐ¾Ð·Ð´Ð°Ð½Ð¸Ñ Ð½Ð¾Ð²ÑÑ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð², ÑÐ°Ðº Ð¸ Ð´Ð»Ñ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ ÑÑÑÐµÑÑÐ²ÑÑÑÐ¸Ñ. ÐÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ñ Ð²ÐºÐ»ÑÑÐ°ÐµÑ Ð² ÑÐµÐ±Ñ ÑÐµÐºÐ¾Ð¼ÐµÐ½Ð´Ð°ÑÐ¸Ð¸ Ð¿Ð¾ ÑÑÐ¸Ð»Ñ Ð½Ð°Ð¿Ð¸ÑÐ°Ð½Ð¸Ñ ÐºÐ¾Ð´Ð°, Ð¿ÑÐ¸Ð¼ÐµÐ½ÐµÐ½Ð¸Ñ Ð¾Ð¿ÑÐ¸Ð¼Ð°Ð»ÑÐ½ÑÑ ÑÐ¿Ð¾ÑÐ¾Ð±Ð¾Ð² ÑÐ¾ÐµÐ´Ð¸Ð½ÐµÐ½Ð¸Ñ Ð´Ð°Ð½Ð½ÑÑ, Ð¸ÑÐ¿Ð¾Ð»ÑÐ·Ð¾Ð²Ð°Ð½Ð¸Ñ Ð°Ð³ÑÐµÐ³Ð¸ÑÑÑÑÐ¸Ñ ÑÑÐ½ÐºÑÐ¸Ð¹, Ð° ÑÐ°ÐºÐ¶Ðµ ÑÐ°Ð·Ð»Ð¸ÑÐ½ÑÑ Ð¿Ð¾Ð´ÑÐ¾Ð´Ð¾Ð² Ðº ÑÐ¸Ð»ÑÑÑÐ°ÑÐ¸Ð¸ Ð´Ð°Ð½Ð½ÑÑ. ÐÐ»Ñ Ð¿ÑÐ¾Ð²ÐµÑÐºÐ¸ ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÐ°Ð½Ð½Ð¾Ð¹ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ð¸ Ð±ÑÐ»Ð¾ Ð¿ÑÐ¾Ð²ÐµÐ´ÐµÐ½Ð¾ ÑÐµÑÑÐ¸ÑÐ¾Ð²Ð°Ð½Ð¸Ðµ Ð½Ð° 10 Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ°Ñ ÐºÐ¾Ð¼Ð¿Ð°Ð½Ð¸Ð¸ "ÐÐµÐ³Ð°ÑÐ¾Ð½". Ð Ð°Ð·ÑÐ°Ð±Ð¾ÑÑÐ¸ÐºÐ°Ð¼ Ð±ÑÐ»Ð¸ Ð¿ÑÐµÐ´Ð¾ÑÑÐ°Ð²Ð»ÐµÐ½Ñ Ð¿Ð°ÐºÐµÑÑ Ñ Ð¸Ð·Ð²ÐµÑÑÐ½ÑÐ¼Ð¸ Ð¿ÑÐ¾Ð±Ð»ÐµÐ¼Ð°Ð¼Ð¸ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÐ¸, Ð¸ Ð¾Ð½Ð¸ Ð´Ð¾Ð»Ð¶Ð½Ñ Ð±ÑÐ»Ð¸ Ð¿ÑÐ¸Ð¼ÐµÐ½Ð¸ÑÑ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ñ Ð´Ð»Ñ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð¿Ð°ÐºÐµÑÐ¾Ð². Ð ÐµÐ·ÑÐ»ÑÑÐ°ÑÑ ÑÐµÑÑÐ¸ÑÐ¾Ð²Ð°Ð½Ð¸Ñ Ð¿Ð¾ÐºÐ°Ð·Ð°Ð»Ð¸, ÑÑÐ¾ Ð¿ÑÐ¸Ð¼ÐµÐ½ÐµÐ½Ð¸Ðµ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ð¸ Ð¿ÑÐ¸Ð²ÐµÐ»Ð¾ Ðº Ð·Ð½Ð°ÑÐ¸ÑÐµÐ»ÑÐ½Ð¾Ð¼Ñ ÑÐ»ÑÑÑÐµÐ½Ð¸Ñ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÐ¸ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð², ÑÐ²ÐµÐ»Ð¸ÑÐ¸Ð² Ð¸Ñ Ð¾Ð±ÑÑÑ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÑ Ð½Ð° 20%. ÐÐ´Ð½Ð¸Ð¼ Ð¸Ð· ÐºÐ»ÑÑÐµÐ²ÑÑ Ð¿ÑÐµÐ¸Ð¼ÑÑÐµÑÑÐ² ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÐ°Ð½Ð½Ð¾Ð¹ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ð¸ ÑÐ²Ð»ÑÐµÑÑÑ ÐµÐµ Ð´Ð¾ÑÑÑÐ¿Ð½Ð¾ÑÑÑ Ð¸ Ð¿Ð¾Ð½ÑÑÐ½Ð¾ÑÑÑ Ð´Ð»Ñ ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÑÐ¸ÐºÐ¾Ð², Ñ ÐºÐ¾ÑÐ¾ÑÑÑ Ð¼Ð¾Ð¶ÐµÑ Ð¾ÑÑÑÑÑÑÐ²Ð¾Ð²Ð°ÑÑ Ð³Ð»ÑÐ±Ð¾ÐºÐ¸Ðµ Ð·Ð½Ð°Ð½Ð¸Ñ Ð² Ð¾Ð±Ð»Ð°ÑÑÐ¸ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ SQL Ð·Ð°Ð¿ÑÐ¾ÑÐ¾Ð². Ð Ð°Ð·ÑÐ°Ð±Ð¾ÑÑÐ¸ÐºÐ¸ ÑÐ¼Ð¾Ð³Ð»Ð¸ ÑÑÐ¿ÐµÑÐ½Ð¾ Ð¿ÑÐ¸Ð¼ÐµÐ½Ð¸ÑÑ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ñ, Ð²ÑÑÐ²Ð¸Ð² Ð¸ Ð¸ÑÐ¿ÑÐ°Ð²Ð¸Ð² ÑÐ·ÐºÐ¸Ðµ Ð¼ÐµÑÑÐ° Ð² Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ°Ñ, Ð´Ð°Ð¶Ðµ Ð½Ðµ Ð¾Ð±Ð»Ð°Ð´Ð°Ñ ÑÐºÑÐ¿ÐµÑÑÐ½ÑÐ¼Ð¸ Ð½Ð°Ð²ÑÐºÐ°Ð¼Ð¸ Ð² Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸. ÐÑÐ¾Ð³Ð¾Ð¼ ÑÐ°Ð±Ð¾ÑÑ ÑÐ²Ð»ÑÐµÑÑÑ Ð¼ÐµÑÐ¾Ð´Ð¾Ð»Ð¾Ð³Ð¸Ñ, ÐºÐ¾ÑÐ¾ÑÐ°Ñ Ð¿ÑÐµÐ´Ð¾ÑÑÐ°Ð²Ð»ÑÐµÑ Ð¿ÑÐ°ÐºÑÐ¸ÑÐµÑÐºÐ¸Ðµ ÑÐµÐºÐ¾Ð¼ÐµÐ½Ð´Ð°ÑÐ¸Ð¸ Ð¸ Ð¸Ð½ÑÑÑÑÐ¼ÐµÐ½ÑÑ, ÐºÐ¾ÑÐ¾ÑÑÐµ Ð¿Ð¾Ð¼Ð¾Ð³Ð°ÑÑ Ð¿Ð¾Ð²ÑÑÐ¸ÑÑ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÑ Ð¸ ÑÑÑÐµÐºÑÐ¸Ð²Ð½Ð¾ÑÑÑ ÑÐ°Ð±Ð¾ÑÑ Ð¿ÑÐ¾Ð³ÑÐ°Ð¼Ð¼Ð½ÑÑ Ð¿Ð°ÐºÐµÑÐ¾Ð². ÐÐµ Ð¿ÑÐ¸Ð¼ÐµÐ½ÐµÐ½Ð¸Ðµ Ð¼Ð¾Ð¶ÐµÑ Ð·Ð½Ð°ÑÐ¸ÑÐµÐ»ÑÐ½Ð¾ ÑÐ¾ÐºÑÐ°ÑÐ¸ÑÑ Ð·Ð°Ð´Ð°ÑÐ¸ Ð¿Ð¾ Ð¾Ð¿ÑÐ¸Ð¼Ð¸Ð·Ð°ÑÐ¸Ð¸ Ð¸ Ð¿Ð¾Ð·Ð²Ð¾Ð»Ð¸ÑÑ ÑÐ°Ð·ÑÐ°Ð±Ð¾ÑÑÐ¸ÐºÐ°Ð¼ Ð±ÐµÐ·Ð¾Ð¿Ð°ÑÐ½Ð¾ Ð²Ð½Ð¾ÑÐ¸ÑÑ Ð¸Ð·Ð¼ÐµÐ½ÐµÐ½Ð¸Ñ Ð² Ð¿Ð°ÐºÐµÑÑ, Ð½Ðµ ÑÑÑÐ´ÑÐ°Ñ Ð¸Ñ Ð¾Ð±ÑÑÑ Ð¿ÑÐ¾Ð¸Ð·Ð²Ð¾Ð´Ð¸ÑÐµÐ»ÑÐ½Ð¾ÑÑÑ.
description: This work is devoted to the research and development of a methodology for optimizing software packages in a corporate environment. Optimization of software packages is an important task for improving the performance and efficiency of enterprise data warehouses. The work explored various ways and methods of query optimization, table architecture and coding style, which are recommended in the literature and scientific articles. An analysis was made of existing optimization methods and their applicability in the context of MegaFons corporate data warehouse. Particular attention was paid to the development of a common methodology that can be applied both to create new software packages and to optimize existing ones. The methodology includes recommendations on the style of writing code, the use of optimal ways to connect data, the use of aggregation functions, as well as various approaches to filtering data. To test the developed methodology, testing was carried out on 10 Megafon software packages. Developers were provided with packages with known performance issues and were required to apply a methodology to optimize the packages. The test results showed that the application of the methodology led to a significant improvement in the performance of software packages, increasing their overall performance by 20%. One of the key advantages of the developed methodology is its accessibility and understandability for developers who may not have deep knowledge in the field of SQL query optimization. Developers were able to successfully apply the methodology by identifying and fixing bottlenecks in software packages without even having expert optimization skills. The result of this work is a methodology that provides practical recommendations and tools that help improve the performance and efficiency of software packages. Its use can significantly reduce optimization tasks and allow developers to safely make changes to packages without degrading their overall performance.