چند نکته در دیتابیس اوراکل-قسمت دوم

چند نکته در دیتابیس اوراکل-قسمت اول

چند نکته در دیتابیس اوراکل-قسمت سوم

Cardinality در اوراکل چیست؟

وقتی شما در query plan مشاهده می کنید. کاردینالیتی ارجاع داده می شود به تعداد رکوردهایی که انتظار می رفت از یک عملکرد ویژه برگشت داده شود.

وقتی شما به دنبال چیزهایی مانند  b*-tree index یا یک bitmap index یا نحوه گزینشی که بر آن دلالت می کند هستید که به منظور بهبود بخشیدن اشاره می گردد.

cardinality ارجاع داده می شود به تعداد مقادیر متمایز(number of distinct values)  در ستون های خاص.

برای واضح تر شدن موضوع: اگر شما یک جدول Person داشتید فیلد GENDER به احتمال زیاد دارای   cardinality خیلی پایینی می باشد(این احتمال هست که فقط دو مقدار داشته باشد) در حالی که فیلد ID به احتمال زیاد دارای   cardinality خیلی بالایی می باشد (چون در هر ردیف دارای مقادیر متفاوتی می باشد) به بیان دیگر اگر فیلدی داشتیم که دارای مقادیر یکتای زیادی بود یعنی دارای cardinality  بالایی می باشد.

مثال جدول EMP

مقايسه Group By با Partition By در اوراکل

•  توابع تجمیعی برای دسته بندی رکوردها از Group By استفاده می کند ولی در توابع تحلیلی از Partition By استفاده می کند.

• تجمیعی (Aggregates) همه ردیف ها در یک ردیف به اصطلاح سقوط می کند ولی در تجزیه و تحلیل(Analytics) هیچ ردیفی سقوط نمی کند.

• طبق بررسی های انجام شده در EXPLAIN PLAN هردوی آنها دارای جواب و کارایی شبیه بهم هستند در شرایط عادی(حال ممکن است که در Sub query ها شرایط کمی فرق کند).

• استفاده از دستور Partition By تمیزتر و شفاف تر می باشد.

• این ایده صحیحی نیست که بخواهیم از Distinct در گروه بندی ردیف ها در Partition By استفاده کنیم.

• در حالی که در Group By دو تا رکورد با شرایط مساوی را باهم بصورت کل دریک رکورد نشان می دهد. در Partition By همه رکوردها حتی در شرایط مساوی هم نشان داده می شود.

استفاده از rownum در کوئری اوراکل

در هنگام استفاده از ROWNUM هوشیار باشید:

• استفاده از rownum می تواند باعث مشکلات عملکرد شود.

• هنگام استفاده از rownum ممکن است حالت بهینه سازی all_rows برای یک کوئری به first_rows تغییر یابد و باعث شود.

• در اجرای پلن (execution plans) زیر بهینه ساز موارد غیرمنتظره رخ دهد.

  یک راه حل این است که همیشه هنگام استفاده از rownum برای انجام top-n query ، از هینت /*+ ALL_ROWS */ نیز استفاده نماییم.


-- 11g
SELECT /*+ ALL_ROWS */ *
FROM emp
WHERE rownum <= 5
ORDER BY empno DESC

--12c+
SELECT /*+ ALL_ROWS */*
FROM emp
ORDER BY empno DESC
FETCH FIRST 5 ROWS ONLY;

• جایگزین های دیگر برای rownum در اوراکل:

استفاده از rownum بسیار خطرناک است – به ویژه در دست مبتدیان ، و همیشه جایگزین های استفاده از rownum وجود دارد:

• • WITH clause – یک رویکرد دیگر برای تنظیم پرس و جوهای مربوط به rownum جدا کردن subquery با استفاده از WITH clause می باشد. یکی دیگر از مزایای جدا کردن subquery مرتب شده این است که براحتی می توان از یک parallel hint و index hint استفاده نمایید اگر بازیابی ردیف ها در یک دستور از قبل مرتب شده سریعتر باشد.

  • استفاده از دو فانکشن تجزیه و تحلیل(Analytics) اوراکل به نام های (ROW_NUMBER و RANK)- میتوان این فانکشن ها را جایگزین استفاده از rownum برای انجام top-n query قرار داد.و همان نتیجه را با زمان پاسخ بسیار سریعتر داشته باشیم.


SELECT empno,
ename,
deptno,
sal,
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
FROM emp;

SELECT empno,
ename,
deptno,
sal,
RANK() OVER (ORDER BY sal) AS row_rank
FROM emp;

• هدف بهینه ساز Hint در اوراکل- در مواردی که از rownum برای تغییر حالت بهینه ساز به first_rows_n استفاده می شود ، ممکن است با استفاده از یک hint all_rows این اثر را نفی کنید.

• hint ایندکس در اوراکل- در مواردی که از rownum برای مجبور کردن استفاده یک ایندکس در یک subquery بکاربرده می شود ، مجدداً یک hint ایندکس را برای دوبرابر کردن سرعت اجرای پلن (execution plans) مستقر کنید.

امیدوار هستم که به دوستان کمک کرده باشم.