OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 오라클 19c 소문자 컬럼 테스트
테스트 중 신기한 점을 발견함
create table 명령을 이용해 테이블 생성시
|
1
|
SQL> create table lowertest as select object_name "col1" from dba_objects;
|
이렇게 ""을 이용해서 테이블을 만들면 컬럼명이 소문자로 만들어짐
일반적으론 아래와 같이 생성할 시 lowertest 테이블의 col1 컬럼은 자동으로 대문자로 만들어짐
|
1
|
SQL> create table lowertest2 as select object_name col1 from dba_objects;
|
테이블 구조 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> desc lowertest
Name Null? Type
------ ------ -----
col1 VARCHAR2(128)
(col1 소문자)
SQL> desc lowertest2
Name Null? Type
------ ------ -----
COL1 VARCHAR2(128)
(COL1 대문자)
|
추가 테스트
컬럼 대소문자 섞어서 테이블 재생성
|
1
2
3
|
SQL>
drop table lowertest purge;
create table lowertest (col1 number, "col2" number, col3 varchar2(5), "col4" varchar2(5));
|
insert 시 컬럼 나열 없이 샘플 데이터 삽입
|
1
2
3
|
SQL> insert into lowertest values (1, 2, '3', '4');
1 row created.
|
정상적으로 삽입됨
insert 시 컬럼 나열 후 샘플 데이터 삽입
|
1
2
3
4
|
SQL> insert into lowertest (col1, col2, col3, col4) values (1, 2, '3', '4');
*
ERROR at line 1:
ORA-00904: "COL4": invalid identifier
|
에러 발생함
컬럼명 "" 를 이용해 소문자로 설정 후 삽입
|
1
2
3
4
|
SQL> insert into lowertest (col1, "col2", col3, "col4") values (1, 2, '3', '4');
1 row created.
commit;
|
정상적으로 삽입됨
테이블 구조 확인
|
1
2
3
4
5
6
7
|
SQL> desc lowertest
Name Null? Type
------ -------- -----------
COL1 NUMBER
col2 NUMBER <-- 소문자
COL3 VARCHAR2(5)
col4 VARCHAR2(5) <-- 소문자
|
select * 로 테이블 조회
|
1
2
3
4
5
6
|
SQL> select * from lowertest;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
col2, col4 소문자임
select 시 소문자만 사용해서 조회
|
1
2
3
4
|
SQL> select col1, col2, col3, col4 from lowertest;
*
ERROR at line 1:
ORA-00904: "COL4": invalid identifier
|
에러 발생함
select 시 컬럼명에 "" 사용해서 조회
|
1
2
3
4
5
6
|
SQL> select col1, "col2", col3, "col4" from lowertest;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
정상적으로 조회됨
col2, col4 은 소문자임
여기서 다시 ""을 이용해 alias 로 조회하면 모두 대문자로 조회됨
|
1
2
3
4
5
6
|
SQL> select col1, "col2" "COL2", col3, "col4" "COL4" from lowertest;
COL1 COL2 COL3 COL4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
alias를 써서 대문자로 만들어주면 모두 대문자로 나옴
where 절 역시 ""을 넣지 않으면 에러 발생함
|
1
2
3
4
5
|
SQL> select * from lowertest
where col2 = 2;
*
ERROR at line 2:
ORA-00904: "COL2": invalid identifier
|
"" 사용시 정상적으로 조회됨
|
1
2
3
4
5
6
7
|
SQL> select * from lowertest
where "col2" = 2;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
인덱스 생성시에도 마찬가지로 에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL>
create index lowertest_ix1 on lowertest(col1);
Index created.
(정상)
create index lowertest_ix2 on lowertest(col2);
*
ERROR at line 1:
ORA-00904: "COL2": invalid identifier
(에러 발생)
create index lowertest_ix2 on lowertest("col2");
Index created.
(정상)
create index lowertest_ix3 on lowertest(col3);
Index created.
(정상)
create index lowertest_ix4 on lowertest(col4);
*
ERROR at line 1:
ORA-00904: "COL4": invalid identifier
(에러 발생)
create index lowertest_ix4 on lowertest("col4");
Index created.
(정상)
|
해당 테이블 쿼리 플랜 확인
statistics_level 파라미터 세션레벨에서 all로 변경
|
1
2
3
|
SQL> alter session set statistics_level = all;
Session altered.
|
테이블 조회1
|
1
2
3
4
5
6
|
SQL> select * from lowertest where col1 = 1;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 a b
1 2 a b
|
xplan display_cursor 조회 테이블 조회1
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1nv7xbyf8pnfn, child number 1
-------------------------------------
select * from lowertest where col1 = 1
Plan hash value: 2424170803
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 2 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LOWERTEST | 1 | 2 | 68 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | LOWERTEST_IX1 | 1 | 2 | | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LOWERTEST@SEL$1
2 - SEL$1 / LOWERTEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "LOWERTEST"@"SEL$1" ("LOWERTEST"."COL1"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "LOWERTEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL1"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "COL1"[NUMBER,22], "LOWERTEST"."col2"[NUMBER,22], "LOWERTEST"."COL3"[VARCHAR2,5], "LOWERTEST"."col4"[VARCHAR2,5]
2 - "LOWERTEST".ROWID[ROWID,10], "COL1"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[LOWERTEST]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
56 rows selected.
|
43번 째 줄에서 보면 자동으로 col1 컬럼을 ""을 이용해서 대문자로 변경해서 조회함
테이블 조회2
|
1
2
3
4
5
6
|
SQL> select * from lowertest where "col2" = 2;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 a b
1 2 a b
|
xplan display_cursor 조회2
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID akzxu3usspmy4, child number 0
-------------------------------------
select * from lowertest where "col2" = 2
Plan hash value: 4099624448
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 2 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LOWERTEST | 1 | 2 | 68 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | LOWERTEST_IX2 | 1 | 2 | | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LOWERTEST@SEL$1
2 - SEL$1 / LOWERTEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "LOWERTEST"@"SEL$1" ("LOWERTEST"."col2"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "LOWERTEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("col2"=2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "LOWERTEST"."COL1"[NUMBER,22], "col2"[NUMBER,22], "LOWERTEST"."COL3"[VARCHAR2,5], "LOWERTEST"."col4"[VARCHAR2,5]
2 - "LOWERTEST".ROWID[ROWID,10], "col2"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[LOWERTEST]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
56 rows selected.
|
43번 째 줄에서 보면 where 절에 작성한것처럼 col2 컬럼을 ""을 이용해서 소문자로 조회함
테이블 조회(문자형 컬럼인 col3을 숫자형으로 조회)
|
1
2
3
4
5
6
|
SQL> select * from lowertest where col3 = 3;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
xplan display_cursor 조회3
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0f47vxcpjjwk0, child number 0
-------------------------------------
select * from lowertest where col3 = 3
Plan hash value: 597123558
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| LOWERTEST | 1 | 2 | 68 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LOWERTEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "LOWERTEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("COL3")=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "LOWERTEST"."COL1"[NUMBER,22], "LOWERTEST"."col2"[NUMBER,22], "COL3"[VARCHAR2,5],
"LOWERTEST"."col4"[VARCHAR2,5]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[LOWERTEST]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
53 rows selected.
|
40번 쨰 줄에 보면 자동으로 형변환이 되고 to_number안에 col3가 자동으로 "" 처리 되어서 대문자로 나옴
테이블 조회(문자형 컬럼인 "col4"을 숫자형으로 조회)
|
1
2
3
4
5
6
|
SQL> select * from lowertest where "col4" = 4;
COL1 col2 COL3 col4
---------- ---------- ----- -----
1 2 3 4
1 2 3 4
|
xplan display_cursor 조회4
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2syvzqtdutd3c, child number 0
-------------------------------------
select * from lowertest where "col4" = 4
Plan hash value: 597123558
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 2 |00:00:00.01 | 8 |
|* 1 | TABLE ACCESS FULL| LOWERTEST | 1 | 2 | 68 | 3 (0)| 00:00:01 | 2 |00:00:00.01 | 8 |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / LOWERTEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "LOWERTEST"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("col4")=4)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "LOWERTEST"."COL1"[NUMBER,22], "LOWERTEST"."col2"[NUMBER,22], "LOWERTEST"."COL3"[VARCHAR2,5],
"col4"[VARCHAR2,5]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[LOWERTEST]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
53 rows selected.
|
40번 쨰 줄에 보면 자동으로 형변환이 되고 to_number안에 col3가 where 에서 작성한대로 "" 처리 되어서 소문자로 나옴
결론 :
오라클에서 테이블 생성 시 자동로 대분자 컬럼으로 만들어짐
굳이 ""를 이용해 소문자로 만들면 쿼리 사용시 불편함
xplan 상에서 보면 자동으로 해당 컬럼 소문자, 대문자 상관없이 모두 ""가 들어감
하지만 datapump expdp나 impdp 시 job name을 소문자로 쓰면 소문자 object 로 만들어짐
이 경우 https://positivemh.tistory.com/902 내용처럼 job이 비정상 중단된 경우 ""를 이용해서 지워줘야하기 때문에
대문자를 사용하는게 좋아보임
참조 :
https://positivemh.tistory.com/902
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 테이블스페이스 BIGFILE/SMALLFILE Default 값 변경 (0) | 2023.06.28 |
|---|---|
| 오라클 19c 인덱스 생성 및 rebuild 시 발생하는 lock 확인 (0) | 2023.06.28 |
| 오라클 19c insert 쿼리 logical read 확인 (0) | 2023.06.02 |
| 오라클 19c 일반 dml 시 발생하는 lock 확인 (0) | 2023.06.02 |
| 오라클 19c ASM usable_file_mb -(음수) 됬을때 조치방법 (0) | 2023.05.23 |