ESC را فشار دهید تا بسته شود

زمیوس آموزش، یادگیری و سرگرمی

مقایسه CTE (WITH Clause) و Subquery‌ها از منظر Performance در Oracle

مقایسه CTE (WITH Clause) و Subquery‌ها از منظر Performance در Oracle

اگر با Oracle SQL کار کرده باشی، حتماً با دو روش اصلی برای شکستن کوئری‌های پیچیده آشنا هستی:

  1. استفاده از CTE یا همان WITH Clause
  2. استفاده از Subqueryهای معمولی

هر دو روش ظاهراً همان هدف را دنبال می‌کنند، اما از نگاه Performance (کارایی و سرعت اجرا) تفاوت‌های مهمی دارند که در تصمیم‌گیری‌های روزمره یک برنامه‌نویس یا DBA تأثیر زیادی می‌گذارد.

 در این مقاله آموزش Oracle در بخش آموزش Oracle SQL میخواهیم به طورکامل مقایسه کنیم و درنهایت کدام کاربردی تر هستش.

در اوراکل، در نسخه جدید Oracle 23ai این امکان و فابلیت Multi‑Row Insert به صورت پیشرفته‌تری با سینتکس مشابه MySQL و PostgreSQL فراهم شده است.

پیشنهاد می کنم این مقاله زیر رو حتما مطالعه کنی.

در این مقاله شما می خوانید

CTE یا WITH Clause چیست؟

CTEها یک نتیجه میانی را به شکل یک جدول موقت منطقی در حافظه ایجاد می‌کنند، که فقط در طول اجرای همان کوئری وجود دارد. مثلاً:

				
					WITH sales_cte AS (
    SELECT sale_id, customer_id, amount
    FROM sales
    WHERE amount > ۱۰۰۰
)
SELECT c.customer_name, s.amount
FROM customers c
JOIN sales_cte s ON c.customer_id = s.customer_id;

  • مزایا:

    • کوئری خواناتر می‌شود، مخصوصاً وقتی چند بخش مختلف دارد.
    • امکان استفاده چندباره از یک نتیجه میانی بدون محاسبه مجدد.
    • کنترل بهتر با Hintها برای اجرای خاص.

Subquery چیست؟

یک Subquery همان‌طور که از اسمش پیداست، کوئری‌ای است که داخل کوئری اصلی قرار می‌گیرد:

				
					SELECT c.customer_name, s.amount
FROM customers c
JOIN (
    SELECT sale_id, customer_id, amount
    FROM sales
    WHERE amount > ۱۰۰۰
) s ON c.customer_id = s.customer_id;

  • مزایا:

    • ساده و بدون بخش جداگانه.
    • معمولا Optimizer آن را درجا با کوئری اصلی ادغام می‌کند (Inline).
    • در موارد ساده، سرعت بالاتری دارد.

عملکرد (Performance) در Oracle

تفاوت مهم در نحوه اجرای Oracle Optimizer

  • در بعضی نسخه‌ها (مثل Oracle 12c)، CTE به‌صورت پیش‌فرض Materialize می‌شود؛ یعنی بخش WITH کامل اجرا شده و نتیجه در Temporary Tablespace ذخیره می‌شود.
  • Subquery در اکثر مواقع مستقیم با کوئری اصلی Merge می‌شود، که می‌تواند زمان اجرای کمتری داشته باشد.

مثال عملی تست Performance

فرض کنیم یک جدول فروش بزرگ داریم:

				
					CREATE TABLE sales AS
SELECT level AS sale_id,
       MOD(level, 1000) AS customer_id,
       DBMS_RANDOM.VALUE(100, 10000) AS amount
FROM dual
CONNECT BY level <= 500000;

تست CTE:

				
					WITH high_sales AS (
    SELECT sale_id, customer_id, amount
    FROM sales
    WHERE amount > ۵۰۰۰
)
SELECT COUNT(*) FROM high_sales;

تست Subquery:

				
					SELECT COUNT(*) 
FROM (
    SELECT sale_id, customer_id, amount
    FROM sales
    WHERE amount > ۵۰۰۰
);

💡 نتیجه روی Oracle 19c

  • CTE بدون Hint → زمان اجرا کمی بیشتر، چون یک مرحله اضافه برای ذخیره‌سازی نتایج دارد.
  • Subquery → اجرای سریع‌تر به خاطر Inline Merge با کوئری اصلی.
  • CTE با /*+ INLINE */ → عملکرد تقریباً مشابه Subquery.
  • وقتی نیاز به استفاده چندباره از یک نتیجه داریم → CTE بهترین انتخاب است.

نکات بهینه‌سازی در استفاده از CTE و Subquery

  • برای استفاده‌های چندباره → CTE بهتر عمل می‌کند.
  • برای استفاده یک‌باره و ساده → Subquery اغلب سریع‌تر است.
  • با Hintها رفتار اجرای Oracle را کنترل کن:
    • /*+ MATERIALIZE */ → مجبور به ذخیره‌سازی نتایج.
    • /*+ INLINE */ → ادغام با کوئری اصلی.
  • همیشه با EXPLAIN PLAN یا DBMS_XPLAN بررسی کن.

سوالات متداول درباره CTE و Subquery در اوراکل

نه، این یک باور اشتباهه. در نسخه‌های قدیمی‌تر Oracle مثل ۱۲c، رفتار پیش‌فرض CTE مادی‌سازی (Materialize) هست و ممکنه نسبت به Subquery کندتر باشه.

اما در نسخه‌های جدیدتر (۱۸c، ۱۹c) با استفاده از Hintهایی مثل INLINE میشه CTE رو به اندازه Subquery سریع کرد، و حتی در سناریوهایی که یک نتیجه رو چندبار استفاده می‌کنیم، CTE سریع‌تر هم میشه.

وقتی:

  • یک نتیجه میانی رو چند بار در کوئری نیاز داریم.
  • کوئری پیچیده است و خوانایی کد اهمیت زیادی داره.
  • می‌خواهیم مراحل پردازش رو واضح جدا کنیم.

برای مثال در گزارش‌های پیچیده، CTE باعث میشه هم نگهداری کد راحت‌تر باشه و هم تست بخش‌های مختلف ساده‌تر بشه.

بله، اغلب مواقع Oracle Optimizer Subquery رو با کوئری اصلی ادغام (Inline Merge) می‌کنه که منجر به سرعت بهتر میشه.

اما اگر از Hintهایی مثل MATERIALIZE استفاده کنیم، می‌تونیم رفتار رو تغییر بدیم.

البته این باید با بررسی Plan Execution انجام بشه تا مطمئن بشیم روی کارایی تاثیر مثبت داره.

ساده‌ترین راه اینه که هر دو نسخه کوئری رو بنویسی و زمان اجرا و Execution Plan رو مقایسه کنی:

EXPLAIN PLAN FOR <query>;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

با این روش Oracle دقیقاً نشون میده هر روش چطور اجرا شده، و میتونی تصمیم بگیری کدام برای دیتابیس و داده‌های شما بهینه‌تره.

جمع‌بندی

  • CTE و Subquery هر دو ابزار مهمی در Oracle هستند، اما باید بر اساس نیاز و سناریوی استفاده انتخاب شوند.
  • استفاده از Hintها می‌تواند تفاوت زیادی در کارایی ایجاد کند.
  • برای کوئری‌های بزرگ و چندمرحله‌ای، CTE مزیت دارد؛ برای کوئری‌های ساده و یک‌مرحله‌ای، Subquery سریع‌تر است.

📥 اگر سوالی داری در مورد CTE و Subquery در اوراکل داری، در بخش کامنت‌ها بپرس.

میثم راد

من یه برنامه نویسم که حسابی با دیتابیس اوراکل رفیقم! از اونایی ام که تا چیزی رو کامل نفهمم،ول کن نیستم، یادگرفتن برام مثل بازیه، و نوشتن اینجا کمک می کنه تا چیزایی که یاد گرفتم رو با بقیه به شریک بشم، با هم پیشرفت کنیم.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *