Oracle 23c부터 테이블과 뷰의 최대 칼럼 수가 1000개에서 4096개로 늘어났습니다.
The maximum number of columns allowed in a database table or view has been increased to 4096. This feature allows you to build applications that can store attributes in a single table with more than the previous 1000-column limit. Some applications, such as Machine Learning and streaming IoT application workloads, may require the use of de-normalized tables with more than 1000 columns. You now have the ability to store a larger number of attributes in a single row which for some applications may simplify application design and implementation.
이 기능은 MAX_COLUMNS 파라미터로 설정할 수 있습니다.
-- 1-1
SELECT value, default_value, issys_modifiable, description
FROM v$parameter
WHERE name = 'max_columns';
NAME VALUE DEFAULT_VALUE ISSYS_MODIFIABLE DESCRIPTION
----------- -------- ------------- ---------------- --------------------------------------------------
max_columns STANDARD STANDARD FALSE maximum number of columns allowed in table or view
1개의 행이 선택되었습니다.
-- 1-2
SELECT name, ordinal, value, isdefault
FROM v$parameter_valid_values
WHERE name = 'max_columns';
NAME ORDINAL VALUE ISDEFAULT
----------- ------- -------- ---------
max_columns 1 STANDARD TRUE
max_columns 2 EXTENDED FALSE
2 행이 선택되었습니다.
아래와 같이 칼럼 수가 1000개인 t1 테이블을 생성하겠습니다. 참고로 칼럼 수가 1000개보다 많은 테이블을 생성하면 "ORA-01792: 테이블, 뷰에 지정 가능한 열의 최대수는 1000입니다" 에러가 발생합니다.
-- 2
DROP TABLE t1 PURGE;
DECLARE
v_sql_text CLOB;
BEGIN
v_sql_text := 'CREATE TABLE t1 (c1';
FOR i IN 2 .. 1000 LOOP v_sql_text := v_sql_text || ', c' || i; END LOOP;
v_sql_text := v_sql_text || ') AS ';
v_sql_text := v_sql_text || 'SELECT 1';
FOR i IN 2 .. 1000 LOOP v_sql_text := v_sql_text || ', 1'; END LOOP;
v_sql_text := v_sql_text || ' FROM DUAL';
EXECUTE IMMEDIATE v_sql_text;
END;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
테스트를 위해 max_columns 파라미터를 EXTENDED로 설정하고 DB를 재기동하겠습니다.
-- 3: SYS
ALTER SYSTEM SET max_columns = EXTENDED SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
아래와 같이 칼럼이 4096개인 t2 테이블을 생성하겠습니다.
-- 4
DROP TABLE t2 PURGE;
DECLARE
v_sql_text CLOB;
BEGIN
v_sql_text := 'CREATE TABLE t2 (c1';
FOR i IN 2 .. 4096 LOOP v_sql_text := v_sql_text || ', c' || i; END LOOP;
v_sql_text := v_sql_text || ') AS ';
v_sql_text := v_sql_text || 'SELECT 1';
FOR i IN 2 .. 4096 LOOP v_sql_text := v_sql_text || ', 1'; END LOOP;
v_sql_text := v_sql_text || ' FROM DUAL';
EXECUTE IMMEDIATE v_sql_text;
END;
/
PL/SQL 처리가 정상적으로 완료되었습니다.
아래는 DBMS_SPACE.SPACE_USAGE 프로시저 수행 결과입니다. t1 테이블은 1 블록, t2 테이블은 3 블록에 데이터를 저장합니다.
-- 5
+--------------------------+-------+-------+
|COLUMN_NAME | T1| T2|
+--------------------------+-------+-------+
|total_blocks | 8.00 | 8.00 |
|total_bytes | 64.00k| 64.00k|
|unused_blocks | 4.00 | 2.00 |
|unused_bytes | 32.00k| 16.00k|
|last_used_extent_file_id | 15| 15|
|last_used_extent_block_id | 152| 168|
|last_used_block | 4.00 | 6.00 |
|full_blocks | 1.00 | 3.00 | -- !
|full_bytes | 8.00k| 24.00k|
+--------------------------+-------+-------+
상세 분석을 위해 아래와 같이 블록 덤프를 생성하겠습니다.
-- 6-1
SELECT DISTINCT
b.file#, block#
FROM user_objects a
, v$bh b
WHERE a.object_name = 'T1'
AND b.objd = a.data_object_id
AND b.class# = 1;
FILE# BLOCK#
----- ------
15 155
1개의 행이 선택되었습니다.
-- 6-2
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 155;
-- 6-3
SELECT DISTINCT
b.file#, block#
FROM user_objects a
, v$bh b
WHERE a.object_name = 'T2'
AND b.objd = a.data_object_id
AND b.class# = 1;
FILE# BLOCK#
----- ------
15 171
15 172
15 173
3 행이 선택되었습니다.
-- 6-4
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 171;
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 172;
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 173;
아래는 블록 덤프 결과입니다. t1 테이블은 한 블록에서 4개의 로우가 intra-block row chaining됩니다. t2 테이블은 두 블록에서 각각 8개의 로우가 intra-block row chaining되고, 세 블록이 inter-block row chaining됩니다.
-- 8-1: T1, 15, 155 -> 1000
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1c7a -- 255
0x14:pri[1] offs=0x1974 -- 255
0x16:pri[2] offs=0x166e -- 255
0x18:pri[3] offs=0x13aa -- 235
-- 8-2: T2, 15, 171 -> 2040 = 255 * 8
0xe:pti[0] nrow=8 offs=0
0x12:pri[0] offs=0x22
0x14:pri[1] offs=0x328
0x16:pri[2] offs=0x62e
0x18:pri[3] offs=0x934
0x1a:pri[4] offs=0xc3a
0x1c:pri[5] offs=0xf40
0x1e:pri[6] offs=0x1246
0x20:pri[7] offs=0x154c
-- 8-3: T2, 15, 172 -> 2040 = 255 * 8
0xe:pti[0] nrow=8 offs=0
0x12:pri[0] offs=0x22
0x14:pri[1] offs=0x328
0x16:pri[2] offs=0x62e
0x18:pri[3] offs=0x934
0x1a:pri[4] offs=0xc3a
0x1c:pri[5] offs=0xf40
0x1e:pri[6] offs=0x1246
0x20:pri[7] offs=0x154c
-- 8-4: T2, 15, 173 -> 16
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f4d
아래는 t1, t2 테이블을 조회하는 쿼리의 실행 계획입니다. row chaining에 의해 추가 블록 I/O가 발생하는 것을 확인할 수 있습니다.
-- 9-1
SELECT * FROM t1;
--------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | Reads |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 6 | 2 | -- 6 - 2 = 4, CEIL (1000 / 255) = 4
--------------------------------------------------------------
Rows (1st) Row Source Operation
---------- ---------------------------------------------------
1 TABLE ACCESS FULL T1 (cr=6 pr=2 pw=0 time=317 us starts=1 cost=2 size=3000 card=1)
WAIT #140239421487704: nam='db file sequential read' ela=11 file#=15 block#=154 blocks=1 obj#=130873 tim=395499883429
WAIT #140239421487704: nam='db file sequential read' ela=6 file#=15 block#=155 blocks=1 obj#=130873 tim=395499883480
-- 9-2
SELECT * FROM t2;
--------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers | Reads |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 4 |
| 1 | TABLE ACCESS FULL| T2 | 1 | 21 | 4 | -- 21 - 4 = 17, CEIL (4096 / 255) = 17
--------------------------------------------------------------
Rows (1st) Row Source Operation
---------- ---------------------------------------------------
1 TABLE ACCESS FULL T2 (cr=21 pr=4 pw=0 time=660 us starts=1 cost=3 size=12288 card=1)
WAIT #140673112127992: nam='db file sequential read' ela=9 file#=15 block#=170 blocks=1 obj#=130877 tim=395504377853
WAIT #140673112127992: nam='db file scattered read' ela=13 file#=15 block#=171 blocks=3 obj#=130877 tim=395504377956
참고로 inter-block row chaining에 의한 블록 I/O는 table fetch continued row 통계 값을 증가시킵니다.
-- 10
+--------------------------+----------+----------+------+
|NAME | T1| T2|CLASS |
+--------------------------+----------+----------+------+
|session logical reads | 6.00 | 21.00 |User |
|table fetch continued row | 0.00 | 2.00 |SQL |
+--------------------------+----------+----------+------+
마지막으로 max_columns 파라미터를 STANDARD로 설정하려면 칼럼이 1000개보다 많은 테이블이 존재하지 않아야 합니다.
-- 11
ALTER SYSTEM SET max_columns = STANDARD SCOPE = SPFILE;
ORA-60471: max_columns can not be set to STANDARD as there are one or more objects with more than 1000 columns
DROP TABLE tuna.t2 PURGE;
ALTER SYSTEM SET max_columns = STANDARD SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
아래 링크에서 블록 덤파 파일을 다운로드할 수 있습니다.
block_dump_t1_15_155.trc
0.10MB
block_dump_t2_15_171.trc
0.14MB
block_dump_t2_15_172.trc
0.14MB
block_dump_t2_15_173.trc
0.01MB