نمایش Union بصورت Columnar

select id,
       sum(decode(x, 1, cnt, 0)) c1,
       sum(decode(x, 2, cnt, 0)) c2,
       sum(decode(x, 3, cnt, 0)) c3,
       sum(decode(x, 4, cnt, 0)) c4
  from (select 1 id, 10 cnt, 1 x
          from dual
        union all
        select 2 id, 20 cnt, 2 x
          from dual
        union all
        select 3 id, 30 cnt, 3 x
          from dual
        union all
        select 4 id, 40 cnt, 4 x
          from dual
     )
 group by id order by id

ID   C1   C2   C3   C4

1    10    0    0    0
2    0    20    0    0
3    0    0    30    0
4    0    0    0    40

اوراکل 12 : Default Value برای ستون های جداول

تا پیش از اوراکل 12 چنانچه بخواهید برای ستونی از یک جدول Defaul Value تعیین کنید، باید یک Trigger و Sequence ایجاد شود؛ تا هنگام Insert در جدول مورد نظر، Trigger  اجرا شود و با اضافه شدن  Sequnce،  مقدار آن در ستون مورد نظر قرار گیرد، مانند مثال زیر :

CREATE TABLE Person (
           ID NUMBER(10)    NOT NULL,
… );

ALTER TABLE departments ADD (
  CONSTRAINT person_pk PRIMARY KEY (ID));

CREATE SEQUENCE person_seq START WITH 1;

CREATE OR REPLACE TRIGGER person_trgr
BEFORE INSERT ON Person
FOR EACH ROW

BEGIN
      SELECT person_seq.NEXTVAL
      INTO   :new.id
      FROM   dual;
END;

ولی در اوراکل 12C نیازی به ساختن Trigger نیست و میتوان تنها با استفاده از Sequence برای ستون مورد نظر Default Value تعیین کرد. به عنوان مثال :

CREATE SEQUENCE person_seq;

CREATE TABLE Person (
       ID   NUMBER(10)    DEFUALT person_seq.NEXTVAL,
… );

اوراکل 12 :‌ ستون های نامرئی

اکنون در اوراکل 12c R1 امکان تعریف ستون های نامرئی -invisible columns- برای جداول  وجود دارد ، وفتی فیلد به عنوان یک ستون نامرئی تعریف میشود دیگر در نتایح جستجوهای عادی نمایش داده نخواهد شد و برای استفاده از این ستون ها باید بطور مستقیم به آن ها اشاره کرد.
 تعریف این ستون ها بسیار ساده است :

CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);

همینطور میتوان آن  را غیر فعال کرد :

ALTER TABLE emp MODIFY (sal visible);

 - در عبارت insert نیز باید صریحا به نام این ستون ها اشاره کرد.
 -  virtual column ها و  partition column ها نیز میتوانند به عنوان یک ستون نامرئی تعریف شوند.
 - امکان استفاده از ستون ها نامرئی در جداول موقت - temporary table - ، جداول خارجی - external tables - و جداول کلاستر - cluster table -  وجود ندارد .
 
بیشتر بخوانید :

Invisible Columns in Oracle Database 12c Release 1 (12.1)

تبدیل select تودرتو به join برای بهبود سرعت اجرا

گاهی نیاز است برای بهبود سرعت پرس و جو ، بجای  select تو در تو از join استفاده کنیم.مثال زیر نمونه ای از این تبدیل می باشد:

 select *
  FROM emp A
  WHERE A.sal between 800 and 2000
  AND A.deptno NOT IN
 (SELECT B.deptno
  FROM dept B
  WHERE B.deptno = A.deptno
  AND B.dname = 'sales');
  //-----------------------------------
  SELECT *  
  FROM emp A
  left join dept B on B.deptno = A.deptno AND B.dname='sales'
  WHERE   A.sal between 800 and 2000
  and B.dname is null

غیرفعالسازی Auto Commit در هنگام خروج از SQLPLUS

در صورتی که یکی از دستورات insert,delete,update را در محیط sqlplus بزنید و بدون commit از محیط exit کنید ، بطور اتوماتیک commit انجام میشود . جهت غیرفعالسازی این امکان مراحل زیر را انجام دهید.

در صورت بستن پنجره Sqlplus پروسه به PMON سپرده شده و تراکنش Rollback میگردد.

SQL> show exitcommit
exitcommit ON
SQL> delete from dept where deptno=30;
1 row deleted.
SQL> exit;
$sqlplus scott/tiger
SQL> select * from dept where deptno=30;
0 row.
**********
SQL> set exitcommit off
SQL> show exitcommit
exitcommit OFF
SQL> delete from dept where deptno=30;
1 row deleted.
SQL> exit;
$sqlplus scott/tiger
SQL> select * from dept where deptno=30;;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO

In در مقابل Exists و Not In در مقابل Not Exists

سوال ) تفاوت In/Not In و Exists/Not Exists در چیست ؟ کدامیک سریع تر هستند ؟ 

 

    الف) تفاوت در نتیجه

 همیشه Not In و  Not Exists شبیه هم نیستند و ممکن است پاسخ های متفاوتی داشته باشند .

 (مثال ۱ ) به طور مثال :

select count(*) from emp 
where empno not in ( select mgr from emp ); 

COUNT(*) 
---------- 
0 

این پاسخ به ما میگوید که همه مدیر هستند . 

حال به پرس و جوی زیر دقت کنید :

select count(*) from emp T1 
where not exists ( select null from emp T2 where t2.mgr = t1.empno ); 

COUNT(*) 
---------- 
9 

ولی این پاسخ نشان میدهد ۹ نفر مدیر نیستند! 

چند نکته مهم در کار با ایندکس ها

1- اگر در محیط عملیاتی اقدام به ساخت ایندکس میکنید حتما از حالت Online استفاده کنید تا کاربران دچار Lock Conflict نشوند . همچنین جهت تسریع در ساخت ایندکس میتوان درجه Parallel را تنظیم نمود(معمولاً بیش از 8 توصیه نمیشود).دقت شود که پس از ساخت ایندکس حتما Degree آن برداشته شود .

2- سعی کنید TBS محل ذخیره ایندکس ها جدا از TBS دیتاها باشد.
3- در صورت امکان دیتافایلهای محل ذخیره ایندکس ها را بر روی هارد های SSD قرار دهید . تا کمترین Latency را در I/O داشته باشید.

توابع تحلیلی در PL\SQL

مقاله پیوست شده ترجمه مقاله ای با همین عنوان به آدرس زیر میباشد :

http://www.orafaq.com/node/55

در این مقاله نویستده تلاش دارد تا مفهوم توابع تحلیلی  را به وضوح و با بهره گیری از مثال های ساده  بیان کند . باید اشاره کرد که مخاطب این مقاله آن دسته از برنامه نویسان SQL  میباشند که به دلیل ناآشنایی با نحو  رمزگونه توابع تحلیلی و یا عدم آشنایی با کاربردهای آن  از این قابلیت استفاده نمیکنند . اغلب مشاهده میشود ، افراد سعی در اختراع دوباره عملکردهای توابع تحلیلی با استفاده از پیوندهای طبیعی و پرس و جوهای  تو در تو  دارند !  تاکید این مقاله بر ارائه مثال جهت بیان مطالب و آموزش میباشد ، همچنین پیش نیاز این مقاله آشنایی با جست و جوی تو در تو ، پیوند ها و توابع گروهبندی  میباشد .  

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

  

دریافت مقاله :
کلمه عبور : oradba.blog.ir

لینک دانلود