Friday, July 24, 2009

SQL วันเดียว

สำหรับตอนนี้ มาจากการที่มักจะมีคำถามอยู่บ่อย ๆ ว่า SQL บน Oracle ต่างจาก SQL บน SQLServer อย่างไร หรือต่างจาก SQL บน Relational Database Management System (RDBMS) ตัวอื่น ๆ อย่างไร ครั้นพอจะจัด Full Course Training ก็ดูเหมือนว่าอาจจะไม่จำเป็น เพราะคนที่ทำงานโดยใช้ SQL มา ก็น่าจะมีทักษะอยู่แล้ว แต่อยากรู้เฉพาะจุดที่แตกต่างกัน มากกว่า อย่างไรก็ตามผมคิดของผมเอาเองว่าการรู้ว่า Oracle SQL มี Features สำคัญ ๆ อย่างไร น่าจะเป็นประโยชน์กับผู้ใช้งานบ้างไม่มากก็น้อย อาจจะจำรายละเอียดไม่ได้ แต่อย่างน้อยต่อไปเมื่อต้องการใช้ก็สามารถที่จะไปค้นดูจากใน Reference ได้ต่อไป โจทย์จึงอยู่ที่ว่าทำอย่างไรจึงจะสามารถสรุปเอาเฉพาะส่วนหลัก ๆ (แต่ครอบคลุม) เนื้อหาของ SQL บน Oracle มาให้ผู้ที่ต้องการศึกษาได้ โดยใช้เวลาในการศึกษาไม่มาก จึงได้บทสรุปย่อ ๆ ขนาดเล็กมาบทหนึ่ง

ตารางที่ใช้เป็นตารางที่อยู่ใน Schema(User) HR ซึ่งเป็นดีฟอลต์ของ Oracle ที่มีมาตั้งแต่ตอนสร้างฐานข้อมูล ซึ่ง Schema(User) HR ใน Oracle10g จะล๊อคอยู่โดยดีฟอลต์ คุณจะต้องปลดล๊อคก่อนโดย ล็อกอินเข้าฐานข้อมูลด้วย User ที่มีสิทธิ์เป็น DBA แล้วใช้คำสั่ง

SQL> alter user hr account unlock;
User altered.

********************************************************************************
Chapter1: Basic SQL Statements
********************************************************************************
เราสามารถแบ่งประเภทของ SQL Statements ได้เป็น 5 ประเภทคือ
1. Data Retrieval
- SELECT
2. Data Manupulation Language (DML)
- INSERT
- UPDATE
- DELETE
- MERGE
3. Data Definition Language (DDL)
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
4. Transaction Control
- COMMIT
- ROLLBACK
- SAVEPOINT
5. Data Control Language (DCL)
- GRANT
- REVOKE

1. การใช้คำสั่ง SELECT โดยทั่วไป
select * from departments;
select department_id, department_name, manager_id, location_id from departments;
Note:
- คำสั่ง SQL จะใช้ตัวใหญ่หรือตัวเล็กก็ได้ (Not Case Sensitive)
2. การใช้ตัวคำนวณทางคณิตศาสตร์ เช่น + - * /
select last_name, salary, salary + 300 from employees;
Note:
- ถ้าค่าที่ใช้ในการคำนวณเป็น null ผลการคำนวณที่ได้จะเป็น null เสมอ
select last_name, job_id, salary, commission_pct, 12*salary*commission_pct from employees;
3. ชื่อย่อของ Column (Alias)
select last_name as name, commission_pct comm from employees;
select last_name "Name", salary*12 "Annual Salary" from employees;
4. การนำข้อมูลในต่างคอลัมน์มาแสดงผลต่อกัน (Concatenate)
select last_name || job_id as "Employees" from employees;
5. การพิมพ์ค่าตายตัว (Constant) ลงร่วมกับค่าที่อยู่ในคอลัมน์ (Literal Character String)
select last_name || ' : 1 Month salary = ' || salary Monthly from employees;
6. การกำจัดแถวซ้ำ
select department_id from employees;
select DISTINCT department_id from employees;
7. การดูโครงสร้างตาราง
DESC employees;
select * from tab;
select * from user_tables;
select * from user_tab_columns;


********************************************************************************
Chapter2: Restricting and sorting data
********************************************************************************
1. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเท่ากับ
select employee_id, last_name, job_id, department_id from employees where department_id = 90;
select last_name, job_id, department_id from employees where last_name = 'Whalen';
select last_name, department_id, hire_date from employees where hire_date = '03-jan-90';
2. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเปรียบเทียบอื่น ๆ (Comparison condition)
= > >= < <= <> != ^=
-- Between
select last_name, salary from employees where salary BETWEEN 2500 and 3500;
-- IN
select employee_id, last_name, salary, manager_id from employees where manager_id IN (100,101,201);
-- LIKE
select first_name from employees where first_name like 'S%';
select last_name, hire_date from employees where hire_date like '%95';
select last_name from employees where last_name like '_o%';
select employee_id, last_name, job_id from employees where job_id like '%SA\_%' escape '\'
-- NULL
select last_name, manager_id from employees where manager_id is null;
Logical condition
-- AND
select employee_id, last_name, job_id, salary from employees where salary >= 10000 and job_id like '%MAN%';
-- OR
select employee_id, last_name, job_id, salary from employees where salary >= 10000 or job_id like '%MAN%';
-- NOT
select last_name, job_id from employees where job_id not in ('IT_PROG','ST_CLERK','SA_REP');
3. การเรียงลำดับแถว (Order by)
select last_name, job_id, department_id,hire_date from employees order by hire_date desc;
select employee_id, last_name, salary*12 annsal from employees order by annsal;
select last_name, department_id, salary from employees order by department_id, salary desc;

********************************************************************************
Chapter3: ฟังก์ชั่นที่ใช้กับข้อมูลแต่ละแถว (Single Row Functions)
********************************************************************************
1. ฟังก์ชันที่ใช้กับตัวอักษร Character functions
- Case manipulation functions: LOWER, UPPER, INITCAP
select 'The job id for ' || Initcap(first_name) || ' ' || UPPER (last_name) || ' is ' || LOWER(job_id) as "EMPLOYEE DETAILS" from employees;
select employee_id, last_name, department_id from employees where last_name = 'higgins';
select employee_id, last_name, department_id from employees where LOWER(last_name) = 'higgins';
select employee_id, last_name, department_id from employees where INITCAP(last_name) = 'Higgins';
- Character-manipulation functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'n',1,2) "Contains 'a'?" from employees where SUBSTR(job_id,4) = 'REP';
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'a') "Contains 'a'?" from employees where SUBSTR(last_name,-1,1) = 'n';

2. ฟังก์ชันที่ใช้กับตัวเลข (Number functions)
--ROUND
select ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-2) from dual;
--TRUNC
select TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) from dual;
--MOD
select last_name, salary, MOD(salary,5000) from employees where job_id = 'SA_REP';

3. ฟังก์ชั่นที่ใช้กับวันที่ (Date functions)
select last_name, hire_date from employees where last_name like 'G%';
select SYSDATE from dual;
-- Arithmetic with Dates
select sysdate +1, sysdate -1, sysdate - date '2009-05-20', sysdate + 1/24 from dual;
select last_name, (sysdate - hire_date) / 7 as weeks from employees where department_id = 90;
-- Date Functions

select MONTHS_BETWEEN(date '1995-09-01',date '1994-01-11') from dual;
select ADD_MONTHS (date '1994-01-11',6) from dual; -- Add 6 months
select NEXT_DAY (sysdate,'FRIDAY') from dual; -- Next Friday
select LAST_DAY (sysdate) from dual;-- Last day of month

select employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date) hire_month, ADD_MONTHS(hire_date,6) review, NEXT_DAY(sysdate,'FRIDAY'), LAST_DAY(hire_date) from employees
where months_between (SYSDATE, hire_date) > 175;

select
ROUND(sysdate,'MONTH') ,
ROUND(sysdate,'YEAR') ,
TRUNC(sysdate,'MONTH') ,
TRUNC(sysdate,'YEAR')
from dual;
select employee_id, hire_date, ROUND(hire_date,'MONTH'), TRUNC(hire_date, 'MONTH') from employees where hire_date like '%97';

4. ฟังก์ชั่นที่ใช้เพื่อแปลงชนิดของข้อมูล (Data type conversion functions)
-- การแปลงแบบปริยาย (Implicit conversion)
VARCHAR2 or CHAR => NUMBER
VARCHAR2 or CHAR => DATE
NUMBER => VARCHAR2
DATE => VARCHAR2
-- การแปลงแบบจงใจ (Explicit conversion)
Convert to Character: TO_CHAR
can convert number,date to character
Convert to Number: TO_NUMBER
can convert character to number
Convert to Date: TO_DATE
can convert character to date

select employee_id, TO_CHAR(hire_date,'MM/YY') "Month Hire" from employees where last_name = 'Higgins';
-- ส่วนประกอบของรูปแบบการแสดงผลวันที่ (Elements of the Date format)
YYYY = Full year in numbers
YEAR = Year spelled out
MM = 2-digit month
MONTH = Full name month
MON = 3-letter month
DAY = Full name day
DY = 3-letter day of week
DD = Numeric day of month
Q = Quarter of year
WW = Week of year
W = Week of month
DDD,DD,D = Day of year, month, week
J = Julian day
select TO_CHAR(sysdate,'DAY') from dual;

-- ส่วนประกอบของรูปแบบการแสดงผลเวลา (Time elements)
HH24:MI:SS AM เช่น 15:45:32 PM
DD "of" MONTH เช่น 12 of OCTOBER
AM or PM = Meridian indicator
A.M. or P.M.
HH or HH12 or HH24 = Hour of day
MI = Minute
SS = Second
SSSSS = Seconds past midnight (0-86399)
TH = แสดงลำดับ
SP = แสดงคำอ่าน
SPTH or THSP = แสดงคำอ่านของลำดับ
เช่น ddSPTH แปลงเป็นคำอ่านของวันที่แบบมีลำดับเช่น Fourteenth
select to_char(sysdate,'DDspTH') from dual;
select last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') as hiredate from employees;
select last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') hiredate from employees;
เปลี่ยนภาษาในการแสดงผลวันที่
alter session set NLS_DATE_LANGUAGE=THAI;
แก้เป็นปีพุทธศักราช
alter session set NLS_CALENDAR='THAI BUDDHA';
- ใช้ TO_CHAR กับ number
TO_CHAR
9 = Numeric position
0 = Leading Zero
$ = Floating dollar sign
L = Floating local currency
MI = Minus signs ทางขวา
PR = วงเล็บครอบเลขติดลบ

select salary, TO_CHAR(salary, '$0099,9999.00') x_salary from employees where last_name = 'Ernst';
- Nesting functions
select last_name, manager_id, NVL(TO_CHAR(manager_id), 'No Manager') from employees where manager_id is null;
select TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6), 'FRIDAY'),'fmDay, Month DDth, YYYY') "Next 6 Month Review" from employees order by hire_date;

5. ฟังก์ชั่นอื่น ๆ
NVL (expr1,expr2) ถ้า expr1 เป็น null จึง expr2 ไม่งั้นเป็น expr1
select last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) an_sal from employees;
select last_name, salary, NVL(commission_pct,0), (salary*12) + (salary*12*NVL(commission_pct,0)) an_sal from employees;
-- NVL2 (expr1,expr2,expr3) ถ้า expr1 เป็น null จึง expr3 ไม่งั้นเป็น expr2
select last_name, salary, commission_pct, NVL2(commission_pct,'SAL+COMM','SAL') income from employees where department_id in (50,80);
-- NULLIF (expr1,expr2) ถ้า expr1 = expr2 จึงให้มีค่าเป็น null ไม่งั้นเป็น expr1
select first_name, length(first_name) "expr1", last_name, length(last_name) "expr2", NULLIF(length(first_name), length(last_name)) result from employees;
-- COALESCE แสดงค่าที่เป็น Not Null ตัวแรกของสมาชิกในกลุ่มของ Expression
select last_name, commission_pct,salary,10, COALESCE(commission_pct, salary, 10) comm from employees order by commission_pct;

6. การใช้เงื่อนไขในคิวรี (Condition expression)
-- การใช้ CASE
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
เช่น
select last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
from employees;

-- การใช้ DECODE
DECODE (col | expression, search1, result1[, search2, result2,...,][,default])
select last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) revise_salary
from employees;

select last_name, salary,
DECODE(trunc(salary/2000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) tax_rate
from employees
where department_id = 80;

********************************************************************************
Chapter4: การ Join
********************************************************************************
-- ใช้คำสั่งแบบ Oracle
select table1.column, table2.column from table1, table2 where table1.column1=table2.column2;
select table1.column, table2.column from table1 join table2 on (table1.column1 = table2.column2);
1. Cartesian products
select last_name from employees;
107 rows
select department_name from departments;
207 rows
select last_name, department_name from employees, departments
2889 rows

2. Equijoins
select employees.employee_id, employees.last_name, employee.department_id, departments.department_id
, departments.location_id
from employees , departments
where employees.department_id = departments.department_id;
-- Table alias
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e, departments d
where e.department_id = d.department_id;
- Join more than 2 tables
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;

3. Non-Equijoins
select * from employees;
select * from jobs;
select e.last_name, e.salary, j.job_title,min_salary || ' - ' || max_salary
from employees e, jobs j
where e.salary between j.min_salary and j.max_salary;

4. Outer Joins
- เครื่องหมายบวก '(+)' อยู่ข้างที่ข้อมูลขาดหายไป (เป็น NULL)
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
ุุหรือใช้ SQL1999
select e.last_name, e.department_id, d.department_name
from employees e
left outer join departments d
on (d.department_id = e.department_id);
select * from employees;

5. Self Joins
- การที่ตาราง Join กับตัวมันเอง ใช้ในกรณีที่มีความสัมพันธ์เป็นลำดับชั้นในตาราง เช่น หัวหน้า-ลูกน้องเป็นต้น
select worker.last_name || ' works for ' || manager.last_name
from employees worker, employees manager
where worker.manager_id = manager.employee_id;


- ใช้คำสั่ง SQL มาตรฐาน (SQL1999)
1. Cross Joins (Cartesian Product)
select last_name, department_name
from employees CROSS JOIN departments;

2. Natural Joins
select department_id, department_name, location_id, city
from departments NATURAL JOIN locations;

3. Using
select e.employee_id, e.last_name, d.location_id
from employees e JOIN departments d USING (department_id)
where location_id = 1400;

4. Inner Join with 'ON'
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id);

5. Three-Way Joins with 'ON'
select employee_id, city, department_name
from employees e
JOIN departments d on d.department_id = e.department_id
JOIN locations l on d.location_id = l.location_id;

6. Left(Right) Outer Join
select e.last_name, e.department_id, d.department_name
from employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

select e.last_name, e.department_id, d.department_name
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id)

7. Full Outer Join
-- การ Join ที่แสดงค่าว่างของทั้งข้างซ้ายและขวาขึ้นมา เหมือนการเอา Left กับ Right Join มารวมกัน
select e.last_name, e.department_id, d.department_name
from employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

8. Additional Condition
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id) AND e.manager_id = 149;

********************************************************************************
Chapter5: ฟังก์ชั่นที่ใช้กับข้อมูลเป็นกลุ่มของแถว (Group Functions)
********************************************************************************
1. การใช้ฟังก์ชัน
AVG([DISTINCT | ALL] n) ไม่เอา Null (ถ้าข้อมูลเป็น NULL ไม่เอามาคำนวณ)
COUNT({* | [DISTINCT | ALL] expr}) ถ้าเป็น * เอา Null (ถ้าไม่ใช้ * ไม่เอา NULL)
MAX([DISTINCT | ALL] expr) ไม่เอา Null
MIN([DISTINCT | ALL] expr) ไม่เอา Null
STDDEV([DISTINCT | ALL] x) ไม่เอา Null
SUM([DISTINCT | ALL] n) ไม่เอา Null
VARIANCE([DISTINCT | ALL] x) ไม่เอา Null

-- ตัวอย่าง ===========================================
select AVG(salary), MAX(salary), MIN(salary), SUM(salary)
from employees
where job_id like '%REP%';

select MIN(hire_date), MAX(hire_date) from employees;

select COUNT(*) from employees where department_id = 50;
-- 45
select COUNT(commission_pct) from employees where department_id = 80;
-- 34
select COUNT(distinct department_id) from employees;
-- 11
select AVG (commission_pct) from employees;
-- คำนวณเฉพาะ Rows ที่มี commission_pct ไม่เป็น NULL เท่านั้น คือเอา Rows ที่ commisiton_pct ไม่เป็น Null แล้วหาค่าเฉลี่ยจาก Rows เหล่านี้
select AVG(nvl(commission_pct,0)) from employees;

2. การใช้ฟังก์ชั่น กับ GROUP BY
select department_id, AVG(salary) from employees GROUP BY department_id ORDER BY AVG(salary);
- Group มากกว่า 1 คอลัมน์
select department_id dept_id, job_id, sum(salary)
from employees
GROUP BY department_id, job_id;
- เงือนไขในการ Group (HAVING)
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 8000
- More
select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
- Nesting Group
select MAX(AVG(salary)) from employees group by department_id;

********************************************************************************
Chapter6: การใช้ Subquery
********************************************************************************
-- Single-row (ส่วนที่เป็น Subquery ให้ผลเพียงเรคคอร์ดเดียว)

select last_name, job_id from employees
where job_id =
(select job_id from employees where employee_id = 141);

select last_name, job_id, salary from employees
where job_id =
(select job_id from employees where employee_id = 141)
and salary >
(select salary from employees where employee_id = 143);

select last_name, job_id, salary from employees
where salary =
(select min(salary) from employees);

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary) from employees where department_id = 50);

-- Multi-row (ส่วนที่เป็น Subquery ให้ผลหลายเรคคอร์ด)
select last_name, salary, department_id
from employees
where salary IN
(select min(salary) from employees group by department_id);

-- การใช้ ANY
select employee_id, last_name, job_id, salary
from employees
where salary < ANY
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ใด ๆ ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนมากที่สุด"

select employee_id, last_name, job_id, salary
from employees
where salary < ALL
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ทุก SALARY ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนน้อยที่สุด"

********************************************************************************
Chapter7: การเปลี่ยนแปลงข้อมูล (Manipulation Data)
********************************************************************************
1. การแทรกแถวข้อมูล Insert
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (700, 'Public Relations', 100, 1700);

INSERT INTO departments (department_id, department_name)
VALUES (700, 'Public Relations');

INSERT INTO departments
VALUES (700, 'Public Relations',null, null);

- การแทรกแถวด้วยข้อมูลพิเศษ (เช่นวันที่ปัจจุบัน (SYSDATE))
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (999, 'Louis', 'Popp', 'LPOPP1', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);

- การแทรกแถวด้วยข้อมูลที่เป็นวันที่
INSERT INTO employees
VALUES (1999,'Den','Raphealy','xDraphel','515.127.4561', TO_DATE('1999 12', 'YYYY MM'), 'AC_ACCOUNT', 11000, null, 100,30);

2. การแทรกแถวข้อมูลโดยเอาแถวข้อมูลจากอีกตารางหนึ่ง
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
from employees
where job_id like '%REP%';

3. การเปลี่ยนแปลงข้อมูล
UPDATE employees SET department_id = 70
WHERE employee_id = 113;

UPDATE employees
SET job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
WHERE employee_id = 114;

UPDATE employees e
SET salary = (select max_salary from jobs j where j.job_id = e.job_id)
WHERE job_id like '%REP';

4. การลบแถวข้อมูล
DELETE from departments
WHERE department_name = 'Finance';

DELETE employees
WHERE department_id = (select department_id from departments where department_name like '%Public%');
-- เมื่อรันคำสั่งแล้ว เมื่อคิวรีด้วย Subquery ที่อยู่ใน Insert into จะต้องปรากฎเรคคอร์ดนั้นด้วย ถ้าไม่ จะขึ้น Error
insert into
(select employee_id, last_name, email, hire_date, job_id, salary,department_id from employees where department_id = 50 WITH CHECK OPTION)
values (999998, 'Smith','JSMITH',to_date('07-JUN-99','dd-mon-rr'),'ST_CLERK',5000,40);

5. การใช้ค่าดีฟอลต์ที่กำหนดไว้ในโครงสร้างตาราง
update jobs set max_salary = DEFAULT where job_id = 'IT_PROG';

6. การใช้ MERGE (เปรียบเทียบค่าในคอลัมน์ของตารางหนึ่งกับของอีกตารางหนึ่ง ถ้า Match (เหมือนกัน) ก็ทำอย่างหนึ่ง หรือถ้าไม่ Match ก็ทำอีกอย่าง)
MERGE INTO employees e
using copy_emp c
on (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.salary = e.salary + c.extra_bonus
WHEN NOT MATCHED THEN
INSERT VALUES (c.employee_id,c.first_name,c.last_name,c.email,c.phone_number,c.hire_date,c.job_id,c.salary,c.commission_pct,c.manager_id,c.department_id);

7. การควบคุมธุรกรรม (Transaction Control)
Controlling Transactions
- Session A ==========================================
select * from employees where employee_id = 100 for update;
- Session B ==========================================
update employees set salary = salary + 2 where employee_id =100;
-- Transaction hang
- Session A ==========================================
update employees set salary = salary + 1 where employee_id =100;
commit;
- Session B ==========================================
-- Transaction successful wait for DCL

- SAVEPOINT ช่วยให้สามารถ Rollback ถึงเพียงบางจุด ไม่ต้อง Rollback ทั้งหมด
select * from regions;
insert into regions values (5,'Africa');
select * from regions;
SAVEPOINT before_lunch;
insert into regions values (6,'South East Asia');
select * from regions;
ROLLBACK TO before_lunch;
select * from regions;
ROLLBACK;
select * from regions;

********************************************************************************
Chapter8: การสร้างและจัดการตาราง
********************************************************************************
CREATE TABLE EMPLOYEE
(empid NUMBER(10)
,lastname VARCHAR2(25)
,firstname VARCHAR2(25)
,salary NUMBER(10,4) DEFAULT 10)
TABLESPACE users;

select * from user_tables;
select * from user_catalog;
select * from cat;

-- ชนิดของข้อมูลที่มีใน Oracle
VARCHAR2(size) => Variable-length character; size is a maximum size (upto 4000 bytes)
CHAR(size) => Fixed-length character; size is a maximum size
NUMBER(p,s) => p is precision จำนวนหลักทั้งหมดรวมตัวเลขหลังจุดทศนิยมแต่ไม่รวมจุด and s is scale จำนวนหลักของตัวเลขหลังจุดทศนืืยม
DATE => Date and time values
CLOB => Character data upto 4GB
BLOB => Binary data upto 4 GB
BFILE => Binary data ที่เก็บในไฟล์ที่อยู่นอก Database ขนาดใหญ่สุด 4GB
ROWID => เลขฐานสิบหกที่ใช้ชี้ที่อยู่ของแถว ในฐานข้อมูล
TIMESTAMP => วันที่ รวมกับเศษส่วนของวินาที
INTERVAL YEAR TO MONTH => เก็บช่วงเวลาเป็นปีและเดือน
INTERVAL DAY TO SECOND => เก็บช่วงเวลาเป็นวันและชั่วโมง,นาที และวินาที
เช่น
create table test_time (time_begin TIMESTAMP(6));
select * from test_time
insert into test_time select hire_date from employees;
commit;
select time_begin from test_time;
-- สร้างตารางที่ใส่จำนวนปีได้ถึง 3 หลัก
drop table test_time2;
create table test_time2 (time_duration INTERVAL YEAR (3) TO MONTH);
-- Insert ข้อมูลเข้าไป 120 เดือน ตรง MONTH(2) ถ้าใส่ 3 หรือตัด (2) ออก ก็จะได้ค่าเท่ากัน ตราบใดที่ค่าที่ใส่เข้าไปยังอยู่ใน YEAR(3) หรือ 999 ปีก็ใช้ได้
insert into test_time2 (time_duration) values (INTERVAL '120' MONTH(3));
select * from test_time2;
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '120' YEAR(3));
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '10-2' YEAR(2) TO MONTH);
select sysdate + time_duration from test_time2;

drop table test_time3;
create table test_time3 (time_duration INTERVAL DAY (1) TO SECOND);
insert into test_time3 (time_duration) values (INTERVAL '3' DAY (3));
insert into test_time3 (time_duration) values (INTERVAL '3' HOUR (3));
select * from test_time3;
select sysdate + time_duration from test_time3;

--Create table by using a subquery
CREATE TABLE dept80
as
select * from employees where department_id = 80;

-- Alter table
ALTER TABLE dept80 ADD new_col varchar2(20);
ALTER TABLE dept80 MODIFY new_col char(5);
ALTER TABLE dept80 set unused column new_col;
select * from user_unused_col_tabs;
ALTER TABLE dept80 drop unused column;
-- Drop table
DROP TABLE dept80;
-- Flashback เอาตารางที่ Drop ไปแล้วกลับมา
select * from dept80;
FLASHBACK TABLE dept80 TO BEFORE DROP;
-- Rename table
RENAME test_time to test_time1;
-- Truncate table
TRUNCATE TABLE test_time1;

********************************************************************************
Chapter9: การใช้ Constraints
********************************************************************************
drop table employee2;
-- การสร้าง Constraints พร้อม ๆ กับตาราง
create table employee2
(
empid number(10) UNIQUE
,lastname number(25) NOT NULL
,firstname number(25) NOT NULL
,salary number(10,4) CHECK (salary > 100)
,department_id number(4)
)
tablespace users;

select * from user_constraints where table_name = 'EMPLOYEE2'
select * from user_cons_columns where table_name = 'EMPLOYEE2';

-- เพิ่ม Constraints ทีหลัง
ALTER TABLE employee2 ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);
ALTER TABLE employee2 ADD CONSTRAINT employee_uq UNIQUE (first_name,last_name);
ALTER TABLE employee2 ADD CONSTRAINT employee_ck CHECK (salary > 0);

ALTER TABLE employee2 ADD CONSTRAINT employee_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
ON DELETE CASCADE NOVALIDATE;
-- NOVALIDATE คือถ้ามีข้อมูลเก่าที่ละเมิดกฎอยู่ก็ไม่เป็นไร แต่ข้อมูลใหม่จากนี้ไปจะถูกบังคับด้วยกฎของ FK
-- สามารถใช้ ON DELETE SET NULL ก็ได้

ALTER TABLE employee2 DISABLE CONSTRAINT employee_fk;
ALTER TABLE employee2 ENABLE CONSTRAINT employee_fk;

ALTER TABLE employee2 DROP CONSTRAINT CASCADE CONSTRAINTS;
ALTER TABLE departments DROP CONSTRAINT dept_id_pk CASCADE;

select * from USER_CONSTRAINTS;
select * from USRE_CONS_COLUMNS;

********************************************************************************
Chapter10: การสร้างวิว (Creating Views)
********************************************************************************
CREATE OR REPLACE VIEW empvu80
AS select employee_id, last_name, salary
from employees
where department_id = 80;

CREATE OR REPLACE VIEW salvu50
AS select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
from employees
where department_id = 50;

CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

DROP VIEW dept_sum_vu;

-- Inline Views คือแทนตารางด้วยคิวรีในคำสั่ง
select a.last_name, a.salary, a.department_id, b.maxsal
from employees a, (SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id) b
where a.department_id = b.department_id
and a.salary < b.maxsal;

-- Top-N Analysis
-- การสร้างลำดับที่ และคิวรีเอาเฉพาะลำดับที่ ๆ ต้องการ
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <= 3;

select a.rank, a.last_name, a.salary from
(
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <=15
) a
where a.rank >=10;

********************************************************************************
Chapter11: การสร้างเลขอัตโนมัติ (Sequences)
********************************************************************************
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 320
MAXVALUE 9999
NOCACHE
NOCYCLE;

-- การตรวจดู sequences
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;

-- การใช้งาน sequences
insert into departments (department_id, department_name, location_id)
values (DEPT_DEPTID_SEQ.NEXTVAL,'Support', 2500);

select dept_deptid_seq.currval from dual;

-- ปัญหาการเกิดการข้ามหมายเลขใน sequences
เมื่อมีการใช้ Cache จะทำให้ระบบทำงานได้เร็วขึ้น แต่ก็อาจจะทำให้เกิดการข้ามหมายเลขได้ ถ้ามีการ Rollback หรือระบบ Down, เลขที่อยู่ใน Cache ก็จะหายไปหมด

-- การแก้ไข sequences จะแก้ได้ถ้าไม่ทำให้เกิดขัดแย้งกับเลขที่ได้รันไปแล้ว เช่นข้างล่างนี้ OK
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
CACHE 20
NOCYCLE;

-- แต่ข้างล่างนี้จะ Error ถ้ามีการแก้ให้ไปเริ่มต้นที่ค่าใหม่ (START WITH) เมื่อแก้ไม่ได้ก็ต้อง Drop และสร้าง Sequence ตัวใหม่
alter sequence dept_deptid_seq
increment by 20
maxvalue 999999
cache 20
nocycle
START WITH 20;

-- การลบ sequence
DROP SEQUENCE dept_deptid_seq;

********************************************************************************
Chapter12: การใช้ Index เพื่อเพิ่มประสิทธิภาพของคิวรี
********************************************************************************
-- การสร้าง Index
create index emp_last_name_idx on employees (last_name);
select * from employees where last_name = 'ABF'

drop index emp_last_name_idx;
create unique index emp_last_name_idx on employees (first_name,last_name);

select * from employees where first_name = 'ABF'

-- ควรจะใช้อินเด็กซ์ก็ต่อเมื่อ
1. มีความหลากหลายของค่าในคอลัมน์
2. มี Null จำนวนมาก
3. ถูกใช้ใน Where clause บ่อย
4. ตารางมีขนาดใหญ่ และคิวรีส่วนใหญ่จะดึงข้อมูลมาประมาณ 2-4 % ของ Rows ทั้งหมด
-- ไม่ควรใช้อินเด็กซ์เมื่อ
1. ตารางเล็ก
2. ข้อมูลไม่ได้ถูกใช้ใน Where Clause
3. คิวรีส่วนใหญ่ดึงข้อมูลมาเกิน 2 ถึง 4 เปอร์เซ็นต์ของจำนวน Rows ทั้งหมด
4. ข้อมูลถูก Update บ่อยมาก
5. คอลัมน์ที่ถูก Index เวลาถูกใช้ใน Where Clause จะเป็นส่วนหนึ่งของ Expression

SELECT * FROM EMPLOYEES WHERE TRUNC(HIRE_DATE) = DATE '2009-01-01';
create index idx_employees on employees (trunc(hire_date))

-- อินเด็กซ์ที่ใช้ร่วมกับฟังก์ชั่น (Function-Based Indexes)
create index upper_dept_name_idx on departments (UPPER(department_name));
-- บน SQLPlus ใช้คำสั่งนี้เพื่อให้ผลที่ได้เป็น Explain Plan แทนที่จะเป็นแถวข้อมูล
set autotrace traceonly explain
select * from departments where upper(department_name) = 'SALES';

-- การลบ Index
drop index upper_dpt_name_idx;

-- การใช้ Synonym เพื่อทำปลอกครอบชื่อตาราง เราสามารถอ้างอิงถึงตารางโดยชื่อตารางโดยตรง หรือโดยใช้ Synonym ก็ได้
create synonym d_sum for dept_sum_vu;
drop synonym d_sum;

********************************************************************************
Chapter13: การใช้ SET OPERATIONS
********************************************************************************
-- UNION เอามาทั้งสองชุด ที่ซ้ำกันไม่เอา
select employee_id, job_id
from employees
UNION
select employee_id, job_id
from job_history;

-- UNION ALL เอามาทั้งสองชุด มีเท่าไรเอามาให้หมด
select employee_id, job_id, department_id
from employees
UNION ALL
select employee_id, job_id, department_id
from job_history
order by employee_id;

-- INTERSECT เอาตัวที่มีอยู่ในทั้งสองชุด
select employee_id, job_id
from employees
INTERSECT
select employee_id, job_id
from job_history;

-- MINUS เอามาเฉพาะตัวที่อยู่ในชุดแรก และไม่อยู่ในชุดที่สอง
select employee_id, job_id
from employees
MINUS
select employee_id, job_id
from job_history;

select current_timestamp from dual
select sysdate from dual;
select dbtimezone from dual;
select extract (year from sysdate) from dual;
select extract (month from sysdate) from dual;
select extract (day from sysdate) from dual;
select hire_date, hire_date + to_yminterval ('01-02') as hire_date_ymininterval
from employees where department_id = 20;

********************************************************************************
Chapter14: เพิ่มเติมกับการใช้ GROUP BY
********************************************************************************
select department_id, job_id, SUM(salary), COUNT(employee_id)
from employees GROUP BY department_id, job_id;

-- ใช้ HAVING เพื่อ Filter เอาผลที่ต้องการ
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 9500;

select department_id, job_id, SUM(salary)
from employees
group by department_id, job_id

-- ใช้ ROLLUP เพื่อให้ทำการ Sum ผลลัพธ์ในแต่ละ Group โดยอัตโนมัติ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY ROLLUP (department_id, job_id);

-- ใช้ CUBE เพื่อให้แสดงทุก ๆ ความเป็นไปได้ของผลลัพธ์ในมิติต่าง ๆ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY CUBE (department_id, job_id);

-- ฟังก์ชัน Grouping ใช้เพื่อให้ผลการ Group ดูง่ายขึ้น
-- 0 หมายถึงรายละเอียด
-- 1 หมายถึงสรุป
select department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING (department_id) GRP_DEPT,
GROUPING (job_id) GRP_JOB
from employees
where department_id < 50
GROUP BY ROLLUP(department_id, job_id);

********************************************************************************
Chapter15: Advanced Subqueries
********************************************************************************
-- Subqueries
select last_name, job_id, salary
from employees
where salary < (select avg(salary)
from employees);
-- Pairwise Comparison subquery
-- ใน Where Clause มีคอลัมน์มากกว่าหนึ่ง
select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in
(select manager_id, department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Nonpairwise Comparison subquery
-- คิวรีนี้ให้ผลเหมือนกับคิวรี Pairwise ข้างบน สังเกตว่าคอลัมน์ทั้งสอง (manager_id,department_id) ถูกแยกออกเป็นสองเงื่อนไข และใช้ Subquery คนละตัวกัน
select employee_id, manager_id, department_id
from employees
where manager_id in
(select manager_id from employees where employee_id in (178,174))
and department_id in
(select department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Subquery in the FROM Clause
-- คิวรีข้างล่างแทนที่จะใช้สองตาราง Join กัน กลับใช้ 1 ตาราง กับอีก 1 คิวรี (ซึ่งใช้แทนตาราง) มา Join กันแทน
select a.last_name, a.salary, a.department_id, b.salavg
from employees a, (select department_id, AVG(salary) salavg from employees group by department_id) b
where a.department_id = b.department_id
and a.salary > b.salavg;

-- Scalar Subquery Expressions
-- เป็น Subquery ที่จะให้ค่าที่ Select ขึ้นมาเพียงหนึ่งค่า (หนึ่งแถวและหนึ่งคอลัมน์ ค่าเดียว จึงเรียกว่า Scalar)
-- เราใช้ Scalar Subquery แทนการ Join โดยเฉพาะ Outer Join
select last_name,d.department_name from employees e, departments d where e.department_id = d.department_id (+)
-- จากตัวอย่างเรา Select จากตาราง EMPLOYEES เท่านั้น ไม่ต้องทำ Outer Join กับ DEPARTMENTS เราจึงแน่ใจว่าคิวรีข้างล่างจะแสดงผลทุกแถวของ EMPLOYEES เสมอ
select last_name,(select department_name from departments d where d.department_id = e.department_id) dept_name from employees e
-- In CASE Expressions
-- เราสามารถใช้ Scalar Subquery ใน Case Expression ได้เช่นกัน
select employee_id, last_name
, (case
when department_id =
(select department_id from departments where location_id = 1800)
then 'Canada' else 'USA' end) location
from employees;

-- ORDER BY Expressions
-- เราสามารถใช้ Scalar Subquery ใน ORDER BY Clause ก็ได้
-- ตัวอย่างข้างล่างเป็นการ ORDER BY department_name แม้ว่าข้อมูลที่ Select ขึ้นมาจะไม่มีคอลัมน์นี้ (และในตาราง EMPLOYEES ก็ไม่มีคอลัมน์นี้)
select employee_id, last_name, department_id
from employees e
ORDER BY (select department_name from departments d where e.department_id = d.department_id);
-- ลอง Select ดูจากตาราง DEPARTMENTS โดย Order by DEPARTMENT_NAME
select department_name,department_id from departments order by department_name;

-- Correlated Subqueries
-- คือ Subquery ที่จะ Return ค่าที่ผันแปรตามค่าในเรคคอร์ดของคิวรีหลัก
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
from employees
where department_id = outer.department_id);

-- Another example
select employee_id, last_name, e.job_id
from employees e
where 2 <= (select count(*)
from job_history
where employee_id = e.employee_id);

-- EXISTS Operators
-- เราใช้ EXISTS แทน IN และ NOT EXISTS แทน NOT IN
select * from departments for update
select distinct department_id from employees
select * from departments

select distinct department_id from departments where department_id not in (select nvl(department_id,0) from employees )
select department_id from departments d where not exists (select 'I love you' from employees e where e.department_id = d.department_id)

select * from employees e where e.department_id not in (select department_id from departments d)
select * from departments where department_id like '1%'
select * from departments d
where not exists
(select 1 from employees e where e.department_id = d.department_id)

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select department_id, department_name
from departments d
where department_id not exists (select 0 from employees where department_id = d.department_id);

-- Correlated Update
-- การ Update ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
alter table employees add (department_name varchar2(14));

update employees e
set department_name = (select department_name
from departments d
where e.department_id = d.department_id);

-- Correlated Delete
-- การ Delete ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
delete from employee e
where employee_id = (select employee_id
from emp_history
where employee_id = e.employee_id);

delete from job_history jh
where employee_id = (select employee_id
from employees e
where jh.employee_id = e.employee_id
and start_date = (select min(start_date)
from job_history jh
where jh.employee_id = e.employee_id)
and 5 > (select count(*)
from job_history jh
where jh.employee_id = e.employee_id
group by employee_id
having count(*) > =4));

-- WITH
-- เปรียบ WITH คล้าย ๆ กับการใช้ View เพียงแต่เราไม่ต้องสร้าง View เอาไว้ก่อน การใช้ WITH ช่วยให้การเขียนคิวรีที่ต้องอ้างถึง Subquery ตัวเดิมซ้ำ ๆ กัน สามารถกำหนด Subquery ไว้ที่เดียว ทำให้คิวรีสั้นขึ้น ดูง่ายขึ้น และแก้ไขได้ง่ายขึ้น
with
dept_costs as (
select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name),
avg_cost as (
select sum(dept_total) / count(*) as dept_avg
from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name

********************************************************************************
Chapter16: Extension of DML and DDL
********************************************************************************
create table mgr_history as select employee_id,manager_id,salary from employees where 1=0;
create table sal_history as select employee_id,hire_date,salary from employees where 1=0;

-- Unconditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง (หรือมากกว่า)
INSERT ALL
INTO sal_history (employee_id,hire_date,salary) values (empid, hiredate,sal)
INTO mgr_history (employee_id,manager_id,salary) values (empid, mgr, sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;

rollback;

-- Conditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง แบบมีเงื่อนไขว่า ถ้า.. ให้ Insert เข้าตาราง...
INSERT ALL
WHEN sal > 10000 THEN
INTO sal_history values (empid,hiredate,sal)
WHEN mgr > 200 THEN
INTO mgr_history values (empid,mgr,sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;
select * from sal_history;
select * from mgr_history;

-- Conditional FIRST INSERT ถ้า Condition แรกเป็นจริงก็ข้ามไปแถวต่อไป
INSERT FIRST
WHEN sal > 25000 THEN
INTO special_sal VALUES (deptid, sal)
WHEN hiredate like ('%') then
INTO hiredate_history_00 VALUES (deptid, hiredate)
WHEN hiredate like '%99%') then
INTO hiredate_history_99 VALUES (deptid,hiredate)
ELSE
INTO hiredate_history values (dept_id, hiredate)
SELECT department_id deptid, sum(salary) sal,
max(hire_date) hiredate
FROM employees
GROUP BY department_id;

-- Pivot INSERT กลับคอลัมน์เป็นแถว
-- เราสามารถใช้ INSERT ALL ในการทำ Pivot ก็ได้
-- จากตัวอย่างข้างล่างเดิมปริมาณการขายเก็บของแต่ละวันในสัปดาห์ ถูกเก็บไว้แยกเป็นคอลัมน์วัน (จันทร์, อังคาร, ...) เราต้องการนำข้อมูลเหล่านี้มาเก็บในอีกตารางที่อยู่ในรูปของการเก็บแบบแต่ละวันของสัปดาห์ในรูปของแต่ละเรคคอร์ด
INSERT ALL
INTO sales_info values (employee_id,week_id,sales_MON)
INTO sales_info values (employee_id,week_id,sales_TUE)
INTO sales_info values (employee_id,week_id,sales_WED)
INTO sales_info values (employee_id,week_id,sales_THU)
INTO sales_info values (employee_id,week_id,sales_FRI)
select employee_id, week_id,sales_MON,sales_TUE,sales_WED,sales_THU,sales_FRI
from sales_source_data;

********************************************************************************
Chapter17: External Tables
********************************************************************************
เราสามารถ Select ข้อมูลจากเท็กซ์ไฟล์ข้างนอกระบบฐานข้อมูลได้

1. สมมติเรามีเท็กซ์ไฟล์ที่มีข้อมูลดังนี้
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

2. การ Select จากไฟล์ จะต้องเก็บไฟล์ไว้ในไดเรคทอรีที่ Oracle รู้จัก โดยในตัวอย่างนี้เราจะเก็บไฟล์ไว้ในไดเรคทอรีบนดาต้าเบสที่ชื่อว่า 'DATA_PUMP_DIR' ซึ่งมีมากับดาต้าเบสโดยดีฟอลต์ การจะเช็คดูว่ามี directory แล้วหรือยังและไดเรคทอรีดังกล่าวแม็ปกับไดเรคทอรีบน OS ใด เราสามารถใช้คำสั่ง (จะต้อง Login user ที่มีสิทธิ์ DBA)
select * from dba_directories where directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ --------------------- ----------------------------------
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle\admin\XE\dpdump\
เราอาจจะสร้าง directory ใหม่เพื่อให้แม็ปกับ directory ที่เราต้องการก็ได้โดยการสร้างจะต้องมีสิทธิ์ที่เป็น DBA เช่นกัน

3. Save ไฟล์ในข้อ 1 ด้วยชื่อ "emp.dat" ไว้ในไดเรคทอรีที่แม็ปไว้กับไดเรคทอรี DATA_PUMP_DIR ใน Oracle
4. ผู้จะสร้าง External Table จะต้องมีสิทธิ์ Read,write ในไดเรคทอรีที่เก็บไฟล์ (จะต้องให้ user ที่มีสิทธิ์ DBA grant ให้)
grant read,write on directory data_pump_dir to scott;
Grant succeeded

5. จากนั้นล็อกอินด้วย user ทีต้องการสร้างตารางแล้วสร้างตารางดังข้างล่าง
create table emp_ext
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
organization external
(type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp.dat')
)
reject limit 1000;
โดยคอลัมน์ที่กำหนดจะต้องสอดคล้องกับข้อมูลในไฟล์
- ตรง 'location' เป็นตัวกำหนดว่าชื่อไฟล์ที่จะให้ไปอ่านชื่ออะไร
- โดย Oracle จะไปอ่านไฟล์ดังกล่าวตามที่กำหนดไว้ใน default directory
- 'fields terminated by' เป็นตัวกำหนดว่าข้อมูลแต่ละคอลัมน์ถูกแยกจากกันด้วยสัญญลักษณ์อะไร
- 'reject limit' เป็นตัวกำหนดว่าถ้ามีข้อผิดพลาดในการอ่านไม่เกินจำนวนเท่าไรจึงจะแสดง error และไม่ต้องอ่านต่อ

6. หลังจากสร้างตารางเสร็จแล้ว เราสามารถ select ข้อมูลได้เหมือนตารางปกติ
select * from emp_ext;

********************************************************************************
Chapter18: Hierarchical Retrieval
********************************************************************************
-- คิวรีข้างล่างแสดงข้อมูลของพนักงาน โดยเรคคอร์ดถัดไปจะเป็นเรคคอร์ดของหัวหน้าของเรคคอร์ดที่อยู่ก่อนหน้า
select employee_id, last_name, job_id, manager_id
from employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
-- เราสามารถใช้ PRIOR เพื่อแสดงผลในคอลัมน์ได้
select level,last_name || ' reports to ' ||
PRIOR last_name "Walk Top Down", level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

-- Using Level to format Hierarchical Reports
-- เราใช้ LEVEL เพื่อแสดงลำดับที่ในผลของคิวรีได้ ตัวอย่างข้างล่างในใช้ LEVEL เพื่อจัดรูปแบบของ Report ในลักษณะของแผนภูมิแบบลำดับชั้น (Hierarchical)
select LPAD(last_name, length(last_name) + (level*2) -2 , '_') as org_chart, level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
-- เราสามารถเพิ่มเงื่อนไขใน CONNECT BY ได้
select employee_id, last_name, job_id, manager_id
from employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id and last_name != 'Higgins'

-- Use WHERE to eliminate a node
-- เราใช้ WHERE เพื่อตัดเฉพาะบางโหนด (คน) ออกไป คิวรีข้างล่างเฉพาะเรคคอร์ดของ 'Higgins' ถูกตัดออกไป
select department_id, employee_id,last_name, job_id,salary, manager_id
from employees
where last_name != 'Higgins'
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id;

-- Use CONNECT BY to eliminate a branch
-- เราเพิ่มเงื่อนไขใน CONNECT BY เพื่อตัดเฉพาะบางกิ่ง (โหนดและโหนดลูก,หลาน) ออกไป คิวรีข้างล่างเรคคอร์ดของ 'Higgins' และเรคคอร์ดที่เป็นลูกน้องของ 'Higgins' จะถูกตัดออกไป
select department_id, employee_id, last_name, job_id, salary, manager_id
from employees
start with last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Higgins';

Tuesday, July 14, 2009

วิธีการ Migrate Character Set ที่เหมาะสม (ตอนที่ 2, ตอนจบ)

(แปลจากบางส่วนของ “Character Set Migration Best Practices", An Oracle White Paper January2005)

การแปลง และสิ่งที่ต้องทำหลังจากการแปลง
วิธีการต่าง ๆ ในการ Migrate
Oracle ได้เตรียมคำสั่ง, เครื่องมือ และบริการที่จะช่วยให้ใช้เวลาในการปิดระบบน้อยที่สุด ในขณะเดียวกันก็ปลอดภัยต่อข้อมูลมากที่สุด ซึ่งไม่ว่าจะใช้วิธีใด จะต้องทำการ Pre-Scan ข้อมูลโดยการใช้โปรแกรมที่ชื่อ Character Set Migration ดังได้กล่าวมาแล้ว

โปรแกรม Export/Import
โปรแกรม Oracle Export และ Import เป็นวิธีง่าย ๆ ในการย้ายข้อมูลระหว่างสองฐานข้อมูลที่เป็น Oracle ทั้งคู่ แม้ว่าทั้งคู่จะอยู่บนต่างฮาร์ดแวร์, OS หรือ (Database) Configuration เมื่อคุณ Export วัตถุใด ๆ ในฐานข้อมูล (เช่น Tables) วัตถุนั้น ๆ จะถูกดึง (แต่ไม่ได้ลบเอาวัตถุนั้นออกจากฐานข้อมูล) ออกมาพร้อมทั้งวัตถุที่เกี่ยวข้อง (เช่น Indexes, Comments และ Grants) ข้อมูลที่ถูกดึงออกมา จะถูกเขียนลงในไฟล์ Dump ที่เป็นฟอร์แมตของ Oracle โดยเฉพาะ และสามารถที่จะนำเข้าฐานข้อมูลเฉพาะ Oracle โดยการใช้โปรแกรม Oracle Import เท่านั้น โปรแกรม Import อ่านโครงสร้างของวัตถุและข้อมูลในวัตถุจากไฟล์ Dump และ Insert เข้าไปในฐานข้อมูล Oracle

ตั้งแต่ Oracle9i เป็นต้นมา โปรแกรม Export จะดึงข้อมูลโดยใช้ Character Set ของ Server (Character Set จะถูกกำหนดเมื่อตอนสร้างฐานข้อมูล) ย้อนหลังไป Oracle8i โปรแกรม Export จะดึงข้อมูลโดยใช้ Character Set ที่กำหนดโดยพารามิเตอร์ NLS_LANG ที่กำหนดไว้ที่เครื่องที่ใช้ในการ Export (ซึ่งอาจจะไม่ใช่ Database Server เอง อาจจะเป็นเครื่อง Client ก็ได้) เมื่อทำการนำข้อมูลเข้า โปรแกรม Import จะแปลงข้อมูลให้เป็น Character Set ของเครื่องที่ Import ข้อมูลเข้าโดยอัตโนมัติ คุณจะต้องระวังว่า Character Set ของไฟล์ Export (Dump)จะต้องเป็น Subset ของ Character Set ของ Import Server มีข้อจำกัดบางประการเมื่อ Export จากฐานข้อมูลที่มีชุดตัวอักษรเป็นแบบหลายไบท์ ดูรายละเอียดจาก Oracle Database Utilities Guide ในหัวข้อการ Export และ Import โปรแกรม Export/Import ใช้เทคโนโลยีที่เรียกว่า Oracle Stream ซึ่งทำให้สามารถทำได้โดยไม่ต้องปิดระบบฐานข้อมูลหรือถ้าปิดก็ใช้เวลาน้อยมาก

การใช้สคริปต์ CSALTER
สคริปต์ CSALTER เป็นเครื่องมือของ DBA สำหรับทำการ Migrate ชุดตัวอักษร CSALTER เป็นส่วนหนึ่งของ Character Set Scanner Utilities และเป็นสคริปต์ที่ใช้ในการ Migrate ชุดตัวอักษรที่ตรงไปตรงมาที่สุด แต่สามารถจะใช้ได้เฉพาะในบางสถานการณ์เท่านั้น กล่าวคือข้อมูลในฐานข้อมูลจะต้องใช้ชุดตัวอักษรที่เป็น Strict Subset(ทุกตัวอักษรในชุดจะต้องเป็นส่วนหนึ่ง)ของชุดตัวอักษรปลายทาง หรืออีกนัยหนึ่งชุดตัวอักษรปลายทางจะเป็น Strict Superset ของชุดตัวอักษรต้นทางก็ต่อเมื่อ
1) ตัวอักษรแต่ละตัว และทุก ๆ ตัวจะต้องมีในชุดตัวอักษรปลายทาง
2) ตัวอักษรแต่ละตัว และทุก ๆ ตัวจะต้องมีรหัส (Code Point) เดียวกันกับตัวอักษรในชุดปลายทาง เช่น ชุดตัวอักษรหลายชุดที่เป็น Strict Superset ของ US7ASCII

อย่างไรก็ตาม ในกรณีของ CLOB สคริปต์ CSALTER จะทำการแปลงได้เฉพาะคอลัมน์ CLOB ใน Data Dictionary และ Sample Schema ซึ่งถูกสร้างโดย Oracle เท่านั้น คอลัมน์ CLOB ที่ user สร้างขึ้นมาเองจะต้องถูกจัดการแยกต่างหาก จาก Oracle9i เป็นต้นมา Field ที่ใช้ภายในบางตัวของ Data Dictionary และ Sample Schema ถูกเก็บในคอลัมน์ CLOB กรณีเป็นคอลัมน์ CLOB ที่สร้างขึ้นเอง ถ้าชุดตัวอักษรเป็นแบบหลายไบท์ ข้อมูลที่เป็น CLOB จะถูกเก็บในรูปแบบที่สอดคล้องกับข้อมูลที่เป็น UCS-2 แต่ถ้าชุดตัวอักษรเป็นแบบไบท์เดี่ยว ข้อมูล CLOB จะถูกเก็บโดยใช้ชุดตัวอักษรของฐานข้อมูลเองCSALTER จะแปลงข้อมูลในคอลัมน์ CLOB ใน Data Dictionary และใน Sample Schema เท่านั้น คอลัมน์ CLOB อื่น ๆ คุณจะต้อง Export ออกมาและ Drop ทิ้งก่อนที่จะใช้สคริปต์ CSALTER

การใช้ CSALTER ร่วมกับการ Export/Import แบบเลือกเฉพาะบางรายการ
CSALTER เป็นวิธีที่อาจจะเร็วที่สุดในการ Migrate เนื่องจากมันไม่ได้ทำการการแปลงข้อมูลใด ๆ ยกเว้นกับคอลัมน์ CLOB ที่อยู่ใน Data Dictionary และใน Sample Schema ซึ่งสร้างขึ้นโดย Oracle (ตอนสร้างฐานข้อมูล) บ่อยครั้งที่ข้อมูลส่วนใหญ่ในชุดตัวอักษรต้นทางเป็น Strict Subset ของชุดตัวอักษรปลายทาง แต่อย่างไรก็ตามถ้าหากมีข้อมูลที่มีลักษณะที่อาจจะต้องมีการเปลี่ยนแปลงจากโคัดหนึ่งไปยังอีกโคัดหนึ่ง (Convertible) แม้เพียงนิดเดียวก็จะทำให้ไม่สามารถใช้ CSALTER

การ Export/Import ข้อมูลทั้งฐานอาจจะใช้เวลาและทรัพยากรของเครื่องมากหากเป็นฐานข้อมูลขนาดใหญ่ ซึ่งเราสามารถใช้วิธีลูกผสมในการนี้ได้ โดยเบื้องต้นข้อมูลจำนวนน้อยส่วนที่เป็น Convertible จะถูก Export ไปเป็นไฟล์ Dump และถูกลบออกจากฐานข้อมูล ซึ่งจะทำให้เราสามารถใช้สคริปต์ CSALTER ได้ จากนั้นเราก็จะ Import ส่วนข้อมูลจากไฟล์ Dump กลับเข้ามาเป็นชุดตัวอักษรใหม่ ดู Case Study3 ซึ่งจะอธิบายถึงสภาวะที่เหมาะสม ส่วนเทคนิคในการแปลงแบบนี้ กรุณาดู Oracle Database 10g Globalization Guide เกี่ยวกับเรื่อง CSALTER

เตรียมแบ็คอัพ
ในขั้นตอนนี้ข้อมูลต้นฉบับได้ถูกสแกน และได้เลือกวิธีการในการแปลงไว้แล้ว การทำการแบ็คอัพเป็นเรื่องจำเป็นโดยเฉพาะกับเครื่องที่เป็น Production ก่อนที่จะทำการแปลง ถ้าข้อมูลถูกแก้ไขหลังจากการสแกนครั้งล่าสุด ก็ให้รัน Database Character Set Scanner อีกที ถ้าจำเป็น

เช็คดูว่ามีพื้นที่เพียงพอ
เมื่อคุณ Migrate จากชุดข้อมูลแบบไบท์เดี่ยวมาเป็นหลายไบท์ จะทำให้ต้องใช้พื้นที่เพิ่มขึ้นเป็นจำนวนมากสำหรับฐานข้อมูลตัวใหม่ เราสามารถทำการคำนวณการขยายตัวของข้อมูลได้จากสัดส่วนของจำนวนไบท์ระหว่างตัวต้นทางกับตัวปลายทาง เช่นถ้า Migrate จากไบท์เดี่ยวไปยังหลายไบท์แบบความกว้างคงที่ (Fixed Width Multibyte Character Set เช่น AL16UTF16 จะกินพื้นที่สองไบท์ต่อหนึ่งตัวอักษรเสมอไม่ว่าจะเป็นตัวอักษรใด) การคำนวณก็จะตรงไปตรงมาคือคูณด้วยสอง แต่สำหรับการแปลงไปยังหลายไบท์แบบความกว้างไม่คงที่ (Variable Width Multibyte Character Set เช่น AL32UTF8) การคำนวณจะซับซ้อนกว่า เช่นกรณีของ UTF-8 ถ้าเป็นส่วนที่เป็น ASCII จะใช้หนึ่งไบท์ต่อหนึ่งตัวอักษร ตัวอักษรละตินที่มี Accent, ภาษา Greek, Cyrillic, Arabic และภาษา Hebrew จะใช้สองไบท์ ส่วนภาษาอื่น ๆ รวมทั้งภาษาจีน, ญี่ปุ่น, เกาหลี และอินเดียจะใช้สามไบท์สำหรับแต่ละตัวอักษร และถ้าเป็น ตัวอักขระพิเศษ (Supplementary Character) จะใช้สี่ไบท์สำหรับแต่ละตัวอักษร

ซ้อมรัน
แนะนำให้ทำการทดสอบการรันบนฐานทดสอบ ซึ่งจะช่วยให้คุ้นเคยกับขั้นตอนทั้งหมด ซึ่งจะลดเวลาในการปิดระบบ และยังจะช่วยให้กะเวลาได้ว่าจะใช้เวลาทั้งหมดเท่าใด และจะต้องใช้ทรัพยากรเท่าใด และเป็นจังหวะที่เราสามารถทดสอบดูด้วยว่า Application ดั้งเดิม (Legacy) หรือ Application ใหม่สามารถทำงานได้ไม่มีปัญหากับชุดตัวอักษรชุดใหม่

รันการแปลง และขั้นตอนการตรวจสอบหลังการแปลง
ทันทีที่การแปลงเสร็จสิ้น และฐานข้อมูลได้เปิดใช้งาน ให้ตรวจสอบว่าการแปลงข้อมูลทำได้ถูกต้อง ถ้าเราใช้โปรแกรม Import ให้ตรวจสอบดูว่าไม่มีข้อความเตือนใด ๆ ใน Log เราอาจจะตรวจสอบข้อมูลสักกลุ่มหนึ่งว่าถูกต้อง และเราอาจจะรัน Database Character Set Scanner อีกครั้งหนึ่งเพื่อเช็คหา Exception ทดสอบดูทุก ๆ Application ที่ใช้ฐานข้อมูลเพื่อให้แน่ใจว่ายังทำงานได้ตามปกติ และท้ายสุดตรวจสอบระบบอย่างใกล้ชิดสักช่วงระยะเวลาหนึ่งเพื่อดูประสิทธิภาพในการทำงานว่าลดลงหรือไม่ (ประสิทธิภาพที่ลดลงอาจจะมาจากขนาดของแต่ละตัวอักษรที่ใหญ่ขึ้น)

กรณีศึกษา
กรณีศึกษาข้างล่าง มีเพื่อเป็นตัวอย่างของวิธีการ, เทคนิค และการตัดสินใจที่จะต้องทำ ในการทำการ Migrate ชุดตัวอักษรให้ปลอดภัย

1) Migrate ฐานข้อมูลที่ใช้ US7ASCII เพื่อให้รองรับภาษาได้มากขึ้น
ผู้ใช้รายหนึ่งมีความต้องการที่จะ Migrate ฐานข้อมูลจากตัวเดิมที่เป็น US7ASCII เพื่อให้รองรับภาษาในยุโรปตะวันตก และประเทศในเอเชีย เครื่อง Client ที่ใช้ติดต่อกับฐานข้อมูลเป็น Windows ภาษาอังกฤษ โดยได้ตั้งค่า NLS_LANG เป็น AMERICAN_AMERICA.US7ASCII ดังนั้นการติดต่อจาก Client ไปฐานข้อมูลจึงเป็นแบบ PASS-THUR ผู้ใช้คิดว่าข้อมูลที่เข้ามาน่าจะเป็น ASCII แท้ร้อยเปอร์เซนต์ แต่หลังจากรัน Database Character Set Scanner โดยให้ชุดตัวอักษรต้นทางเป็น US7ASCII (ASCII 7 บิท) และปลายทางเป็น AL32UTF8 กลับพบว่า มี Exception โดยเมื่อผ่านการตรวจสอบแล้วพบว่ามีตัวอักษรท้องถิ่น และตัวอักษรพิเศษ (ที่เป็นตัวอักษร 8 บิท) ที่อยู่ในฐานข้อมูลอย่างผิดปกติ ตอนนี้ผู้ใช้จะต้องตัดสินใจว่าจะทำยังไงต่อ ถ้าข้อมูลที่เป็น 8 บิทไม่สำคัญ ผู้ใช้ก็อาจจะตัดสินใจลบข้อมูล หรือทำการแก้ไขข้อมูลนั้น เช่นแก้จากตัวอักษร 'ä' เป็นตัว 'a' เมื่อข้อมูล "สะอาด" และได้สแกนดูอีกทีแล้วว่าเป็นข้อมูลที่เป็นแบบ Changeless ทั้งหมด (คือเมื่อแปลงแล้วตัวอักษรจะมี Code Point เหมือนเดิม) ก็สามารถใช้ CSALTER ได้

ในอีกทางหนึ่งถ้าข้อมูลที่เป็น 8 บิทจำเป็นจะต้องเก็บไว้ก็ต้องใช้วิธีการอื่น โดยจะต้องทำการตรวจดูว่าข้อมูลโดยทั่วไปมีลักษณะเป็นอย่างไร สมมติว่าข้อมูล 8 บิทนั้นมาจาก Windows ภาษาอังกฤษ เราอาจจะลองใช้ Scanner และเลือกชุดตัวอักษรต้นทางที่เราคิดว่าครอบคลุมชุดตัวอักษรของข้อมูล สิ่งหนึ่งที่ต้องสังเกตคือชุดตัวอักษร Latin1 (WE8ISO8859P1) และ WE8MSWIN1252นั้นแม้ว่าจะมีความคล้ายกันมาก แต่ก็ไม่เหมือนกัน ชุดตัวอักษร WE8MSWIN1252 ของไมโครซอฟต์นั้น มีตัวอักษรเพิ่มเติมซึ่งใช้ทุก ๆ รหัสที่อยู่ในช่วง 8 บิท ดังนั้นถ้าเราตั้งให้ Scanner ใช้ WE8MSWIN1252 ก็จะไม่ช่วยในการหา Exception ที่อยู่ในช่วงของชุดตัวอักษร 8 บิท วิธีการที่ดีกว่าน่าจะเป็นการตั้งค่าตัวอักษรต้นทางเป็น WE8ISO8859P1 และเลือกตัวปลายทางเป็นตัวที่ต้องการเช่น AL32UTF8 ผลการสแกนอาจจะแสดงว่าอาจจะเกิดการตัดแบ่ง (Truncation) เนื่องจากตัวอักษรยุโรป (เดิมที่ใช้หนึ่งไบท์) ที่เป็นตัว Accented จะใช้สองไบท์ใน UTF-8 ดังนั้นถ้าไม่เพิ่มขนาดคือยังคงใช้หนึ่งไบท์จะทำให้การแปลงข้อมูลผิดพลาด ดังนั้นจึงต้องมีการเปลี่ยนแปลงขนาดของคอลัมน์ด้วย ถ้าผลการสแกนแจ้งว่าข้อมูลทั้งหมดสามารถที่จะถูกแปลงได้ (Convertible) และไม่มีคอลัมน์ใด ๆ ที่จะถูกตัดแบ่ง ก็หมายความว่าข้อมูลสามารถจะถูก Migrate ได้อย่างปลอดภัยโดยใช้โปรแกรม Export และ Import มีขั้นตอนสองขั้นตอนที่ต้องทำ ถ้าชุดตัวอักษรเดิมเป็น US7ASCII เราจะต้องรันสคริปต์ CSALTER เพื่อจะเปลี่ยนชุดตัวอักษรไปเป็น WE8MSWIN1252 หรือ WE8ISO8859P1 จากนั้นก็การ Export (แบบ Full) และ Import เพื่อที่จะแปลงข้อมูลทั้งหมดไปเป็น UTF-8

2) Migrate จาก WE8ISO8859P1 ไปเป็น WE8ISO8859P15 เพื่อให้สนับสนุนตัวอักษรยูโร (€)
ตัวอย่างนี้เป็นการ Migrate จาก WE8ISO8859P1 (Latin1) ไปเป็น WE8ISO8859P15 (Latin9) เพื่อให้รองรับตัวยูโร WE8ISO8859P1 มีชุดตัวอักษรบางตัวที่ไม่มีใน WE8ISO8859P15 {เช่น ¼,½,¾,¤ และอื่น ๆ} ถ้าผลจากการสแกนแสดงว่าข้อมูลทั้งหมดเป็น Changeless ก็สามารถที่จะใช้สคริปต์ CSALTER ได้ สังเกตว่าในสคริปต์ CSALTER เพียงแค่ตรวจสอบว่าข้อมูลใน Schema ที่มีอยู่ (ไม่ใช่ชุดตัวอักษร) เป็น Strict Subset ของชุดตัวอักษรปลายทาง ถ้าผลจากการสแกนบอกว่าข้อมูลทั้งหมดไม่ได้เป็นแบบ Changeless เราจะต้องตัดสินใจว่าจะทำยังไงกับข้อมูลที่ไม่สามารถแปลงได้ (Non-Convertible) เราอาจจะต้องใช้ Oracle Locale Builder เพื่อที่จะตัดแต่ง WE8ISO8859P15 เพื่อให้สามารถรองรับตัวอักษรใน WE8ISO8859P1 ที่มันไม่รองรับ แล้วจึง Migrate โดยใช้ Export และ Import เพื่อให้ฐานข้อมูลรองรับตัวอักษรเพิ่มเติมเหล่านี้

3) Migrate จากชุดตัวอักษรยุโรปตะวันตกไปเป็น AL32UTF8 เพื่อให้สามารถรองรับภาษาเอเชียได้
ฐานข้อมูลส่วนใหญ่ที่ใช้ชุดตัวอักษรยุโรปตะวันตกจะมีตัวอักษรที่อยู่ในชุดของ ASCII เป็นจำนวนมาก ถ้าเราเลือก Migrate เฉพาะคอลัมน์ที่เก็บข้อมูลที่ใช้ชุดตัวอักษรยุโรปตะวันตก เราจะเหลือแต่ข้อมูลที่ใช้ชุดตัวอักษร ASCII ซึ่งจะทำให้เราสามารถใช้สคริปต์ CSALTER ได้ ประโยชน์ของวิธีการนี้คือสามารถลดเวลาที่ปิดฐานข้อมูลเพื่อแปลงข้อมูลโดยใช้โปรแกรม Export และ Import การสแกนจะแสดงผลว่า ASCII เป็นข้อมูลที่ Changeless ในขณะที่ข้อมูลภาษายุโรปตะวันตกจะเป็น Convertible
- ถ้ามีการใช้ตัวอักษรยุโรปตะวันตก (ตัวอักษรที่ถูก Accent และตัวอักษรพิเศษเช่น ตัวยูโร (€) ซึ่งจะถูกแปลงเป็นสองไบท์ใน UTF-8) เป็นจำนวนมาก และกระจัดกระจายทั่วไปในฐานข้อมูล การทำ Full Export/Import น่าจะเป็นวิธีการที่ดีที่สุด
- ถ้ามีการใช้ตัวอักษรยุโรปตะวันตกเป็นจำนวนน้อย และแยกอยู่ในตารางไม่กี่ตาราง การเลือก Export/Import เฉพาะตารางเหล่านั้นก็ทำให้งานง่ายขึ้น โดยปล่อยให้ส่วนที่เหลือของฐานข้อมูลถูกแปลงโดยใช้สคริปต์ CSALTER หลังจากที่การสแกนให้ผลว่าส่วนที่เหลือนั้นเป็น Changeless
- ถ้าการใช้งานภาษายุโรปตะวันตกเป็นไปอย่างกระจายคืออาจจะมีจำนวนไม่มากนัก แต่กระจายออกไปในหลาย ๆ ตาราง วิธีที่ดีที่สุดอาจจะเป็น Oracle Migration Services, Inline Migration Utility สามารถใช้ผลที่ได้จากการสแกน เพื่อที่จะแปลงเฉพาะภาษายุโรปตะวันตก และใช้ CSALTER เพื่อแปลง ASCII ไปเป็น AL32UTF8
จากกรณีต่าง ๆ ที่กล่าวมา ลองมาดูว่าข้อมูลที่ผิดปกติมีผลอย่างไรต่อกรณีเหล่านี้ สมมติว่ามีผู้ใช้รายหนึ่งต้องการ Migrate จาก WE8ISO8859P1 (Latin1) ไปเป็น AL32UTF8(UTF-8) เพื่อให้สามารถรองรับภาษาเอเชียได้เพิ่มเติมจากภาษายุโรปตะวันตกที่ใช้อยู่ ผู้ใช้รัน Database Character Set Scanner โดยมี Source เป็น WE8ISO8859P1 และ Target เป็น AL32UTF8 ผลการสแกนแสดงว่ามีตัวอักษรภาษาญี่ปุ่นเก็บอยู่ในฐานข้อมูลแบบผิดปกติ ผู้ใช้จะต้องตัดสินใจว่าจะทำอย่างไร ในขั้นแรกผู้ใช้ทราบว่าข้อมูลดังกล่าวเข้ามาในฐานข้อมูลได้จากวิธีการ Pass-Thru อย่างไรก็ตามข้อมูลเหล่านี้จะต้องถูกเก็บไว้ด้วย แต่จะทำอย่างไร? การเลือก Export/Import เฉพาะบางรายการอาจจะเป็นทางเลือก แต่ก็ยังมีคงปัญหาทางเทคนิคอยู่ดี เนื่องจากฐานข้อมูลเป็น WE8ISO8859P1 ซึ่งจะถูก Export ด้วย Character Set นี้ และการ Import ไปเป็น AL32UTF8 ก็จะให้ผลที่ผิดพลาด เมื่อพบสถานการณ์แบบนี้อาจจะต้องพึ่งบริการ Migration จาก Oracle

4) ผู้ใช้ต้องการให้ระบบรองรับภาษาจีนทั้งแบบ Simplified และ Traditional
กรณีฐานข้อมูลของผู้ใช้รองรับภาษาจีนแบบ Simplified อยู่โดยใช้ชุดตัวอักษร ZHS16GBK ต่อมาเกิดความต้องการที่จะให้ฐานข้อมูลสามารถรองรับ Traditional Chinese ได้ด้วย การให้ฐานข้อมูลสามารถรองรับภาษาเอเชียได้มากกว่าหนึ่งภาษาพร้อม ๆ กันนั้นมักจะต้องใช้ชุดตัวอักษรแบบ Unicode ผู้ใช้รันฐานข้อมูล Oracle10g และใช้ AL32UTF8 เพื่อที่จะให้รองรับ 94,140 ตัวอักขระที่เพิ่มขึ้นมา ซึ่งรวมถึงตัวอักษร Ideographs ของเอเชียนด้วย หลังจากที่สแกนและ "ทำความสะอาด" ตามที่จำเป็นแล้ว ผู้ใช้สามารถใช้โปรแกรม Export/Import เพื่อแปลงข้อมูล ในกรณีนี้ผู้ใช้ต้องขยายบางคอลัมน์ เพื่อให้สามารถรองรับการขยายตัวของชุดตัวอักษร ZHS16GBK (ที่ใช้สองไบท์) มาเป็นสามไบท์ใน AL32UTF8

สรุป
การ Migrate ชุดตัวอักษรเป็นเรื่องที่ Serious การทำโดยปราศจากความเข้าใจ และขั้นตอนที่ถูกต้องสามารถทำให้เกิดความเสียหายได้มาก จงปฏิบัติตามข้อควรระวัง และใช้เทคนิคที่ได้รับการยอมรับแล้วดังที่กล่าวในเอกสารนี้ เพื่อ Migrate อย่างปลอดภัยและมีประสิทธิภาพ ถ้ามีปัญหาในขั้นตอนใด ๆ ให้หาความช่วยเหลือจาก Oracle Support and Migration Services โดยตัว Oracle เองได้ช่วยให้ผู้ใช้หลายรายสามารถ Migrate ชุดตัวอักษรได้อย่างประสบผลสำเร็จมาแล้ว เมื่อการ Migrate เสร็จสมบูรณ์จะช่วยให้สามารถรวมฐานข้อมูลเป็นหนึ่งเดียว และสนับสนุนชุดตัวอักษรใหม่ และตัวอักษรหลากชนิดในแต่ละชาติแต่ละภาษา ทำให้ข้อมูลมีประโยชน์และสามารถให้บริการให้กับผู้ใช้งานได้กว้างขวางขึ้น

อ่านรายละเอียดเพิ่มเติมใน Oracle Database Globalization Support Guide 10g Release 2 (10.2)

จบบทความเรื่อง "วิธีการ Migrate Character Set ที่เหมาะสม" โดยบริบูรณ์ครับ

Monday, July 6, 2009

วิธีการ Migrate Character Set ที่เหมาะสม (ตอนที่ 1)

(แปลจากบางส่วนของ “Character Set Migration Best Practices", An Oracle White Paper January2005)

ทำไมต้อง Migrate?
การ Migrate Database Character Set บ่อยครั้งเกิดจากความต้องการที่จะให้ระบบฐานข้อมูลสามารถใช้ได้กับภาษาใหม่ เพราะการขยายตัวขององค์กรออกไปยังประเทศอื่น ในอดีตระบบเก่า ๆ ขององค์กรจะใช้ Character Set ที่มีข้อจำกัดในการรองรับภาษาอื่น ๆ เช่น ชุดตัวอักษร America 7-bit (ASCII)สามารถใช้ได้กับภาษาอังกฤษเท่านั้น ในขณะที่ในยุโรป ชุดตัวอักษร European 8-bit สามารถจะรองรับภาษาอังกฤษบวกกับตัวอักขระอื่น ๆ ที่ใช้กันในยุโรปอีกด้วย ในขณะที่ในเอเชีย จะต้องใช้ชุดตัวอักษรที่ประกอบด้วยมากกว่า 1 ไบท์ต่อตัวอักษร 1 ตัว การที่จำเป็นต้องมีหลายชุดตัวอักษรเช่นนี้เป็นข้อจำกัดในการที่จะทำให้ระบบฐานข้อมูลสามารถรองรับภาษาต่าง ๆ ได้ทั่วโลก นอกจากนี้การเปลี่ยนแปลงทางวัฒนธรรม (ทางภาษา) เช่นการรวมตัวของสหภาพยุโรป ซึ่งทำให้ความจำเป็นใช้ครื่องหมาย Euro (€) ไปทั่วโลก หรือการที่ระบบของเราต้องติดต่อกับระบบซึ่งมี Character Set อื่นที่ใหญ่กว่า เช่น เว็บไซท์บน WWW หรือแม้แต่ Windows Client ซึ่งใช้ Code Page 1252 ซึ่งมี Character Set ที่มีจำนวนตัวอักษรมากกว่า ASCII ก็ทำให้เกิดความจำเป็นในการ Migrate เช่นกัน หรืออีกเหตุผลหนึ่งก็คือความต้องการที่จะรวมข้อมูลที่เป็นภาษาที่แตกต่างกันลงบนฐานข้อมูลตัวเดียวกัน เช่นข้อมูลชุดหนึ่งต้องใช้ภาษารัสเซียน แต่อีกชุดหนึ่งต้องใช้ภาษาฝรั่งเศสเป็นต้น

การเลือกใช้ Character Set ชุดใหม่
เราอาจจะเลือกใช้ Character Set ตัวที่ตอบสนองความต้องการของเรา แต่ถ้าในอนาคตเกิดมีความต้องการภาษาอื่น ๆ เพิ่มเติมขึ้นมาอีก เราก็อาจจะต้องทำการ Migrate อีก ดังนั้นควรเลือก Character Set ที่สามารถรองรับความต้องการที่จะเกิดขึ้นในอนาคตได้อีกด้วย

Character Set ของระบบฐานข้อมูล จะเป็นอิสระจาก Characte Set ของระบบปฏิบัติการ เพราะ Oracle มีโครงสร้างในการจัดการเรื่องความแตกต่างกันของภาษาของตนเอง ตัวอย่างเช่น บน Windows ที่เป็นภาษาอังกฤษ คุณอาจจะรันฐานข้อมูลที่มี Character Set เป็นภาษาญี่ปุ่น อย่างไรก็ตามเมื่อเครื่อง Client ต้องการติดต่อกับฐานข้อมูล ระบบปฏิบัติการ (OS) ของ Client จะต้องรองรับ Character Set ภาษาญี่ปุ่นด้วยพร้อมทั้งฟอนต์ที่เหมาะสม และวิธีการนำข้อมูลเข้า วิธีการหนึ่งคือใช้ Client ที่มี OS เป็นภาษาญี่ปุ่นในการติดต่อกับระบบฐานข้อมูล ถ้าคุณเลือก Character Set ที่แตกต่างไปจาก OS ของ Client จะทำให้ Oracle จะต้องแปลง Characte Set ของ OS ตัวนั้นไปเป็น Database Character Set

เพื่อประสิทธิภาพที่ดีที่สุด เลือก Character Set ที่หลีกเลี่ยงการแปลงดังกล่าว และเลือกเอา Character Set ที่มีประสิทธิภาพที่สุดสำหรับภาษาที่ต้องการ การใช้ชุดตัวอักษรไบท์เดี่ยว (เช่น WE8ISO8859P1 หรือ TH8TISASCII เป็นต้น) จะให้ประสิทธิภาพที่ดีกว่าชุดตัวอักษรที่เป็นหลายไบท์ และใช้พื้นที่ในการเก็บน้อยกว่า แต่ในขณะเดียวกันชุดตัวอักษรแบบไบท์เดี่ยวก็มีข้อจำกัดในการรองรับภาษาที่หลากหลายในสิ่งแวดล้อมที่มี Client ที่ใช้ Code Page หรือชุดตัวอักษรที่หลากหลายแตกต่างกันในการทำงานกับฐานข้อมูลตัวเดียวกัน

การเลือกชุดตัวอักษรจำเป็นจะต้องพิจารณา OS ของเครื่อง Client และ Application ที่จะติดต่อกับฐานข้อมูล ชุดตัวอักษรของฐานข้อมูลจะต้องมีตัวอักษรครบทุกตัวที่ชุดตัวอักษร Client ใช้ ไม่เช่นนั้นอาจจะมีการสูญเสียข้อมูลได้ เช่น ถ้าคุณต้องการจะใช้ชุดตัวอักษร A (บน Client) กับฐานข้อมูลที่ใช้ชุดตัวอักษร B คุณจะต้องแน่ใจว่าชุดตัวอักษร A เป็น Subset ของชุดตัวอักษร B อย่างไรก็ตามถ้าเราต้องการใช้ตัวอักษรเพียงบางส่วนของชุดตัวอักษร A และบางส่วนนั้นก็เป็น Subset ของชุดตัวอักษร B ก็ไม่จำเป็นที่ทุก ๆ ตัวของชุดตัวอักษร A จะต้องเป็น Subset ของชุดตัวอักษร B

ยกตัวอย่างเช่น บาง Application อาจจะมี Client บางตัวเป็น WE8MSWIN1252 (ชุดตัวอักษรภาษาอังกฤษที่ใช้บน Windows) และบางตัวเป็น WE8ISO8859P1 (ชุดตัวอักษรภาษาอังกฤษที่ใช้ในยุโรป) ถ้าไม่มีการใช้ตัวอักษรเช่น เครื่องหมาย '€' กับ Application นี้การแปลงชุดตัวอักษรก็จะไม่สูญเสียข้อมูล การสูญเสียข้อมูลเกิดจากการที่ Oracle จะแปลงตัวอักษรใด ๆ ซึ่งไม่อยู่ในชุดตัวอักษร B ให้เป็นเครื่องหมายคำถาม หรือแปลงเป็นตัวอักษรที่มีลักษณะคล้ายกันในเชิงของภาษา เช่นตัว a (ตัว a ที่มี umlaut) อาจจะถูกแปลงเป็น 'a' ถ้าคุณต้องทำงานในลักษณะที่มีเครื่อง Client กระจายกันอยู่ในที่ต่าง ๆ (Distribute Environment) พยายามใช้ชุดตัวอักษรที่มีตัวอักษรอยู่ในกลุ่มเดียวกันเพื่อหลีกเลี่ยงการสูญเสียข้อมูล

ถ้า Client ทุกตัวใช้ชุดตัวอักษรเดียวกันทั้งหมด คุณควรจะใช้ชุดตัวอักษร (ของฐานข้อมูล) ที่เป็น Superset ของชุดตัวอักษรที่ใช้กับ Client เหล่านั้น หรือถ้า Client แต่ละตัวไม่ได้ใช้ชุดตัวอักษรเดียวกัน ชุดตัวอักษรของฐานข้อมูลก็ควรจะเป็น Superset ของชุดตัวอักษรของ Client ทุก ๆ ตัวรวมกัน

สรุปคือ การเลือกชุดตัวอักษรควรจะทำดังนี้
1) เลือกชุดตัวอักษรที่สามารถจะรองรับระบบได้ทั้งในปัจจุบัน และในอนาคต
2) ชุดตัวอักษรของฐานข้อมูล ควรจะต้องเป็น Superset หรือเทียบเท่ากับชุดตัวอักษรของระบบปฏิบัติการของ Client ทุกตัวรวมกัน

ทำไมการใช้ UNICODE จึงอาจจะเป็นทางเลือกที่ดีที่สุด?
Unicode เป็นชุดตัวอักษรที่กำหนดรหัสของแต่ละตัวอักษร (Code Point) ไว้เป็นสากล ซึ่งทำให้สามารถเก็บตัวอักษรในทุก ๆ ภาษาในโลกไว้ได้ทั้งหมดในชุดตัวอักษรเดียว โดยไม่มีข้อจำกัดเรื่อง Platform (เช่น Unix, Linux หรือ Windows), โปรแกรม หรือภาษา ซึ่งทำให้เราสามารถที่จะพัฒนา, ติดตั้ง และเก็บภาษาต่าง ๆ ภายในฐานข้อมูลตัวเดียวกัน

ปัจจุบัน Unicode กลายมาเป็นมาตรฐานของซอฟต์แวร์ต่าง ๆ และเป็นชุดตัวอักษรที่มีการใช้กันอย่างมากมายในการแปลงข้อมูลจากระบบสมัยเก่า (Legacy System)

บน Oracle นั้น Unicode ถือเป็นชุดตัวอักษรขนาดใหญ่ที่สุดที่เป็น Superset ของทุก ๆ ชุดตัวอักษรที่ Oracle รองรับ จึงทำให้ Unicode เป็นชุดตัวอักษรที่เหมาะสมมากในการที่จะเป็นชุดตัวอักษรปลายทางที่ถูกแปลงมาจากชุดตัวอักษรอื่น สำหรับการติดตั้งระบบใหม่ๆ Oracle แนะนำให้ใช้ Unicode เสมอ

ในแง่ของประสิทธิภาพ ฐานข้อมูลที่เป็น Unicode บน Oracle10g อาจจะทำให้ประสิทธิภาพลดลงไปได้ถึง 10% เมื่อเทียบกับชุดตัวอักษรแบบไบท์เดี่ยว และอาจจะมากกว่าถ้ามีการใช้ String Function (เช่น Substring, Instring เป็นต้น) จำนวนมาก

Unicode ยังจำเป็นเมื่อมีการใช้ภาษาบางภาษาร่วมกับภาษาอื่น ๆ เช่นภาษาที่เป็นภาษาตัวเขียน (Scripts) เช่น Latin หรือ Cyrillic เมื่อมีการใช้ภาษาเหล่านี้ร่วมกันกับภาษาอื่น ๆ ทำให้มักจะต้องใช้ Unicode เสมอ ผู้สนใจอาจจะหาข้อมูลเพิ่มเติมได้ที่
1) "Oracle Unicode Database Suport" ซึ่งหาได้จาก Home Page
http://www.oracle.com/technology/tech/globalization/index.html

การใช้ Unicode Datatype (แทนที่จะเป็น Unicode Database ดังได้กล่าวมาแล้ว)
อีกทางเลือกหนึ่งของการเก็บข้อมูลในฐานข้อมูลเป็น Unicode คือการใช้ Datatype ที่เป็น NCHAR ซึ่งตัวอักษรที่เป็น Unicode สามารถนำมาเก็บไว้ในคอลัมน์พวกนี้ได้โดยไม่ต้องคำนึงถึงชุดตัวอักษรของฐานข้อมูล (เริ่มมาตั้งแต่ Oracle9i ชนิดข้อมูล NCHAR ถูกใช้เก็บข้อมูลที่เป็น Unicode เท่านั้น) NCHAR รองรับชุดตัวอักษร (Unicode) สองแบบคือ UTF-16 และ UTF-8

ถ้าคุณต้องการให้ระบบของคุณรองรับได้หลายภาษาในบางคอลัมน์เท่านั้น คุณสามารถเพิ่มคอลัมน์ชนิด NCHAR ในตารางเดิมที่ไม่ได้เป็น Unicode หรือในตารางใหม่ภายหลังก็ได้ คุณสามารถ Migrate ข้อมูลจากคอลัมน์ชนิด CHAR ไปเป็น NCHAR โดยใช้คำสั่ง ALTER TABLE MODIFY COLUMN
เช่น
SQL> alter table emp modify (enam NVARCHAR2(10));
การใช้ NCHAR มีประโยชน์ในด้านอื่น ๆ ด้วย กล่าวคือ
1) คุณสามารถใช้ NCHAR ในฐานะที่เป็นข้อมูล Unicode ได้ ในการสร้าง Application ให้กับลูกค้า
2) คุณต้องการให้ระบบฐานข้อมูลของคุณมีสิ่งแวดล้อมเหมือนกับสิ่งแวดล้อมของโปรแกรมเดิมที่เป็น UCS-2 หรือ UTF-16
3) คุณต้องการประสิทธิภาพที่ดีที่สุดที่เป็นไปได้ ถ้าฐานข้อมูลเดิมเป็นแบบไบท์เดี่ยว จงใช้คอลัมน์ NCHAR เพิ่มเติมสำหรับข้อมูล Unicode (แทนที่จะ Migrate ทั้งฐานข้อมูลให้เป็น Unicode)

NCHAR สามารถใช้ร่วมกับชนิดข้อมูลอื่น ๆ ได้ เมื่อมีการใช้หรือมีการ Operation กับชนิดข้อมูลอื่น คุณสามารถทำ Conversion แบบโดยตรง (Explicit) หรือแบบอ้อม (Implicit) ได้ โดยทางอ้อมคุณสามารถเก็บ, นำมาแสดง หรือทำกระบวนการใด ๆ กับ NCHAR ในทำนองเดียวกับชนิดข้อมูล CHAR ได้ และสามารถทำกับ DATE, NUMBER, ROWID, RAW และ CLOB ได้เช่นเดียวกัน โดยปกติแบบอ้อม Oracle จะทำการแปลงให้หากมีการทำ Operation กับข้อมูลที่มีชนิดข้อมูลต่างกัน
ในทางกลับกัน Explicit สามารถควบคุมได้มากกว่า คุณสามารถระบุว่าต้องการแปลงไปเป็นชนิดข้อมูลใด ตัวอย่างฟังก์ชั่นที่ใช้ในการแปลงเช่น
TO_NCHAR(), TO_CHAR(), TO_CLOB(), TO_NCLOB(), TO_NUMBER(), TO_DATE(), UNISTR(), ASCIISTR(), ROWIDTONCHAR(), CHARTOROWID()
อ่านข้อมูลเพิ่มเติมได้ที่:
1) "Migration to Unicode Datatypes for Multilingual Databases and Applications in Oracle9i" ใน Globalization Support โฮมเพจ
http://www.oracle.com/technology/tech/globalization/index.html

สิ่งที่ต้องคำนึงถึงในการ Migrate
1) ช่วงเวลาที่ระบบจะใช้งานไม่ได้อันเนื่องจากการปิดระบบเพื่อ Migrate (Downtime) จะต้องน้อยที่สุด และมีแผนสำรองหากมีปัญหาเกิดขึ้น บางที Downtime อาจจะเป็นเวลาไม่กี่ชั่วโมงไปจนถึงเป็นเวลาหลายวันแล้วแต่ความจำเป็นของระบบ
2) การสูญเสียข้อมูล ข้อมูลที่ได้ภายหลังจากการแปลงควรจะเหมือนกับตัวก่อนแปลง ควรจะคำนวณขนาดของข้อมูลหลังจากการแปลงไว้ก่อนด้วย โดยเฉพาะเมื่อแปลงไปยังชุดตัวอักษรที่เป็นหลายไบท์จะทำให้กินพื้นที่เพิ่มขึ้น
3) ประสิทธิภาพของฐานข้อมูลที่ใช้ชุดตัวอักษรใหม่ การแปลงชุดตัวอักษรแบบไบท์เดี่ยวไปยังไบท์เดี่ยว หรือชุดตัวอักษรแบบหลายไบท์ไปยังหลายไบท์จะไม่มีผลต่อประสิทธิภาพ แต่การแปลงชุดตัวอักษรจากไบท์เดี่ยว ไปยังหลายไบท์จะมีผลต่อประสิทธิภาพ

ข้อปฏิบัติก่อนแปลง
ก่อนจะทำการแปลงจะต้องรู้ขอบเขตของสิ่งที่ต้องทำก่อน จะต้องมีการทำการวิเคราะห์ข้อมูลเพื่อเช็คปัญหาที่อาจจะเกิดขึ้นได้ และประเมินว่าข้อมูลมากน้อยเพียงใดที่ต้องการแปลง

โปรแกรม Scanner ที่ใช้สแกนชุดตัวอักษร
ไม่ว่าจะเป็นการแปลงจากชุดตัวอักษรใดไปยังชุดตัวอักษรใด คุณจะต้องทำการ Pre-Scan ข้อมูลชุดนั้นก่อนเพื่อจะได้แน่ใจว่าการแปลงจะประสบความสำเร็จ Database Character Set Scanner เป็นโปรแกรมยูทิลิตี้ที่มีประสิทธิภาพในการสแกนฟิลด์ที่เป็นตัวอักษร และจะแสดงผลหรือแสดงประเด็นที่อาจจะเกิดขึ้นในการแปลง โดยมันจะจำลองการแปลงชุดตัวอักษร และแสดงผลเป็นรายงานสรุปผลการสแกน สิ่งที่ต้องทำในการแปลง และแนะนำชุดตัวอักษรปลายทางที่เหมาะสมที่สุด ผลของการแปลงอาจจะแบ่งได้เป็น 4 แบบคือ

Changeless (ไม่มีการเปลี่ยนแปลง)
ข้อมูลที่แปลง (ในเชิงไบนารี่) ไม่ได้มีการเปลี่ยนระหว่างตัวอักษรตัวต้นกับตัวปลายทาง เช่นตัว 'A' เมื่อเป็นไบนารี่คือ hex41 ที่ตัวต้นทาง เมื่อแปลงไปเป็นปลายทางก็ยังคงเป็น hex41 ดังนั้น 'A' ไม่มีการเปลี่ยนแปลงจากตัวต้นไปยังตัวปลายทาง ข้อมูลทั้งหมดจะต้องเป็นแบบ Changeless จึงจะสามารถใช้ สคริปต์ CSALTER (ใน Oracle Database10g) ได้อย่างปลอดภัย

Convertible (แปลงได้)
ข้อมูลตัวต้นทางสามารถถูกแปลงไปยังตัวปลายทางได้ เช่นถ้าต้นทาง 'A' เป็น hex41 แต่ตัวปลายทาง 'A' เป็น hex90 ข้อมูลทั้งหมดจะต้องอยู่ในแบบ Convertible หรือไม่ก็ Changeless โดยไม่มีการตัดแบ่งข้อมูลใด ๆ (Truncate) ก่อนที่จะใช้ Export-Import ยูทิลิตี้

Truncation (ข้อมูลถูกตัดแบ่งออก)
เมื่อมีการแปลง และข้อมูลปลายทางไม่สามารถรองรับข้อมูลจากต้นทางได้ เช่นเมื่อทำการแปลงจากชุดตัวอักษรแบบไบท์เดี่ยว Western European ไปเป็น Unicode, ตัวอักษรที่มี Accent จะถูกตัดแบ่งจาก 1 ไบท์ไปเป็น 2 ไบท์ ซึ่งทำให้ต้องมีการขยายคอลัมน์

Data Loss
เกิดขึ้นเมื่อชุดตัวอักษรปลายทางไม่มีตัวอักษรตัวที่อยู่ในชุดตัวอักษรต้นทางเช่นตัวยูโร (€) เป็นตัว hex80 ใน WE8MSWIN1252 แต่ไม่มีในชุดตัวอักษร WE8ISO8859P1 (Latin1) ซึ่งทำให้เกิดการสูญเสียข้อมูล

Data Truncation
Database Character Set Scanner จะทดสอบว่าข้อมูลหลังจากการแปลงจะสามารถเก็บลงในขนาดของคอลัมน์ปัจจุบันได้หรือไม่ ตัว Scanner จะชี้ให้เห็นว่าคอลัมน์ใดต้องทำการเปลี่ยนขนาด และแสดงข้อมูล 30 ไบท์แรกในคอลัมน์ ถ้าไม่ทำการแก้ไขข้อมูล หรือไม่เปลี่ยนขนาดของคอลัมน์จะทำให้เกิดการสูญเสียข้อมูลทั้งแถวระหว่างกระบวนการ Export-Import ในสถานการณ์ที่ปลายทางเป็นแบบหลายไบท์อาจทำให้เกิดสิ่งที่เรียกว่า Data Truncation เนื่องจากโดยปกติคอลัมน์ถูกกำหนดความกว้างเป็น Byte คอลัมน์ที่กำหนดเป็น VARCHAR(1) จะเก็บ 1 ตัวอักษรต่อ 1 Byte แต่จะไม่พอสำหรับชุดตัวอักษรแบบหลายไบท์ ตัวอย่างเช่น ตัวอักษรที่เป็น Unicode UTF-8 หนึ่งตัวอาจจะกินพื้นที่ 1-4 ไบท์เลยทีเดียว

แนวโน้มที่จะเกิดการ Truncate เมื่อแปลงไปเป็น UTF-8 ขึ้นอยู่กับภาษา เช่นถ้าฐานข้อมูลจะเก็บแต่ภาษาอังกฤษ โอกาสที่จะเกิดการ Truncate ก็มีน้อย และมีตัวอักษรสัญลักษณ์ไม่กี่ตัวที่จะถูก"ขยาย"จาก 1 ไบท์ไปเป็น UTF-8 ตัวอักษรยุโรปมักจะมีตัวอักษรท้องถิ่นที่จะต้องขยายไปเป็น 2 ไบท์เมื่อแปลงไปเป็น UTF-8 ตัวอักษรเอเชียเป็นตัวอักษรที่มักจะถูก Truncate มากที่สุดจากชุดตัวอักษรแบบดั้งเดิม (2 ไบท์) มาเป็น UTF-8 (3 ไบท์)

โอกาสที่จะเกิดมากขึ้นจากชุดตัวอักษรตะวันตกไปตะวันออกดังนี้
- English [£ ©] (1%)
- European [ä ß] (10%)
- Asian [佺俓來] (50%)

Data Loss
Export-Import ยูทิลิตี้ของ Oracle สามารถแปลงชุดตัวอักษรจากต้นฉบับมายังอีกชุดตัวอักษรหนึ่งได้ อย่างไรก็ตามผลที่ได้บางครั้งอาจจะมีการสูญเสียหรือเสียหายของข้อมูลได้ คุณจะต้องแน่ใจว่าข้อมูลต้นฉบับ"สะอาด" และตัวอักษรแต่ละตัวสามารถที่จะหาตัวแม็บได้ในอีกชุดตัวอักษร เช่นถ้าคุณกำลังแปลงจากชุดตัวอักษร A ไป B ชุดตัวอักษร B ควรจะเป็น Superset ของชุด A ชุดตัวอักษร B จะเป็น Superset ของชุดตัวอักษร A ก็ต่อเมื่อทุก ๆ ตัวของชุดตัวอักษร A มีอยู่ในชุดตัวอักษร B ตัวอักษรที่ไม่อยู่ในชุดตัวอักษร B จะถูกแปลงไปเป็นตัวอักษรแทน ซึ่งมักจะได้แก่ ? หรือเครื่องหมายคำถามแต่กลับหัว หรือตัวอักษรที่เกี่ยวเนื่องกับตัวอักษรที่ไม่มี เช่น ä สามารถถูกแทนด้วย 'a' ตัวอักษรแทนเหล่านี้ถูกกำหนดขึ้นที่ชุดตัวอักษรปลายทาง การสูญเสียข้อมูลอาจจะแตกได้เป็น 3 ประเภทคือ
1) สูญเสียข้อมูลตั้งแต่ต้นทางก่อนการ Migrate เช่นมีตัวอักษรที่ผิดปกติแต่ต้นทาง อาจจะมีการ Process ตัวอักษรผิดปกติ หรือข้อมูลมาจากชุดตัวอักษรอื่น (เช่นบน Client ที่มีชุดตัวอักษรคนละแบบกัน)
2) สูญเสียข้อมูลที่ปลายทาง เกิดขึ้นเมื่อไม่มีรหัสตัวอักษรที่เหมาะสมสำหรับชุดตัวอักษรปลายทางเพื่อแม็ปกับต้นทาง
3) สูญเสียข้อมูลจากการไป-กลับของการแปลง เกิดขึ้นเมื่อมีการแปลงจากชุดตัวอักษรต้นทางไปยังปลายทางได้อย่างถูกต้อง แต่เมื่อต้องการแปลงกลับจากตัวปลายทางมาเป็นตัวต้นทางอีกครั้งกลับได้ตัวอักษรคนละตัว
ดูเพิ่มเติมที่เรื่อง Why do Invalid Data Exception Occur? ข้างล่าง

สิ่งที่ต้องทำหลังการสแกน ก่อนการแปลง
เมื่อได้รัน Database Character Set Scanner แล้ว เราจะพบกับประเด็นต่าง ๆ ที่ต้องจัดการดังนี้

การจัดการกับ Exceptions
ขั้นแรกจะต้องพิจารณาข้อมูลที่ตกอยู่ใน Exceptions ประเภทใดประเภทหนึ่ง และตัดสินใจว่าจะจัดการกับมันอย่างไร

Truncation
เพื่อจัดการกับ Truncation เรามักจะขยายขนาดของคอลัมน์ ถ้าเราขยายขนาดของคอลัมน์เราจะต้องตัดสินใจว่าจะขยายขนาดให้ใหญ่พอที่จะรองรับขนาดของข้อมูลที่ใหญ่ที่สุดในคอลัมน์นั้นหลังจากการแปลง หรือขยายให้รองรับกับขนาดของคอลัมน์ตามกฎการแปลงจากชุดตัวอักษรหนึ่งไปยังอีกชุด เช่นถ้าเราต้องการแปลงจากคอลัมน์ที่เดิมเป็น CHAR(1) และใช้ชุดตัวอักษร 8859-1 ไปเป็น UTF-8 เราอาจจะขยายให้เป็น CHAR(4) (ขนาดของ 1 ตัวอักษรบน UTF-8 ที่ใหญ่ที่สุดคือ 4 ไบท์) เพื่อให้แน่ใจว่ามันจะสามารถรองรับตัวอักษรภาษาอะไรก็ได้รวมทั้งตัวอักขระพิเศษในอนาคต หรือถ้าเราแน่ใจว่าข้อมูลที่เราจะใช้ไม่มีทางเกิน 3 ไบท์แน่ ๆ และเรามีข้อจำกัดเรื่องพื้นที่ฮาร์ดดิสก์ เราอาจจะเลือกที่จะขยายให้เป็นแค่ CHAR(3) ก็ได้

เปลี่ยนวิธีการกำหนดขนาด(คอลัมน์)
เริ่มตั้งแต่ Oracle9i เราสามารถที่จะเปลี่ยนวิธีการกำหนดขนาดคอลัมน์ได้ โดยเฉพาะการเปลี่ยนวิธีการกำหนดขนาดเพื่อขยายขนาดคอลัมน์ให้รองรับการแปลงชุดข้อมูลไปเป็น Unicode UTF-8 วิธีการกำหนดขนาดคอลัมน์มีประโยชน์ในแง่การจัดการกับข้อความในคอลัมน์ และกำหนดขนาดพื้นที่ที่ต้องการของคอลัมน์สำหรับข้อความที่ใช้ตัวอักษรแบบหลายไบท์ และมีขนาดของตัวอักษรต่าง ๆ ไม่เท่ากัน (UTF-8 ใช้ไบท์เดียวสำหรับภาษาอังกฤษ, ใช้ 3 ไบท์สำหรับภาษาเอเชียส่วนใหญ่เป็นต้น) ในชุดตัวอักษรที่เป็นไบท์เดี่ยว จำนวนตัวอักษรกับจำนวนไบท์มักจะเท่ากันเสมอ ในขณะที่ในชุดตัวอักษรแบบหลายไบท์ หนึ่งตัวอักษร (หรือเรียกอีกอย่างว่า Code Unit) อาจจะประกอบด้วยหนึ่งไบท์หรือมากกว่า การคำนวณขนาดคอลัมน์เป็นไบท์เราเรียกว่า "Byte Sementics" ขณะที่การคำนวณขนาดคอลัมน์เป็นจำนวนตัวอักษรเราเรียกว่า "Character Semantics"
es ตัวอย่างเช่น ถ้าคุณแปลงฐานข้อมูลจากชุดตัวอักษร 8 บิท Western European Character Set ไปเป็น Unicode (AL32UTF8) สมมติว่าคุณมีคอลัมน์ที่เป็น VARCHAR2 ที่เก็บตัวอักษรที่เป็นสัญลักษณ์ท้องถิ่น ผลการสแกนจะระบุว่าคอลัมน์ (VARCHAR2) เหล่านี้จะต้องถูกเปลี่ยนขนาดเพื่อจะรองรับการขยายตัวไปเป็นฐานข้อมูลที่ใช้ชุดตัวอักษรแบบหลายไบท์ที่ใช้วิธีการกำหนดขนาดคอลัมน์แบบ Byte Semantics

และถ้าคุณต้องการจะเพิ่มภาษาเอเชียเข้าไปในฐานข้อมูล การใช้วิธีการกำหนดขนาดคอลัมน์แบบ Character Semantics สำหรับฐานข้อมูลหลังการแปลงอาจจะเป็นทางเลือกที่ดีที่สุด โดยเฉพาะเมื่อมีคอลัมน์แบบนี้จำนวนมากและชนิดของข้อมูลที่จะเก็บจะไม่เปลี่ยนแปลง ไม่อย่างนั้นถ้าเราใช้ Byte Semantics เราจะต้องขยายทุกๆ คอลัมน์เอง (เช่น ถ้าต้นทางเป็น VARCHAR2(4) ถ้าเราใช้แบบ Character Semantics เรายังคงสามารถใช้ VARCHAR2(4) ได้โดยไม่ต้องเปลี่ยนแปลง แต่ถ้าเราใช้ Byte Semantics เราอาจจะต้องกำหนดขนาดใหม่ตามขนาดที่อาจจะเพิ่มขึ้นเช่น อาจจะเป็น VARCHAR2(12) ถ้าเราคิดว่าแต่ละตัวอักษรจะขยายเป็น 3 ไบท์เป็นต้น

พารามิเตอร์เริ่มต้น (Initialization Parameter) ชื่อ NLS_LENGTH_SEMANTICS เป็นตัวกำหนดว่าคอลัมน์ที่เป็นตัวอักษรจะใช้ Byte หรือ Character Semantics ตอนที่มันถูกสร้างขึ้น ปกติ Byte Semantics เป็นดีฟอลต์สำหรับชุดตัวอักษรของฐานข้อมูล ส่วน Character Semantics เป็นดีฟอลต์สำหรับชนิดตัวอักษรแบบ NCHAR (ซึ่ง NCHAR จะใช้ได้กับการกำหนดแบบ Character Semantics เท่านั้น)

ทำไมจึงเกิด Invalid Data Exceptions?
ถ้ามีการสูญเสียข้อมูลเราจำเป็นต้องวิเคราะห์เหตุผลของการสูญเสีย การแก้ไขจัดการ Exception ถือเป็นการทำ Data Cleansing ข้อมูลที่ผิดปกติ (Invalid) อาจจะเกิดขึ้นได้เสมอเนื่องมาจากการตั้งพารามิเตอร์ NLS_LANG ที่ Client ไม่เหมาะสม และการ Conversion ระหว่าง Client กับ Server ก็ไม่ถูกต้อง ที่ถูกต้องแล้ว NLS_LANG ควรจะสอดคล้องหรือเป็นตัวเดียวกับ Code Page (ชุดตัวอักษร) ของ Operating System ยกตัวอย่างเช่น บน OS ที่เป็น Windows ภาษาอังกฤษ จะใช้ Code Page เป็น 1252 ซึ่งจะสอดคล้องกับชุดตัวอักษรของ Oracle ชื่อ WE8MSWIN1252 ถ้า NLS_LANG ถูกกำหนดไว้อย่างถูกต้อง ฐานข้อมูลจะสามารถแปลงข้อมูลที่เข้ามาจาก Client ได้โดยอัตโนมัติ แต่ถ้ากำหนดไม่ถูกต้องอาจจะทำให้เกิดการแปลงผิดได้สองกรณีคือ

1) เซ็ท NLS_LANG ต่างจาก OS Code Page และต่างจากชุดตัวอักษรของฐานข้อมูลด้วย
- ระหว่างการแปลงอาจจะมีตัวอักษรบน OS บางตัวที่ไม่สามารถแม็ปกับชุดตัวอักษรของ NLS_LANG ได้เลย ซึ่งก็จะทำให้ไม่สามารถแม็ปกับชุดตัวอักษรของฐานข้อมูลได้เช่นกัน กรณีนี้ฐานข้อมูลจะใช้ตัวอักษรแทน (เช่น ? เป็นต้น)
- ระหว่างการแปลงอาจจะมีตัวอักษรบน OS บางตัวแม้ว่าจะแม็ปกับรหัสชุดตัวอักษรของ NLS_LANG ได้ แต่เป็นคนละตัวอักษรกัน (รหัสตรงกัน แต่กลายเป็นคนละตัวอักษร) ตัวอย่างเช่น ตัว 'A' มีรหัสเป็น hex61 ใน Code Page บน Client (OS) แต่ 061 กลับเป็นเครื่องหมายเปอร์เซนต์ (%) ในชุดตัวอักษรของ NLS_LANG ในกรณีนี้การแปลงผิดจะเกิดขึ้น และข้อมูลจะถูกแม็ปไปเป็น '%' แทนที่จะเป็นตัวอักษร 'A' ดูภาพที่ 1



2) เซ็ท NLS_LANG ต่างจาก OS Code Page แต่ตรงกับชุดตัวอักษรของฐานข้อมูล ซึ่งเป็นกรณีที่เกิดขึ้นได้บ่อย ๆ เนื่องจากผู้ใช้งานคิดว่าจะต้องเซ็ท NLS_LANG ให้ตรงกับชุดตัวอักษรของฐานข้อมูลเท่านั้น โดยไม่คำนึงถึง OS Code Page เมื่อเป็นเช่นนี้ Oracle จะไม่ทำการแปลงใด ๆ ทั้งสิ้น โดยเหตุผลทางประสิทธิภาพ ซึ่งเราเรียกว่า Pass-Thru ผลที่ได้คือข้อมูล (ที่ไม่ได้ถูกแปลงเลย) จะถูกเก็บด้วยรหัสตัวอักษรบนฐานข้อมูลที่ไม่ตรงกับบน OS เลย ซึ่งก็คือข้อมูลขยะดี ๆ นี่เอง ที่น่ากลัวก็คือ ถ้ามองดูจาก OS บน Client ข้อมูลอาจจะดู OK เนื่องจากข้อมูลที่ผ่านจากฐานข้อมูลมา Client จะถูกแสดงออกมาโดยไม่ถูกแปลงเช่นกัน การ Pass-Thur ในลักษณะนี้เหมือนกับการครอบข้อมูลที่ถูกเก็บอย่างไม่ถูกต้องในฐานข้อมูล



(ยังไม่จบนะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)