مقدمه :

موتور بهینه ساز اورکل (oracle optimizer ) وظیفه اش بهبود کارایی اجرای دستورات sql می باشد. یکی از راه های اجرای بهتر، ساده کردن دستورات می باشد تا مسیر ساده تری برای دستیابی به داده ها پیدا کرد. یکی از این موارد حذف join  های بی مورد در دستورات می باشد. در ادامه این مورد را با هم بررسی خواهیم کرد.

حذف join به وسیله optimizer ارواکل ( Join Elimination)

پیاده سازی :

برای ایجاد محیط آزمایش خود، جداول مورد نیاز خود را با دستورات زیر ایجاد می کنیم.

CREATE TABLE parent (

  id          NUMBER NOT NULL,

  description VARCHAR2(50) NOT NULL,

  CONSTRAINT parent_pk PRIMARY KEY (id)

);

INSERT INTO parent VALUES (1, ‘PARENT ONE’);

INSERT INTO parent VALUES (2, ‘PARENT TWO’);

COMMIT;

CREATE TABLE child (

  id          NUMBER NOT NULL,

  parent_id   NUMBER NOT NULL,

  description VARCHAR2(50) NOT NULL,

  CONSTRAINT child_pk PRIMARY KEY (id),

  CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)

);

CREATE INDEX child_parent_fk_idx ON child(parent_id);

INSERT INTO child VALUES (1, 1, ‘CHILD ONE’);

INSERT INTO child VALUES (2, 1, ‘CHILD ONE’);

INSERT INTO child VALUES (3, 2, ‘CHILD TWO’);

INSERT INTO child VALUES (4, 2, ‘CHILD TWO’);

COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, ‘PARENT’);

EXEC DBMS_STATS.gather_table_stats(USER, ‘CHILD’);

به FK بین CHILD.PARENT_ID و ستون PARENT.ID دقت کنید. این قید، نشان می دهد که امکان ندار فرزندی باشد که والد نداشته باشد. پس هر وقت join از جدول فرزند با جدول والد اجرا شود منجر به کاهش هیچ سطری از جدول فرزند نخواهد شد. پس اگر کوئری با فرض عنوان شده اجرا شود و هیچ ستونی از جدول پدر در آن نباشد، Oracle Optimizer دستور join با جدول پدر را حذف و تبدیل به یک کوئری ساده می کند.

بررسی مثال ها :

کوئری زیر یک join بین جداول PARENT و CHILD انجام می دهد، اما فقط به ستون هایی از جدول CHILD در لیست انتخاب اشاره می کند. همچنین توجه داشته باشید که هیچ فیلتری در هیچ ستونی در جدول PARENT وجود ندارد.

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT c.id, c.parent_id, c.description

FROM   child c

       JOIN parent p ON c.parent_id = p.id;

—————————————————————————

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |

—————————————————————————

با بررسی execution plan متوجه می شویم optimizer دستور join را حذف کرده و فقط از جدول child استفاده شده است.

خوب آیا در واقعیت این join اجرا می شود، خود ما هم می تونستیم از اول join را حذف کنیم و دچار این اشتباه نمی شدیم. پس کاربرد حذف join ها کجاست؟

با هم بررسی می کنیم

View ها :

یک view با دستور زیر می سازیم.

CREATE VIEW parent_child_v AS

  SELECT p.id AS parent_id,

         p.description AS parent_description,

         c.id AS child_id,

         c.parent_id AS child_parent_id,

         c.description AS child_description

  FROM   child c

  JOIN   parent p ON c.parent_id = p.id;

تا زمانی که ستون های جدول والد را در لیست انتخاب یا در علت WHERE به عنوان فیلتر ارجاع ندهیم، جدول والد را می توان حذف کرد.

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT child_id,

       child_parent_id,

       child_description

FROM   parent_child_v;

—————————————————————————

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |

—————————————————————————

این امکان به دلیل ادغام نمایش وجود دارد. محتویات نما در کوئری که حاوی آن است ادغام می شود و امکان بهینه سازی های بعدی مانند حذف join را می دهد.

تغییر شکل اضافی برای اجازه حذف join:

به محض ارجاع ستون ها در جدول PARENT، باید join را انجام دهیم.

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_description,

       child_id,

       child_parent_id,

       child_description

FROM   parent_child_v;

—————————————————————————————————-

| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————————-

|   0 | SELECT STATEMENT             |                     |     4 |   120 |     5   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                     |       |       |            |          |

|   2 |   NESTED LOOPS               |                     |     4 |   120 |     5   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL         | PARENT              |     2 |    28 |     3   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | CHILD_PARENT_FK_IDX |     2 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| CHILD               |     2 |    32 |     1   (0)| 00:00:01 |

اگرچه همیشه اینطور نیست. ببینید چه اتفاقی می‌افتد اگر ستون PARENT_CHILD_V.PARENT_ID را که از ستون PARENT.ID گرفته شده است، پرس و جو کنیم.

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_id,

       child_id,

       child_parent_id,

       child_description

FROM   parent_child_v;

—————————————————————————

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |

—————————————————————————

اگر به طور مستقیم از جداول پرس و جو کنیم، می توانیم این موضوع را واضح تر ببینیم.

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT p.id AS p_id, c.id, c.parent_id, c.description

FROM   child c

       JOIN parent p ON c.parent_id = p.id;

—————————————————————————

| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————

|   0 | SELECT STATEMENT  |       |     4 |    64 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| CHILD |     4 |    64 |     3   (0)| 00:00:01 |

—————————————————————————

چگونه می تواند باشد؟ به دلیل وجود FK بین ستون‌های CHILD.PARENT_ID و PARENT.ID، Oracle می‌داند که هر ارجاعی به PARENT.ID در واقع می‌تواند با ارجاع به CHILD.PARENT_ID جایگزین شود. در هر دو مورد بالا، اوراکل کوئری را برای استفاده از ستون از جدول CHILD تغییر شکل داد و به جدول PARENT اجازه داد تا با حذف join انجام شود.

از بین بردن حذف join:

جلوگیری از حذف Join با ندادن اطلاعات کافی به  optimizerبسیار آسان است. صرفاً حذف کلید خارجی امکان حذف Join را از بین می برد.

ALTER TABLE child DROP CONSTRAINT child_parent_fk;

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT parent_id,

       child_id,

       child_parent_id,

       child_description

FROM   parent_child_v;

—————————————————————————————————-

| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |

—————————————————————————————————-

|   0 | SELECT STATEMENT             |                     |     4 |    76 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS                |                     |       |       |            |          |

|   2 |   NESTED LOOPS               |                     |     4 |    76 |     3   (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN           | PARENT_PK           |     2 |     6 |     1   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN          | CHILD_PARENT_FK_IDX |     2 |       |     0   (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| CHILD               |     2 |    32 |     1   (0)| 00:00:01 |

—————————————————————————————————-

با از بین رفتن محدودیت، بهینه‌ساز دیگر از رابطه ویژه بین ستون‌های CHILD.PARENT_ID و PARENT.ID اطلاعی ندارد. در واقع، هیچ چیزی در پایگاه داده وجود ندارد که از حفظ رابطه اطمینان حاصل کند، بنابراین حتی اگر برنامه ما این رفتار را اعمال کند، بهینه ساز نمی تواند از آن استفاده کند.