12c กับคอลัมน์ INVISIBLE และ set colinvisible

ฐานข้อมูลออราเคิล 12c นั้นเราสามารถเซต COLUMN ที่เราไม่ต้องการนั้น Invisible (ไม่ปรากฏหรือโชว์เมื่อเราดึงข้อมูล) แต่เรายังสามารถ INSERT/UPDATE ข้อมูลใน COLUMN (Invisible) นั้นได้ ...แถมยังเอาไปใช้สร้าง INDEX ได้อีกด้วย

SET COLINVISIBLE
ใช้ในการโชว์ข้อมูล column ที่ invisible

ตัวอย่าง

SQL> show colinvisible
colinvisible OFF
SQL>
SQL>  create table mytable(col1 number, col2 varchar2(20) invisible);

Table created.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> insert into mytable (col1,col2) values (1,'Surachart Opun');

1 row created.

SQL> select * from mytable;

      COL1
----------
         1

SQL>
SQL> show colinvisible
colinvisible OFF
SQL>
SQL> set colinvisible on
SQL> show colinvisible
colinvisible ON
SQL>
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                                   VARCHAR2(20)

SQL>
SQL>  select * from mytable;
      COL1
----------
         1

SQL> select COL1, COL2 from mytable;

      COL1 COL2
---------- --------------------
         1 Surachart Opun

SQL>
SQL> set colinvisible off
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL>
SQL>
SQL> select COL1, COL2 from mytable;

      COL1 COL2
---------- --------------------
         1 Surachart Opun

จะพบว่าเราสามารถ INSERT/UPDATE หรือ SELECT ข้อมูลจาก column ที่ invisible ได้ด้วยการระบุชื่อ column

อย่างไรก็ดี COLUMN ที่เราต้องการเซตเป็น INVISIBLE ไม่ควรมี default (NOT NULL)

SQL> create table mytable(col1 number, col2 varchar2(20) not null);

Table created.

SQL> alter table mytable modify (col2 invisible);

Table altered.

SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> insert into mytable values (1);
insert into mytable values (1)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("DEMO"."mytable"."COL2")


SQL>
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL>
SQL> show colinvisible
colinvisible OFF
SQL> set colinvisible on
SQL>
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                          NOT NULL VARCHAR2(20)

SQL>
SQL> insert into mytable values (1,'Surachart Opun');
insert into mytable values (1,'Surachart Opun')
            *
ERROR at line 1:
ORA-00913: too many values


SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 COL2 (INVISIBLE)                          NOT NULL VARCHAR2(20)

SQL>  insert into mytable (col1,col2) values (1,'Surachart Opun');

1 row created.

จะเห็นว่าเกิด Error ดังตัวอย่าง... เราจึงควรเช็คให้ดี หรือแจ้งให้ Developer ทราบ ^______________^

เรียน row_limiting_clause บนฐานข้อมูลออราเคิล 12c

ผมได้มีโอกาสในการอ่านเกี่ยวกับ row_limiting_clause บนฐานข้อมูลออราเคิล 12c ผมคิดว่าเป็นเรื่องที่น่าสนใจ ผมเลยนำมาแชร์ต่อเพื่อนๆ ดังที่ทราบกันว่าบนฐานข้อมูล 12c เราสามารถจำกัดจำนวนแถวที่ต้องการ จากการดึงข้อมูล หรือจำกัดเป็นเปอร์เซนต์ พร้อมกันนี้เรายังสามารถข้ามแถวของข้อมูลที่ไม่ต้องการ ก่อนที่จะทำการจำกัดแถวที่ต้องการ เพื่อแสดงข้อมูลออกมา


ตัวอย่าง

SQL> show user;
USER is "SCOTT"
SQL> select empno from emp
  2  ;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

เริ่มต้นเพื่อดูข้อมูลก่อน
SQL> select empno from emp order by empno;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

ดึงข้อมูล 5 แถวแรก
SQL>  select empno from emp order by empno fetch first 5 rows only;

     EMPNO
----------
      7369
      7499
      7521
      7566
      7654

อยากได้ข้อมูลแถวที่ 6 - 10
SQL>  select empno from emp order by empno offset 5 rows fetch first 5 rows only;

     EMPNO
----------
      7698
      7782
      7788
      7839
      7844

ผมจำได้ว่าผมเคยเขียน "query เอาข้อมูล max อันดับที่ 3 เท่านั้น"

SQL> create table test as select * from all_objects;

Table created.

SQL> column OBJECT_NAME format a30
SQL>
SQL>
SQL>  select * from (select * from (select object_name , object_id from test order by object_id desc) where rownum <=3 order by object_id) where rownum <=1;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
BONUS                               91952

ว้าวกว่าจะได้! ดังนั้นขอใช้ 12c row_limiting_clause ^______________^

SQL> select object_name , object_id from test order by object_id desc offset 2  rows fetch first 1 rows only;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
BONUS                               91952

จะเห็นว่าสะดวกขึ้นมากเลย...

เซต VARCHAR2 32767 byte (limit) บนฐานข้อมูลออราเคิล12c

ดังที่เราทราบกันดีว่าบนฐานข้อมูลออราเคิลนั้นเราสามารถเซต varchar2 ได้ 4000 ไบต์ แม้แต่บนฐานข้อมูลออราเคิล 12c อย่างไรก็ดีในบางครั้ง เราอาจต้องการให้ varchar2 มากกว่า 4000 ไบต์
ฐานข้อมูลออราเคิล 12c เราสามารถควบคุมขนาดของ VARCHAR2, NVARCHAR2, และ RAW ด้วยพารามิเตอร์ MAX_STRING (เปลี่ยนค่าในพารามิเตอร์นี้ ใน Upgrade โหมด นะครับ)

Syntax MAX_STRING_SIZE = { STANDARD | EXTENDED }

การเปลี่ยนแปลงค่า MAX_STRING_SIZE สามารถเปลี่ยนจาก STANDARD => EXTENDED แต่ไม่สามารถเปลี่ยนจาก EXTENDED => STANDARD

ดังนั้นควรพิจารณาให้ดีเสียก่อน!
การเปลี่ยนค่า MAX_STRING_SIZE ทำได้โดยการ startup ฐานข้อมูลที่ "UPGRADE" โหมด จากนั้น "ALTER SYSTEM" ตามด้วยรัน "utl32k.sql"... เมื่อเรียบร้อยก็ restart ฐานข้อมูลเข้าสู่โหมดปกติ

ตัวอย่าง

SQL> show user;
USER is "DEMO"
SQL>
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>
SQL> create table mytest (a varchar2(32767));
create table mytest (a varchar2(32767))
                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2287864 bytes
Variable Size             452986632 bytes
Database Buffers          788529152 bytes
Redo Buffers                8859648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter MAX_STRING_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL> alter system set max_string_size=EXTENDED;

System altered.

SQL>
SQL>

SQL> @?/rdbms/admin/utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.

Session altered.
.
.
.
SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
noncdb           OPEN MIGRATE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2287864 bytes
Variable Size             452986632 bytes
Database Buffers          788529152 bytes
Redo Buffers                8859648 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select INSTANCE_NAME,STATUS from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
noncdb           OPEN

SQL>
SQL> conn demo
Enter password:
Connected.
SQL>
SQL>
SQL> show user
USER is "DEMO"
SQL>
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL>
SQL>
SQL> create table mytest (a varchar2(32767));

Table created.

SQL> desc mytest
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  VARCHAR2(32767)

หวังว่าตัวอย่างสามารถทำให้เพื่อนๆ เข้าใจได้ง่ายขึ้นนะครับ.

อ่านเพิ่มเติมได้ที่ Oracle Documents (Oracle Database Reference 12C) นะครับ