최대 칼럼 수 증가

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

저작자표시 비영리 변경금지

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

최대 칼럼 수 증가

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	-- 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 행이 선택되었습니다.

	-- 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 처리가 정상적으로 완료되었습니다.

	-- 3: SYS
	ALTER SYSTEM SET max_columns = EXTENDED SCOPE = SPFILE;
	SHUTDOWN IMMEDIATE;
	STARTUP;

	-- 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 처리가 정상적으로 완료되었습니다.

	-- 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;

	-- 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

	-- 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

	-- 10
	+--------------------------+----------+----------+------+
	\|NAME \| T1\| T2\|CLASS \|
	+--------------------------+----------+----------+------+
	\|session logical reads \| 6.00 \| 21.00 \|User \|
	\|table fetch continued row \| 0.00 \| 2.00 \|SQL \|
	+--------------------------+----------+----------+------+

	-- 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;