# 테이블 생성 (데이터 타입은 TEST, NUMERIC, INTEGER, REAL, BLOB 등)
c.execute("CREATE TABLE IF NOT EXISTS table1 \
(id integer PRIMARY KEY, name text, birthday text)")
일단 "test.db"라는 데이터베이스를 생성해봤다. 브라우저로 열어서 보면 잘 보일 거다. 엑셀에서 첫 행을 적어두었다 생각하면 된다.
c.execute("CREATE TABLE IF NOT EXISTS 테이블이름()")안에 문자열로 필드(열) 이름과 데이터 타입을 작성해주면 된다.
CREATE TABLE IF NOT EXITSTS 테이블_이름() 이라는 쿼리는 말 그대로 “테이블_이름이라는 테이블이 없으면 테이블을 생성해라”라는 의미다. 있으면 있는 걸 그대로 쓰는 거고. 그 안에 필드를 나열해주면 되는데 필드명 > 테이터 타입 순으로 입력한다. PRIMARY KEY는 테이블 내에 있는 레코드를 식별하는 고유 키를 말하며, 유일한 것이어야 한다.
개념 1. commit & rollback
위에서 sqlite3.connect() 괄호 안에 isolation_level=None이라고 명시했는데 이는 (실습을 위해) 쿼리문을 실행하여 DB에 즉시 반영, 즉시 자동 커밋을 하기 위함이다.
commit(커밋)은 “변경사항을 DB에 반영한다”는 뜻이라 commit을 하지 않으면 수정(추가/갱신/삭제 등) 작업에 대한 기록을 컴퓨터 메모리 같은 데 임시로 가지고 있을 뿐 실제로 DB에는 반영하지 않는다. 최종적으로 DB를 수정을 하려면 마지막에 반드시 conn.commit()이라는 명령을 실행해주어야 한다.
참고로 이것과 commit과 반대되는 개념으로 rollback(롤백)이 있다. 이전 이력으로 되돌린다는 뜻. conn.rollback()으로 명령한다.
개념 2. cursor
파이썬에서 파일을 읽고 쓰려면 커서를 가져와야 한다. 그래서 conn.cursor()로 일단 커서를 생성한다.
데이터 삽입하기
데이터 한 건을 넣으려면 c.execute(“INSERT INTO table1 VALUES()”)라고 해서 값을 직접 넣자.
# 데이터 삽입 방법 1
c.execute("INSERT INTO table1 \
VALUES(1, 'LEE', '1987-00-00')")
테이블 안에 데이터가 들어간 걸 확인할 수 있다. 그런데 이렇게 하는 건 필드명과 순서를 정확히 알고 있다는 전제 하에 가능한 거다.
만약 정석으로 데이터를 삽입하려면 아래와 같이 튜플로 넣어주는 게 좋다.
# 데이터 삽입 방법 2
c.execute("INSERT INTO table1(id, name, birthday) \
VALUES(?,?,?)", \
(2, 'KIM', '1990-00-00'))
이번엔 튜플이나 리스트 형태의 데이터 세트를 한 번에 삽입하는 방법도 알아야겠지. c.executemany()를 사용하면 된다.
test_tuple = (
(3, 'PARK', '1991-00-00'),
(4, 'CHOI', '1999-00-00'),
(5, 'JUNG', '1989-00-00')
)
c.executemany("INSERT INTO table1(id, name, birthday) VALUES(?,?,?)", test_tuple)
끝이다. 리스트로도 가능하다.
데이터 불러오기
이제 데이터들이 잘 있나 확인해야겠지. 우선 데이터를 모두 선택한 다음에 c.fetchone()으로 하나씩 출력해보자.
c.execute("SELECT * FROM table1")
print(c.fetchone())
print(c.fetchone())
print(c.fetchall())
c.fetchone()을 사용하면 한 줄씩 출력하는 걸 알 수 있다. 커서가 이동하기 때문이다. 그리고 이후에 c.fetchall()이라는 걸 사용해서 전체를 가져와서 출력하더라도 이미 읽은 지점 이후에 있는 것들만 출력된다. (참고로 fetch는 “가져오다”라는 뜻.)
그래서 만약 전체 데이터를 출력하고 싶다면 이렇게 전체를 다시 읽어 놓고 해야 한다.
c.execute("SELECT * FROM table1")
print(c.fetchall())
그리고 이렇게 가져온 데이터는 리스트 형태로 출력되는 걸 알 수 있는데, 결국 반복문을 돌 수 있다는 뜻이다.
# 방법 1
c.execute("SELECT * FROM table1")
for row in c.fetchall():
print(row)
# 방법 2
for row in c.execute("SELECT * FROM table1 ORDER BY id ASC"):
print(row)
방법 2가 한 줄 덜 쓰긴 하지만 가독성이 떨어져서 차라리 방법 1이 나은 것 같다.
데이터 조회하기 (필터링)
원하는 데이터만 찾아서 가져올 수도 있어야겠지. SQL에서 WHERE 문을 써야 한다.
그런데 이때 방법이 이것저것 있다. 포매팅을 할 수도 있고, 딕셔너리 형식으로 설정해서 가져올 수도 있다. 좀 복잡하니 해보면서 익숙한 방법을 사용하면 된다.
여기서는 id라는 변수가 어떤 녀석인 것들만 선택해서 가져올지 짜봤다.
# 방법 1
param1 = (1,)
c.execute('SELECT * FROM table1 WHERE id=?', param1)
print('param1', c.fetchone())
print('param1', c.fetchall())
# 방법 2
param2 = 1
c.execute("SELECT * FROM table1 WHERE id='%s'" % param2) # %s %d %f
print('param2', c.fetchone())
print('param2', c.fetchall())
# 방법 3
c.execute("SELECT * FROM table1 WHERE id=:Id", {"Id": 1})
print('param3', c.fetchone())
print('param3', c.fetchall())
# 방법 4
param4 = (1, 4)
c.execute('SELECT * FROM table1 WHERE id IN(?,?)', param4)
print('param4', c.fetchall())
# 방법 5
c.execute("SELECT * FROM table1 WHERE id In('%d','%d')" % (1, 4))
print('param5', c.fetchall())
# 방법 6
c.execute("SELECT * FROM table1 WHERE id=:id1 OR id=:id2", {"id1": 1, "id2": 4})
print('param6', c.fetchall())
처음엔 좀 어렵지만, 익숙해지면 어렵지 않게 쓸 수 있겠지.
데이터 수정하기
위에서 조회할 때 사용한 WHERE 문과 함께 UPDATE를 통해 갱신하는 거다.
id가 무엇인 녀석을 찾아서 name을 새로운 걸로 고쳐보자. 이렇게.
# 방법 1
c.execute("UPDATE table1 SET name=? WHERE id=?", ('NEW1', 1))
# 방법 2
c.execute("UPDATE table1 SET name=:name WHERE id=:id", {"name": 'NEW2', 'id': 3})
# 방법 3
c.execute("UPDATE table1 SET name='%s' WHERE id='%s'" % ('NEW3', 5))
# 확인
for row in c.execute('SELECT * FROM table1'):
print(row)
편한 방법으로 사용하자.
데이터 삭제하기
테이블에 있는 특정 데이터를 지우려면 WHERE과 DELETE를 조합하면 된다.
# 방법 1
c.execute("DELETE FROM table1 WHERE id=?", (1,))
# 방법 2
c.execute("DELETE FROM table1 WHERE id=:id", {'id': 3})
# 방법 3
c.execute("DELETE FROM table1 WHERE id='%s'" % 5)
# 확인
for row in c.execute('SELECT * FROM table1'):
print(row)
만약 테이블에 있는 데이터 전체를 지우려면 conn.execute() 안에 쿼리문을 써주면 된다.
# 방법 1
# conn.execute("DELETE FROM table1")
# 방법 2
print(conn.execute("DELETE FROM table1").rowcount)
뒤에 rowcount를 붙여주면 지운 행 개수를 돌려준다.
DB 연결 해제
데이터베이스를 연결해서 이런저런 수정을 했으면 마지막엔 그 연결을 해제해야 한다. 그래서 항상 conn.close() 명령으로 마무리한다.
DB 백업하기 (dump)
데이터베이스는 항상 dump를 통해 백업을 해놓는 게 중요하다. 그래야 나중에 다른 PC에서도 이걸 그대로 재구성할 수 있다.
with conn:
withopen('dump.sql', 'w')as f:
for line in conn.iterdump():
f.write('%s\n' % line)
print('Completed.')
이래놓고 sql 파일을 열어보면 신기하게 아래와 같이 생겼다.
BEGINTRANSACTION;
CREATETABLE table1(id integer PRIMARYKEY, name text, birthday text);
모듈이 최상위 코드 환경에서 실행되면 해당 __name__이 '__main__' 문자열로 설정됩니다.
Python 모듈 또는 패키지를 가져오면 __name__이 모듈 이름으로 설정됩니다. 일반적으로 .py 확장자가 없는 Python 파일 자체의 이름입니다.
"최상위 코드 환경"이란? __main__은 최상위 코드가 실행되는 환경의 이름입니다. "최상위 코드"는 실행을 시작하는 첫 번째 Python 모듈입니다. 프로그램에 필요한 다른 모든 모듈을 가져오기 때문에 "최상위"라고 합니다. "최상위 코드"를 애플리케이션의 진입점이라고도 합니다.
결과적으로 모듈은 __name__을 확인하여 최상위 환경에서 실행 중인지 여부를 확인할 수 있습니다. 이는 모듈이 import 문에서 초기화되지 않을 때 조건부로 코드를 실행하는 일반적인 관용구를 허용합니다.
if __name__ == '__main__':
# Execute when the module is not initialized from an import statement.
...
관용적 사용법 일부 모듈에는 명령줄 인수를 구문 분석하거나 표준 입력에서 데이터를 가져오는 것과 같이 스크립트 전용 코드가 포함되어 있습니다. 예를 들어 단위 테스트를 위해 이와 같은 모듈을 다른 모듈에서 가져온 경우 스크립트 코드도 의도하지 않게 실행됩니다.
여기서 if __name__ == '__main__' 코드 블록을 사용하면 편리합니다. 이 블록 내의 코드는 모듈이 최상위 환경에서 실행되지 않는 한 실행되지 않습니다.
if __name__ == '__main__' 아래 블록에 가능한 한 적은 수의 명령문을 넣으면 코드 명확성과 정확성이 향상될 수 있습니다. 대부분의 경우 main이라는 함수는 프로그램의 기본 동작을 캡슐화합니다.
# echo.py
import shlex
import sys
def echo(phrase: str) -> None:
"""A dummy wrapper around print."""
# for demonstration purposes, you can imagine that there is some
# valuable and reusable logic inside this function
print(phrase)
def main() -> int:
"""Echo the input arguments to standard output"""
phrase = shlex.join(sys.argv)
echo(phrase)
return 0
if __name__ == '__main__':
sys.exit(main()) # next section explains the use of sys.exit
모듈이 main 함수 내부에 코드를 캡슐화하지 않고 대신 if __name__ == '__main__' 블록 내에 직접 넣는 경우, phrase 변수는 전체 모듈에 대해 global 변수가 됩니다. 모듈 내의 다른 함수가 의도하지 않게 local 변수 대신 global 변수를 사용할 수 있으므로 오류가 발생하기 쉽습니다. main 함수는 이 문제를 해결합니다.
main 함수를 사용하면 echo 함수 자체가 격리되고 다른 어느 곳에서나 import할 수 있다는 이점이 있습니다.
echo.py를 가져오면 echo와 main 함수가 정의되지만 둘 다 호출되지 않습니다.
왜냐하면 __name__ != '__main__' 때문입니다.
패키징 고려 사항 주요 기능은 콘솔 스크립트의 진입점으로 지정하여 명령줄 도구를 만드는 데 자주 사용됩니다. 이 작업이 완료되면 pip는 main의 반환 값이 sys.exit()에 전달되는 템플릿 스크립트에 함수 호출을 삽입합니다. 예를 들어:
sys.exit(main()) main에 대한 호출은 sys.exit()에 래핑되어 있으므로 함수가 sys.exit()에 대한 입력으로 허용되는 일부 값을 반환할 것입니다. 일반적으로 정수 또는 None(함수에 return 문이 없는 경우 암시적으로 반환됨)입니다.
이 규칙을 따랐다면 모듈은 나중에 pip-installable 패키지의 콘솔 스크립트 진입점으로 패키징할 때와 마찬가지로 직접 실행할 때(예: python3 echo.py) 동일한 동작을 갖게 됩니다.
특히 main 함수에서 문자열을 반환할 때 주의하십시오. sys.exit()는 문자열 인수를 실패 메시지로 해석하므로 프로그램은 실패를 나타내는 종료 코드 1을 가지며 문자열은 sys.stderr에 기록됩니다.
__main__.py in Python Packages
일반적으로 __main__.py 파일은 패키지에 대한 명령줄 인터페이스를 제공하는 데 사용됩니다.
__main__.py는 패키지 자체가 -m 플래그를 사용하여 명령줄에서 직접 호출될 때 실행됩니다. 예를 들어:
$ python3 -m bandclass
이 명령은 __main__.py가 실행되도록 합니다. 이 메커니즘을 사용하는 방법은 작성 중인 패키지의 특성에 따라 다르지만 이 경우 교사가 학생을 검색하도록 허용하는 것이 합리적일 수 있습니다.
# bandclass/__main__.py
import sys
from .student import search_students
student_name = sys.argv[2] if len(sys.argv) >= 2 else ''
print(f'Found student: {search_students(student_name)}')
from .student import search_students는 상대적 import의 예입니다. 이 import 스타일은 패키지 내에서 모듈을 참조할 때 사용할 수 있습니다.
관용적 용법
__main__.py의 내용은 일반적으로 if __name__ == '__main__' 블록으로 구분되지 않습니다. 대신 해당 파일은 다른 모듈에서 실행하기 위해 짧게 유지됩니다. 그런 다음 이러한 다른 모듈은 단위 테스트를 쉽게 수행할 수 있으며 적절하게 재사용할 수 있습니다.
if __name__ == '__main__' 블록을 사용하면 패키지 내의 __main__.py 파일에 대해 예상대로 작동합니다. import가 되면 __name__ 속성에 패키지의 경로가 포함되기 때문입니다.
이것은 .zip 파일의 루트 디렉토리에 있는 __main__.py 파일에는 작동하지 않습니다. 따라서 일관성을 위해 위에서 언급한 venv와 같은 __main__.py가 더 좋습니다.
import __main__
Python 프로그램이 시작된 모듈에 관계없이 동일한 프로그램 내에서 실행되는 다른 모듈은 __main__ 모듈을 가져와서 최상위 환경의 범위(네임스페이스)를 가져올 수 있습니다. 이것은 __main__.py 파일을 가져오는 것이 아니라 특별한 이름 '__main__'을 받은 모듈을 가져옵니다.
다음은 __main__ 네임스페이스를 사용하는 예제 모듈입니다.
Here is an example module that consumes the __main__ namespace:
# namely.py
import __main__
def did_user_define_their_name():
return 'my_name' in dir(__main__)
def print_user_name():
if not did_user_define_their_name():
raise ValueError('Define the variable `my_name`!')
if '__file__' in dir(__main__):
print(__main__.my_name, "found in file", __main__.__file__)
else:
print(__main__.my_name)
이 모듈의 사용 예는 다음과 같습니다
# start.py
import sys
from namely import print_user_name
# my_name = "Dinsdale"
def main():
try:
print_user_name()
except ValueError as ve:
return str(ve)
if __name__ == "__main__":
sys.exit(main())
프로그램을 실행하면 결과는 다음과 같습니다.
$ python3 start.py
Define the variable `my_name`!
프로그램의 종료 코드는 오류를 나타내는 1이 됩니다. my_name = "Dinsdale"이 있는 줄의 주석 처리를 제거하면 프로그램이 수정되고 이제 성공을 나타내는 상태 코드 0으로 종료됩니다
$ python3 start.py
Dinsdale found in file /path/to/start.py
__main__을 가져오면 시작 모듈의 if __name__ == "__main__" 블록에 있는 스크립트 사용을 위한 최상위 코드를 의도하지 않게 실행하는 데 문제가 발생하지 않습니다. 왜 이런 식으로 작동할까요?
Python은 인터프리터 시작 시 sys.modules에 빈 __main__ 모듈을 삽입하고 최상위 코드를 실행합니다.
이 예에서는 한 줄씩 실행하고 namely를 import하는 시작 모듈입니다. 차례로 __main__(실제로 시작됨)을 import합니다.
이것이 import cycle입니다.
부분적으로 채워진 __main__ 모듈이 sys.modules에 있기 때문에 Python은 이를 namely로 전달합니다.
Python REPL은 "최상위 환경"의 또 다른 예이므로 REPL에 정의된 모든 것이 __main__ 범위의 일부가 됩니다.
이 경우 __main__ 범위는 대화형이므로 __file__ 속성을 포함하지 않습니다. __main__ 범위는 pdb 및 rlcompleter의 구현에 사용됩니다.
파이썬은 왜 프로그램의 시작점이 정해져 있지 않나요?
파이썬이 처음에 개발 될 당시에는 리눅스/유닉스에서 사용하는 스크립트 언어 기반이었기 때문에 프로그램의 시작점이 따로 정해져 있지 않았습니다. 보통 리눅스/유닉스의 스크립트 파일은 파일 한 개로 이루어진 경우가 많은데, 이 스크립트 파일 자체가 하나의 프로그램이다 보니 시작점이 따로 필요하지 않습니다. 하지만 C 언어나 자바같은 언어는 처음 만들어질 때부터 소스 파일을 여러 개 사용했기 때문에 여러 소스 파일의 함수들 중에서도 시작 함수(main)를 따로 정해 놓았습니다.
Linux 쉘이나 Dos 명령창에서 "sqlite3 first.db"를 수행하면 "first.db"라는 새로운 데이터베이스를 생성한다. 명령어를 수행한 경로에 first.db 파일이 생성된다. 즉 별도의 DB 생성 명령어는 존재하지 않고, sqlite3 명령어로 새로은 database 생성한다. 만약, 기존에 데이터베이스가 존재하면 기존 데이터베이스에 접속한다.
$ sqlite3 first.db
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite>
query로 table 목록 조회 type, name, 테이블명, rootpage, sql 이 보여진다. 조회 시 header를 보이게 하려면 .headers on|off 를 사용한다.
아래 예제는 .headers on 을 사용한 결과이다.
sqlite> SELECT * FROM sqlite_master WHERE type='table';
type|name|tbl_name|rootpage|sql
table|tbl1|tbl1|2|CREATE TABLE tbl1(one varchar(10), two smallint)
-- table_name만 조회
sqlite> SELECT tbl_name FROM sqlite_master WHERE type='table';
tbl_name
tbl1
추가 DB 생성
생성하고자 하는 DB명으로 새로운 DB를 생성한다.
$ sqlite3 second.db
SQLite version 3.37.0 2021-11-27 14:13:22
Enter ".help" for usage hints.
sqlite>
transaction 제어(commit, rollback, savepoint 등)
데이터베이스에 액세스하는 모든 명령(기본적으로 일부 PRAGMA 문을 제외한 모든 SQL 명령)은 트랜잭션이 적용되지 않은 경우 자동으로 트랜잭션을 시작한다. 자동으로 시작된 트랜잭션은 마지막 SQL 문이 완료될 때 커밋된다.
트랜잭션은 BEGIN 명령을 사용하여 시작할 수 있다. 이러한 트랜잭션은 일반적으로 COMMIT 또는 ROLLBACK 명령까지 지속됩니다. 그러나 데이터베이스가 닫히거나 오류가 발생하고 ROLLBACK 충돌 해결 알고리즘이 지정된 경우 트랜잭션도 ROLLBACK됩니다. END TRANSACTION은 COMMIT과 동일하다. 아래 transaction에 대한 rollback과 commit 예제이다
-- transaction rollback
sqlite> create table trans(one varchar(10), two smallint);
sqlite> begin transaction;
sqlite> insert into trans values('hello',10);
sqlite> insert into trans values('goodbye', 20);
sqlite> rollback;
sqlite> select * from trans;
-- 입력한 데이터가 rollback 되어 한건도 조회되지 않는다.
-- transaction commit(= end transaction);
sqlite> begin transaction;
sqlite> insert into trans values('goodbye', 20);
sqlite> end transaction;
sqlite> select * from trans;
one|two
goodbye|20
sqlite3_get_autocommit() 인터페이스는 주어진 데이터베이스 연결이 자동 커밋 모드에 있으면 0이 아닌 값을, 그렇지 않은 경우에는 0을 반환한다. sqlite3 DB는 자동 커밋 모드가 기본적으로 켜져 있다. 자동 커밋 모드는 BEGIN 문에 의해 제어된다.
그러므로, 프로그램 작성 시 transaction을 제어하도록 해야 한다.
아래는 java 프로그램으로 transaction을 제어하는 예제이다.
SQLiteConnection con = new SQLiteConnection();
con.exec("BEGIN");
// do transaction work - auto-commit is disabled
con.exec("COMMIT");
// auto-commit is enabled again
DB 계정
사용자 이름/비밀번호는 sqlite/sqlite3 패키지에서 지원되지 않는다.
인증이 필요한 데이터베이스를 구성할려면 PHP와 별도로 SQLite를 컴파일해야 한다.(SQLite User Authentication) 자세한 내용은 SQLite 사용자 인증 문서를 참조한다.
DB접속 종료
sqlite> 프롬프트에서 .quit를 입력하여 프로그램을 종료하거나 .exit CODE 를 입력하면 return-code 로 프로그램을 종료한다.
여기서 CODE 부분은 sqlite 종료 시 리턴값으로 사용될 부분이므로 원하는 값으로 정의해서 사용하면 된다.
Window인 경우에는 Control-C를 Linux인 경우에는 Control-D를 입력하여 sqlite3 프로그램을 종료할 수도 있다.
sqlite DB는 서버리스 DB이기 때문에 접속이 종료되면 process가 보이지 않는다.
DB Dump
명령창에서 아래와 같이 SQLite 명령어를 치면 데이터베이스를 export할 수 있다.
Syntax : sqlite3 db명 .dump > dump파일명
$sqlite3 firstDB.db .dump > firstDB.sql
위의 명령은 firstDB.db 데이터베이스의 전체 내용을 SQLite 문으로 변환하고 ASCII 텍스트 파일 firstDB.sql로 덤프합니다. 생성된 firstDB.sql에서 다음과 같은 방법으로 복원을 수행할 수 있습니다.
AWS Oracle RDS 에서는 X$ 테이블에 접근할 수 있는 권한이 없어, 기존에 hidden parameter 를 조회했던 아래 SQL 이 수행되지 않습니다. sys 계정으로 조회해야 하나, AWS Oracle RDS DB로 sys 계정 접근이 불가합니다.
AWS RDS가 아닌 일반 DB인 경우 아래 query를 수행하여 hidden parameter 를 조회합니다. hidden_parameter_name 부분에 조회를 원하는 파라미터명을 입력하면 됩니다.
SELECT KSPPINM
, KSPPSTVL
FROM X$KSPPI X, X$KSPPCV Y
WHERE X.INDX = Y.INDX
AND X.KSPPINM LIKE '%hidden_parameter_name%'
AND SUBSTR(X.KSPPINM, 1, 1) = '_'
AWS Oracle RDS hidden parameter 조회
dbms_utility.get_parameter_value function을 이용해서 AWS Oracle RDS hidden parameter를 조회하는 프로시저입니다. 프로시저를 실행하면 hidden 파라미터 입력 프롬프트가 나타나고 원하는 hidden 파라미터명을 입력하면 hidden 파라미터값(value)이 출력됩니다.
파라메터 값(Value) 이 숫자형 또는 Boolean형 이면 intval 에 출력, 문자형이면 strval 에 출력 또는 String인 경우에는 파라미터값의 길이(Boolean형 일경우 intval 이 1이면 True, 0이면 False )
set serveroutput on
set lines 200
prompt Enter a value for parameter
DECLARE
intval BINARY_INTEGER;
strval VARCHAR2(256);
partyp BINARY_INTEGER;
intval2 VARCHAR2(12);
BEGIN
dbms_output.put_line(chr(13));
dbms_output.put_line('+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-');
partyp := dbms_utility.get_parameter_value('&1',intval, strval);
IF partyp = 1 THEN
dbms_output.put('parameter value is: ');
dbms_output.put_line(strval);
dbms_output.put_line('parameter type is: STRING/FILE parameter');
dbms_output.put('parameter value length is: ');
dbms_output.put_line(intval);
ELSE
dbms_output.put('parameter value is: ');
IF intval=0 THEN intval2:='FALSE';
dbms_output.put_line(intval||'/'||intval2);
dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
ELSIF intval=1 THEN intval2:='TRUE';
dbms_output.put_line(intval||'/'||intval2);
dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
ELSE
dbms_output.put_line(intval);
dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
END IF;
END IF;
dbms_output.put_line('+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-');
END;
/
간단한 프로시져로 v_parnam 에 조회하고자 하는 파라미터명을 입력한다.
아래 프로시저는 hidden 파라미터인 _cursor_obsolete_threshold 를 조회하는 예제이다.
declare
v_parnam varchar2(100) := '_cursor_obsolete_threshold' ;
v_intval binary_integer ;
v_strval varchar2(100) ;
v_ret varchar2(1000);
begin
v_ret := dbms_utility.get_parameter_value (
parnam => v_parnam,
intval => v_intval,
strval => v_strval
);
dbms_output.put_line(v_parnam||' : ');
dbms_output.put_line(' Type : '||v_ret);
dbms_output.put_line(' INT Val : '||v_intval);
dbms_output.put_line(' STR Val : '||v_strval);
end;
/
이제 WSL에서 그래픽 애플리케이션을 수행할 수 있는 환경을 확인했으니, Linux GUI 프로그램을 수행하여 GUI 프로그램을 사용할 수 있습니다. 본문서에서는 nautilus을 설치합니다.
sudo apt install nautilus
nautilus 명령어로 실행한 화면입니다. 실행하면서 몇 개의 오류 메시지가 뜨는데 아래와 같이 실행은 됩니다.
nautilus
참고 :
XServer(예: 저장된 config.xlaunch를 사용하는 vcxsrv.exe)를 시작하고 Windows DOS 배치 파일에서 XWindows 세션을 시작할 수 있습니다. 이 방법을 사용하면 DOS ipconfig를 사용하여 가상 이더넷 WSL2 IP를 결정하고 세션을 시작하기 전에 DISPLAY 환경 변수를 설정할 수 있습니다. 이것은 가상 이더넷 IP가 Windows를 재부팅할 때마다 변경되기 때문에 유용합니다(따라서 위의 DISPLAY에서 awk를 사용해야 함). 배치 파일의 이름을 지정하고(이 배치 파일 코드 주석 참고) config.xlaunch 파일이 있는 동일한 디렉토리(이 스크립트는 c:\bin\)에서 실행하세요.
@echo off
rem Works ONLY if Ethernet adapter vEthernet (WSL): is the LAST "adapter" in
rem the ipconfig output; that is, this for loop assigns the IPs from first to
rem last from the ipconfig printed output, so the WSL virtual adapter must be the
rem last one in the ipconfig output for this to work correctly.
rem If you have excellent DOS batch file skills, you may find a better
rem way of doing this.
for /f "tokens=1-2 delims=:" %%a in ('ipconfig^|find "IPv4"') do set ip==%%b
set ipAddress=%ip:~1%
rem Trim whitespace
call :TRIM %ipAddress% ipAddress
rem Use this for testing
rem echo IP Address: %ipAddress%
rem Start up the xServer using saved xlaunch configuration
rem This configuration creates a single large window
start /B config_one_window.xlaunch
rem Start up PulseAudio server, will accept audio from external
rem source, i.e., from the X server
start "" /B "C:\bin\pulseaudio-5.0-rev18\pulse\pulseaudio.exe"
rem Call ubuntu.exe, have it run what is essentially a startup
rem script that sets two ENV variables using the ipAddress variable
rem created above, which is the virtual IP to which services
rem INSIDE the running X server can connect; these are the
rem link between WSL2 and Windows 10
rem Initiate an xfce4 session with dbus-launch
rem when the X server is killed, do cleanup by
rem killing pulseadio and vcxsrv
<PATH_TO_UBUNTU_EXE>\ubuntu.exe run "export DISPLAY="%ipAddress%:0.0"; export PULSE_SERVER="%ipAddress%"; if [ -z \"$(pidof xfce4-session)\" ]; then dbus-launch --exit-with-session ~/.xsession; pkill '(gpg|ssh)-agent'; taskkill.exe /IM pulseaudio.exe /F; taskkill.exe /IM vcxsrv.exe; fi;"
rem Function to trim white space or the export VARIABLE will fail
:TRIM
SET %2=%1
GOTO :EOF
데이터 분포가 왜곡된 ID라는 VARCHAR2 열이 있는 테이블을 가지고 있다고 할 때 이 테이블에 대해 자동 통계 작업이 시작되면 거의 모든 SQL 문에서 사용되며 데이터 왜곡이 있기 때문에 이 열에 히스토그램이 자동으로 생성됩니다. 그러나 ID 열의 값은 매우 길고 각 ID의 처음 32자는 동일할 데이터라고 가정합니다.(데이터베이스는 Oracle Database 11g임).
Oracle이 VARCHAR2 열에 히스토그램을 생성할 때 Oracle Database 12c에서 열 값의 처음 64자 또는 Oracle Database 11g에서 열 값의 처음 32자만 고려합니다. 즉, 열 값의 처음 32/64바이트가 동일하면 실제로 값이 다르고 다른 히스토그램 버킷에 있는 것으로 가정하더라도 모든 값이 히스토그램의 단일 버킷에 포함됩니다. Optimizer는 이 히스토그램을 사용할 때 잠재적으로 카디널리티를 잘못 추정할 수 있으며, 이는 차선의 계획을 초래할 수 있습니다. 고유 값 및 기타 통계의 수는 정확하지만 히스토그램에만 이 문제가 있습니다. 이러한 이유로 현재 또는 미래에는 이 특정 열에 히스토그램을 사용하지 않는 것이 좋습니다. 그렇다면 기존 히스토그램을 삭제하고 향후 수집되지 않도록 하려면 어떻게 해야 할까요?
BEGIN
dbms_stats.delete_column_stats(
ownname=>'SH', tabname=>'SALES', colname=>'PROD_ID', col_stat_type=>'HISTOGRAM');
END;
2.DBMS_STATS.SET_TABLE_PREF 프로시저를 사용하여 PROD_ID 컬럼을 제외한 SALES 테이블의 모든 컬럼에 대해 히스토그램을 수집하도록 합니다. 아래 프로시저를 수행하면 PROD_ID 컬렁에 대한 히스토그램을 생성하지 않습니다. 하나의 버킷만을 생성하므로 통계 정보로써의 의미가 없도록 하는 것입니다.
BEGIN
dbms_stats.set_table_prefs('SH', 'SALES',
'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 PROD_ID');
END;
/
컬럼에 히스토그램을 자동 통계가 생성하도록 하려면?
자동 통계 수집 작업이 PORD_ID 컬럼에 히스토그램을 다시 생성도록 하려면 아래와 같이 DBMS_STATS.SET_TABLE_PREF 프로시저를 사용합니다. Oracle 이 자동으로 통계 정보에 대한 버킷을 생성하도록 합니다.
BEGIN
dbms_stats.set_table_prefs('SH', 'SALES',
'METHOD_OPT',
'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE AUTO PROD_ID');
END;
/
최초 테이블 Lock을 의심해서 Lock 정보를 조회하려 DB에 접속 시도를 했으나, 접속이 되지 않았다.
몇 번의 접속 시도를 하는데 갑자기 접속이 되어 DB를 보니 다른 Muiti-Zone으로 failover가 된 상태였다.
DB 시작시간을 확인해 보니 2021/12/28 오전 10:39:09 였다. 장애 발생 이후 시간이다.
이후 AWS RDS의 모니터링과 Performnace Insights 를 살펴보았다.
특정 시점에서 평소 5미만인 Active session이 478까지 증가하였고,
그 시점의 SQL이 library cache lock, library cache : mutex X, regmgr CPU quantum, kksfbc child completion, CPU 지표들이 상당히 높계 계측되었다.
CloudWatch가 수집한 서버 모니터링 정보를 보니,
CPU, DB Connections, Write IOPS, read IOPS 등의 장애 시점에 평소 대비 높았다.
특히 Freeable Memory는 장애 시점에 0으로 여유 메모리가 없는 상황이 발생했다. 이런 상황에서 AWS에서 RDS를 장애로 판단하고 Failover 시킨 것으로 보인다.
일단, SQL 을 추출하여 Plan 정보를 확인해 보았다. SQL문은 Bind 변수를 사용하였다.
특별한 점은 없어 보였다.
library cache lock event 로 보아, 장애 시점에 DDL이 있었을 것이라고 판단하고 object 들의 생성일자와 수정일자를 살펴보았다. 그 시점에 생성이나 변경된 object는 보이지 않았다. ( 생성하고 삭제했을 경우에는 알수가 없을까?)
장애 원인으로 가정한 아래 세가지 상황은 아니다.
1) 테이블 LOCK
2) library cache lock 이 발생한 SQL 관련 object의 생성이나 수정
3) 과도한 SQL 문장(bind 변수없이 literal 사용 포함)
그래서 library cache lock, library cache : mutex X 2가지 event를 추적해 보았다.
첫번째 왜 이런 이벤트(library cache lock, library cache : mutex X)가 발생하는가?
library cache lock 은
SQL 구문을 파싱할 때나 LCO를 참조할 때 획득해야 하는 Lock으로 library cache lock 획득하지 못하여 대기하며 발생하는 이벤트이다.
library cache : mutex X
shared pool에서 cursor를 검색할 때 mutex를 획득하지 못하여 대기하며 발생하는 이벤트이다
결론은 child cursor가 너무 많아 검색 시간이 오래 걸리면서 다른 세션들이 library cache lock, library cache : mutex X 를 획득하는데 대기하는 이벤트가 발생한 것으로 판단해 볼 수 있다.
장애 시점의 child cursor 에 대한 상세 정보를 추적해 본다.
1. snap_id 확인
dba_hist_snapshot 에서 장애 발생 시점의 snap_id를 확인해 보았다.
- 장애 직전 snapshot : 649
- 장애 이후 snapshot : 650
DB 기동 시간 : 2021/12/28 AM 10:39:09.000
2. sql_id와 SQL 관련 정보 확인
SQL fulltext와 sql_id 등의 정보를 얻고자 v$sql 을 조회하려고 했으나 v$sql은 dynamic performance view로 DB가 기동된 이후의 정보만 조회할 수 있으므로 failover된 상태라 dba_hist_sqltext과 dba_hist_sqlstat 뷰를 조회했다.
dba_hist_sqltext 에서 sql_id를 추출했다.
추출한 sql_id로 dba_hist_sqlstat 을 조회했다. 특이사항을 확인해 본다.
plan_hash_value 가 0 인 경우가 많다.
메모리 부족현상이 있었으니 sharable_mem 컬럼과 주의깊게 살펴볼 필요가 있는 dba_hist_sqlstat 테이블의 컬럼(loaded_versions, version_count, fetches_total, fetches_delta, executions_total, executions_delta, invalidations_total, invalidations_delta, parse_calls_total, parse_calls_delta, buffer_gets_total, buffer_gets_delta, rows_processed_total, rows_processed_delta, elapsed_time_total, elapsed_time_delta) 등을 선정해 분석해 본다.
장애 시점에 sharable_mem 값이 상당이 높게 나왔다. 그러나 다른 날의 자료와 비교해 볼 때 상대적으로 특이사항은 보이지 않는다.
version_count 값을 조회해 보니 상당이 높게 나왔다. 다른 날 대비 약간의 이상 징후가 보인다.
parse_calls_delta 값의 추이도 특이사항이 보이지 않는다.
elapsed_time_delta 값의 추이도 장애 시점 상 특이사항이 보이지 않는다.
1차 dba_hist_sqlstat 으로 분석한 결과, version_count 에 대한 분석이 필요한 것으로 판단된다.
* 특이사항으로는 위 컬럼의 정보들이 수집되지 않았다. NULL 값들이 상당히 많다 --> 왜 정보를 수집하지 못했는지에 대한 확인이 필요해 보인다. 정보수집을 못했을 때에 version_count가 상대적으로 상당히 값이 크다.
<참고사항>
장애 시점에 서버 메모리 사용률이 100% 이었는데 이는 어떻게 설명되는가? 아래 이미지를 보면 이해할 수 있을 것이다.
(1) 커서란?
공유 커서 (shared cursor) : 라이브러리 캐시에 공유돼 있는 Shared SQL Area 세션 커서 (session cursor) : Private SQL AREA에 저장된 커서 애플리케이션 커서 (application cursor) : 세션 커서를 가리키는 핸들
추가적으로, 대기 이벤트들이 발생하면서 Active session이 급격히 증가하였고
이는 서버 프로세스를 생성하며 메모리를 소진시키는 원인이 되었다.
1차 결론은
version_count 때문에 장애가 발생한 것을 추정하고 이 값을 중심으로 분석해 본다.
version_count는 무엇인가? 왜 version_count가 높게 나오는 것일까?
version_count란
SQL은 한번 파싱되면 실행계획을 생성하고 재사용하기 위해서 실행정보를 생성한다. ( 이를 LCO: library cache object 형태로 저장되며 shared cursor 라고 합니다.)
그리고 메모리(Shared pool 안의 library cache 안의 Shared SQL Area)에 저장합니다.
동일한 SQL 이더라도 여러가지 이유(주1)로 실행정보를 공유하지 못하면 추가적인 Chlid LCO ( child cursor )를 생성합니다.
즉, 새로운 버전의 child cursor 가 생성되는 것입니다. 이를 version count 가 증가한다고 표현합니다.
하나의 SQL(parent cursor)에 대해서 여러 child cursor 가 생기면 가장 문제가 되는 것은
SQL 수행시에 어떻게 실행할지 실행정보를 탐색하는데 오래걸리고,
경합이 발생할 수 있다는 점과 (이 때 발생하는 Wait Event를 library cache latch 라고 합니다.)
메모리의 공간을 차지한다는 점입니다.
아래 MOS 문서도 참고할 만합니다.
Troubleshooting: High Version Count Issues (Doc ID 296377.1)
1. SQL에서 참조하는 오브젝트명이 같지만 SQL을 실행한 사용자에 따라 다른 오브젝트를 가리킬 때 - N/A
2. 참조 오브젝트가 변경돼 커서가 무효화되면 이후 그 커서를 처음 사용하려는 세션에 의해 다시 하드파싱돼야 하는데
, 특정 세션이 아직 기존 커서를 사용 중(Pin)일 때 - N/A
3. 옵티마이저 모드를 비롯해 옵티마이저 관련 파라미터가 다를 때
Optimizer mismatch : The optimizer environment does not match the existing child cursor
[ Optimizer Mode 외에 옵티마이져 관련 파라미터 불일치시 ]
4. 입력된 바인드 값의 길이가 크게 다를 때
5. NLS 파라미터를 다르게 설정했을 때 - NA
6. SQL trace를 활성화했을 때 - N/A
위의 사유 중 해당 사항을 찾기 위해
version_count가 높게 나온 이유를 파악하기 위해서 V$SQL_SHARED_CURSOR 조회한다.
mismatch 컬럼이 약 61개 정도라 일일이 'Y'인 값을 파악하는 것이 쉽지 않다.
아래 SQL을 수행하면 sql 문장이 출력되는데
출력된 SQL의 '&v_sql_id' 에 자신의 'sql_id' 를 입력한 후 query를 수행하면 mismatch 컬럼값이 'Y'인 것을 count 해서 보여준다.
select q'<select reason_not_sharing, count(*)
from gv$sql_shared_cursor
unpivot ( flag for reason_not_sharing in (>' || listagg(column_name, ', ') within group (order by null) || q'<) )
where sql_id = nvl('>' || '&' || q'<v_sql_id', sql_id)
and flag = 'Y'
group by reason_not_sharing>' x
from dba_tab_columns
where owner = 'SYS'
and table_name = 'V_$SQL_SHARED_CURSOR'
and data_type = 'VARCHAR2'
and data_length = 1;
-------- Results ------
REASON_NOT_SHARING:COUNT(*)
USE_FEEDBACK_STATS:47775
BIND_EQUIV_FAILURE:47787
LOAD_OPTIMIZER_STATS:6
커서가 공유되지 않은 이유로 USE_FEEDBACK_STATS, BIND_EQUIV_FAILURE 로 조회되었다.
위의 query에서 찾은 컬럼으로 다시 아래 SQL로 reason까지 같이 조회해 본다.
SQL>
select BIND_EQUIV_FAILURE, use_feedback_stats, substr(s, instr(s, '<reason>')+8, instr(s, '</reason>') - (instr(s, '<reason>') + 8) ) res, count(*)
from
(
select BIND_EQUIV_FAILURE, use_feedback_stats, DBMS_LOB.substr(reason, 100, 1) s
from v$sql_shared_cursor where sql_id = '69v32zxbv1mzk'
)
group by BIND_EQUIV_FAILURE, use_feedback_stats, substr(s, instr(s, '<reason>')+8, instr(s, '</reason>') - (instr(s, '<reason>') + 8) )
order by 1;
------- Results --------------------------------
BIND_EQUIV_FAILURE:USE_FEEDBACK_STATS:RES:COUNT(*)
Y:N:Bind mismatch(24):6
Y:N:Bind mismatch(25):6
Y:Y:Bind mismatch(22):564
Y:Y:Bind mismatch(24):1145
Y:Y:Optimizer mismatch(13):46183
커서가 공유되지 않은 이유로 USE_FEEDBACK_STATS, BIND_EQUIV_FAILURE이고 reason은 Bind mismatch와 Optimizer mismatch 로 조회되었다.
우선 bind mismatch가 발생한 사유를 알아보자.
BIND_MISMATCH : The bind metadata does not match the existing child cursor.
Oracle은 varchar2 TYPE을 32, 128, 2000, 4000 구간으로 나누어 bind 값을 사용하기 때문에 bind값의 길이에 따라 bind mismatch 가 발생한다. 그러나, 우리의 경우에는 bind를 4개 사용했고 각각 varchar2(27), varchar2(27), varchar2(1000), varchar2(1000) 이었다. 경우의 수를 따져도 9개(1*1*3*3)이다. 즉, 순수 bind 값의 길이 때문에 child cursor 가 새로 생겼다고 보기는 어려울 것 같다.
커서가 공유되지 않은 원인으로 도출된 두 개의 값에 대해 분석해 본다.
BIND_EQUIV_FAILURE = 'Y'
USE_FEEDBACK_STATS = 'Y'
이 두 값이 의미하는 것이 무엇인지 확인해 본다.
USE_FEEDBACK_STATS : 카디널리티 피드백. 카디널리티 피드백이 사용 중이고 현재 실행에 대한 새 계획이 생성될 수 있다.
BIND_EQUIV_FAILURE :바인드 값의 선택도가 기존 자식 커서를 최적화하는 데 사용된 선택도와 일치하지 않습니다. adaptive cursor sharing이 설정되어 있고 커서가 바인드를 인식할 때 선택도가 현재 범위를 벗어나 새로운 Plan이 더 요구되는 경우 이전 Plan을 공유하지 않고 새로운 child cursor 생성합니다. 오라클 문서 836256.1을 참조하십시오.
... once the cursor is marked as bind aware and a second plan is seen then the following will be the resultant output:
SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER B ------------- ---------------- ---------------- ------------ - 19sxt3v07nzm4 000000007A1C0DE0 000000007A1BF980 0 N 19sxt3v07nzm4 000000007A1C0DE0 000000007A10DDB0 1 Y
As can be seen, the new version is created due to BIND_EQUIV_FAILURE
select sql_id, address, child_address, child_number, BIND_EQUIV_FAILURE from v$sql_shared_cursor where sql_id='19sxt3v07nzm4';
커서가 바인드를 인식하게 되면 이 커서가 실행될 때마다 확장 커서 공유 레이어 코드(Extended Cursor Sharing layer code)가 바인드 변수 값(본 문서의 경우에는 4개의 바인드 변수가 있음)을 보고(Peeking) v$sql_cs_selectivity에 대한 select를 실행한다. 기존 child 커서가 Peeking한 바인드 변수의 선택도를 수용하고 있는지 확인하기 위해서이다. 자식 커서가 발견되면 공유하고 그렇지 않은 경우 새 child 커서가 바인드 변수 값 선택성의 새 범위와 함께 v$sql_cs_selectivity에 입력된다. 이 경우 확장 커서 공유 레이어 코드(Extended Cursor Sharing layer code)가 v$sql_cs_selectivity에서 적절한 선택 범위(BIND_EQUIV_FAILURE)를 가진 자식 커서를 찾지 못하면 새로운 실행계획을 컴파일하고 다수의 "optimal" plan으로 v$sql 뷰를 채운다.
체크사항 :
V$SQLAREA에서 커서의 VERSION_COUNT가 높다.
커서를 공유하지 않는 이유가 V$SQL_SHARED_CURSOR에 BIND_EQUIV_FAILURE로 표시된다.
쿼리에는 커서 공유와 관련된 바인드 변수를 사용했다.
선택도 범위(V$SQL_CS_SELECTIVITY)가 많으며 겹친다.
동시성이 높은 경우 문제가 "커서: 핀 S는 X에서 대기"로 표시되며 커서에 대해 대기한다.
위의 경우에 해당될 경우 버그로 의심할 수 있다. 그럴 경우 workaroud로 다음 설정을 권고한다. 본 문서가 오라클 공식 문서가 아니기에 적용 시 반드시 Oracle의 지원을 받아 진행해야 한다.
Workaround : _optimizer_extended_cursor_sharing_rel = none 으로 설정
결국은 optimizer_adaptive_cursor_sharing 때문인 것 같다.
DB 전체에 대해 사용하지 않도록 설정하던지, 아니면 문제를 일으키는 SQL 관련 테이블의 컬럼에 대해서만 설정하던지.
일단, 후자를 선택해 진행해 본다.
대상 테이블 컬럼의 histogram 을 삭제하고, 더 이상 histogram을 생성하지 않도록 설정한다.
상기 장애와 관련하여 최선의 선택이 아닐까 한다. 물론 다른 부작용도 검토를 해야 한다.
Disable the Adaptive cursor sharing in Oracle
alter system set "_optimizer_adaptive_cursor_sharing"=false scope= both;
--Disable bind variable or other parameter alter system set "cursor_sharing"=exact scope=both; alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=both; alter system set "_optimizer_extended_cursor_sharing"=none scope= both;
Three 11g features of cost-based optimization and SQL execution to consider when upgrading from 10g:
Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve execution plans for repeated executions. See Support note 1344937.1 and documentation for additional info. You can be disable cardinality feedback with an underscore parameter which can be set at session or instance level. Usual caveats on setting underscore parameters apply: alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE; This can also be done with a hint, therefore at statement level, using the opt_param syntax:/*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */
Adaptive cursor sharing. This was introduced in 11gR1 to address SQL performance consistency issues related to bind variable peeking. See support note 740052.1 and documentation for details. For the purposes of this doc, if you want to get the 10g behavior with an Oracle version higher than 10g and overall if you want to disable this feature, you may want to add the hint: /*+ NO_BIND_AWARE */. According to support note 11657468.8 adaptive cursor sharing can be disabled by setting the following 2 parameters (say at session level): _optimizer_adaptive_cursor_sharing = false, _optimizer_extended_cursor_sharing_rel = "none"
For more details on this bug, the Document ID of MOS is 14176247.8
(Y|N) A hard parse is forced so that the optimizer can reoptimize the query with improved cardinality estimates
옵티마이저가 향상된 카디널리티 추정으로 쿼리를 다시 최적화할 수 있도록 하드 구문 분석이 강제 실행됩니다.
AWR Report 확인
AWS RDS Oracle Enterprise version에서만 제공한다.
ASH Report 확인
alert log 파일 확인
tarce 파일 확인
<참고>
semaphore vs mutex 란?
mutex : object, 자물쇠, locking mechanism, multiple program threads 이 single recource에만 접근 가능(동시 접근 불가), 오직 mutex를 acquire/release 할 수만 있다(Locked or unlocked)
카디널리티라는 용어는 두 가지 다른 의미로 사용됩니다. 데이터 모델링에서 사용할 때와 SQL 문에서 사용할 때입니다. 데이터 모델링에서 사용할 때 카디널리티는 한 테이블이 다른 테이블과 가질 수 있는 관계를 나타냅니다. 다대다, 다대일/일대다 또는 일대일의 테이블 간의 관계를 카디널리티라고 합니다. SQL에서 사용할 때는 해당 열에 대한 테이블에 나타나는 고유한 값의 수를 나타냅니다.
data modeling 에서의 cardinality
회사에서 직원 정보를 저장하는 데 사용하는 세 개의 테이블(Employee 테이블, Employee_Salary 테이블 및 Department 테이블)이 있다고 가정합니다. 모든 직원은 하나의 부서에만 속할 수 있지만 부서는 여러 직원으로 구성될 수 있기 때문에 부서 테이블은 Employee 테이블과 일대다 관계를 갖습니다. 즉, 직원 테이블과 관련된 부서 테이블의 카디널리티는 일대다입니다. Employee_Salary 테이블과 관련된 Employee 테이블의 카디널리티는 직원이 하나의 급여만 가질 수 있고 그 반대도 마찬가지이므로 일대일입니다(예, 두 명의 직원이 동일한 급여를 가질 수 있지만 여전히 정확히 하나의 급여 항목이 있습니다. 다른 사람이 동일한 급여를 받는지 여부에 관계없이 각 직원에 대해).
만일, 한명의 직원이 여러 부서에 소속될 수 있다고 한다면 부서와 직원간의 관계는 다대다 관계입니다.
SQL에서의 Cardinality
카디널리티의 다른 정의는 아마도 용어의 더 일반적으로 사용되는 버전일 것입니다. SQL에서 주어진 테이블에 있는 열의 카디널리티는 해당 열에 대한 테이블에 나타나는 고유한 값의 수를 나타냅니다. 따라서 카디널리티는 숫자라는 것을 기억하십시오. 예를 들어 "남성"과 "여성"이라는 두 가지 가능한 값만 있는 "성별" 열이 있는 테이블이 있다고 가정해 보겠습니다. 그런 다음 해당 열에 나타날 수 있는 고유한 값은 남성과 여성이라는 두 가지뿐이므로 해당 "성별" 열의 카디널리티는 2입니다. Cardinality정보는 Execution Plan 정보에서 볼 수 있습니다.
primary key의 cardinality
또는 다른 예로 10,000개의 행이 있는 테이블에 기본 키 열이 있다고 가정해 보겠습니다. 해당 열의 카디널리티는 무엇이라고 생각합니까? 자, 10,000입니다. 기본 키 열이기 때문에 열의 모든 값이 고유해야 함을 알고 있습니다. 그리고 10,000개의 행이 있기 때문에 열에 10,000개의 항목이 있다는 것을 알고 있으며, 이는 해당 열에 대해 10,000개의 카디널리티로 변환됩니다. 따라서 기본 키 열의 카디널리티는 항상 동일한 테이블의 레코드 수와 동일하다는 규칙을 생각해낼 수 있습니다
cardinality 가 0 이라는 의미는?
열의 카디널리티가 0이면 열에 고유한 값이 없음을 의미합니다. 이것은 열의 값이 NULL인 경우입니다.
