'프로그래밍 언어/데이터베이스'에 해당되는 글 4건

  1. 2011.10.19 [SQLITE] 명령어 정리.
  2. 2010.12.27 [오라클-튜닝]. 유용한 스크립트 모음
  3. 2010.06.22 [SQLite] 리눅스에서 스크립트 백업 & 복구
  4. 2010.01.21 자연키(Natural Key) 대 대체키(Surrogate Key) (2)
자꾸 까먹어서 인터넷 검색을 하는게 싫어서 정리합니다 -_-;

SHELL 명령어

.tables : 테이블 목록을 보여준다.

.schema [table_name] : CREATE문을 보여준다.


SQL 명령어

select * from sqlite_master;
:
신고
Posted by 소혼
TAG sqlite3
데이터베이스 사랑넷에 있는 글이었던 걸 옛날 블로그에서 펌질했었죠.
옛날 블로그에서 다시 옮겨옵니다. (아직 유효한 정보인지 모르겠지만 옛날 블로그 폐쇄를 위해...)

테이블이 사용중인 블록 크기를 계산해주는 SQL


/*
** Table이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
**
** 사용법 : 1) DBA 권한으로 로그인한다.
** 2) SQL> @tab_block [table명]
**
** Notice : sum(blocks)는 사용하는 블럭의 갯수이며 사이즈는 db_block_size를
** 곱하여 얻을 수 있다.
*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트

/*
** PL/SQL 소스를 보기위한 스크립트.. <박제용>
**
** 사용법 : find_plsql [프로시져명칭]
**
**
*/
select text
from user_source
where name = upper('&1')
order by line;

테이블을 복사해주는 스크립트 (v8.0 only)

/*
** table을 다른 스키마 혹은 table로 복사 <<박제용>>
**
** Notice) 1. Oracle 8.0 이상에서만 지원.
** 2. sql*net 이 설정되어 있어야만 한다.
** 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
** 따라서 테이블을 생성해 주고 입력하는것이 좋다.
** 4. sql*plus 에서만 실행된다.
** 사용법) @tab_copy scott/tiger@link source_table_name target_table_name
**
*/
copy from &1 create &3 using select * from &2

/* 다른 DB로 복사할때는
copy from &1 to &2 create &4 using select * from &3
*/

/* 미리 만들어진 table에 입력할때는
copy from &1 insert &3 using select * from &2
*/

Table Data Size를 정확히 계산해주는 스크립트

/*
** Table Data Size를 정확히 계산해주는 스크립트. <<박제용>>
**
** 사용법 : @tab_size [table_name]
**
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

SELECT GREATEST(4, ceil(NUM_ROWS/
( (round(((1958-(INI_TRANS*23))*((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) TableSize_Kbytes
FROM user_tables
WHERE table_name = upper('&1');

dead lock이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL

/*  
**  
**  사용법   :SQL> @find_deadlock
**  Description : 데드락이 발생할 경우 locking 된 유저와 sql문을 보여준다.
**  
**  데드락이 발생한 유저를 kill 하려면.
** Alter system kill session '{serial#},{SID}';
**
*/
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in( select distinct e.id1 from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL

/*
** 딕셔너리로부터 입력한 키워드에 관한 테이블명을 조회한다. <<박제용>>
**
** 사용법 : SQL> @dic_find [키워드(대소문자가림)]
**
*/
col TABLE_NAME format a15
col COMMENTS format a100

select * from dictionary
where COMMENTS like ('%&1%')
/

컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL

/*
** 컬럼의 스펙과, 소속 테이블을 찾는다. <<박제용>>
**
** 사용법 : SQL> @col_find [컬럼명]
**
*/
col CNAME format a20
col COLTYPE format a10
col NULLS format a5
col DEFAULTVAL format a10

select TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
from col
where CNAME = UPPER('&1')
/

Constraint 이름으로 해당 테이블과 컬럼찾는 SQL

/*
**=============================================
** CONSTRAINT 이름으로 사용 테이블 찾기
**=============================================
**
** Usage : @Show_Columns Constraint_Name
** Description : Shows The Columns Bound By A Constraint
** 사용예 : SQL> @show_Columns PK_EMPNO
*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');

컬럼에 걸려있는 constraint 를 보여주는 SQL

/*
**=======================================
** 해당 COLUMN에 걸려 있는 CONSTRAINT확인
**=======================================
**
** Usage : @Show_Constraints Table_Name Column_Name
**
** Description : 해당 Table의 Column에 걸려 있는 Constraint를 보여준다.
**
** < 실행 예 >
** SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
**
*/

SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

PK와 FK간의 연관관계를 찾아 보여주는 SQL

/*  
**  
**  사용법     :> @Show_Positions  Parent_Table  Child_Table  
**  Description  :  Shows Primary And Foreign Key Positions  
**  
**  WARNING   :  이 문장은 해당 Table의 Constraint생성시 Naming   
**          Convention을 따른 경우에 적용되도록 되어 있다.  
**
*/
SET VERIFY OFF  
CLEAR BREAK  
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&2') AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

테이블의 특정 컬럼에 중복된 값을 찾는 SQL

/*
**=============================================
** 중복된 값 있는지 찾기
**=============================================
** Usage : @중복찾기.sql [테이블명] [중복을조사할컬럼명]
**
** Warning : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째 값부터 출력됨. <>
*/

select * from &1 A
where rowid >
(SELECT min(rowid) FROM &1 B
WHERE B.&2 = A.&2)
order by &2;
신고
Posted by 소혼

안드로이드는 데이터베이스로 SQLite3를 사용한다.
sqlite3 은 가볍게 사용할 수 있어 임베디드에서 많이 사용하는 오픈소스 데이터베이스 엔진이다.

프로젝트에서 DB가 필요한데 sqlite 에 직접 insert하기 귀찮아 스크립트로 sql을 생성했더니 sqlite3에 넣을 방법이 필요했다. 노가다로 붙여넣기 신공을 사용하려 했으나 아주 간단하게 처리가 가능했다.

linux만을 전제한다.

1) sql 명령어 set으로 backup받는 경우

echo ".dump" | sqlite3 mydb.db > backup.script

2) sql 명령어 집합으로 db 파일 만들기

sqlite3 < backup.script


(backup.script는 SQL로 이루어진 text 파일이다)
허접한 글이지만, 도움이 되신다면 아래 손가락 꾹 눌러주세요.
신고
Posted by 소혼

<B Tree Index - www.filibeto.org/sun/lib/nonsun/...t169.gif 발췌>

오랜만에 DB를 만지게 되었다.
sqlite3의 내부 구조를 모르지만, File DB라고 들었는데 성능을 위한 옵션 같은것이 분명 존재할 것 같다.

어쨌거나 모바일에서 테이블 한두개 만드는데 문제가 되겠냐만, 옛날 DB 공부할 때 생각에 Primary Key를 Natural Key로 할 지, Surrogate Key로 할지에 대해 고민해봤다.

생각보다 입맛에 딱 맞는 자료를 못찾아 그냥 내 맘데로 생각을 풀어본다.

먼저 자연키가 무엇이고 대체키가 무엇인지부터 정리해보기로 한다.
기본키 ( Primary Key ) : 테이블에서 레코드를 유일하게 식별하는 데 가장 적합한 후보키(Candidate Key)
자연키 ( Natural Key ) : 테이블을 이루는 컬럼들 가운데 의미를 담고 있는 후보키
대체키 ( Surrogate Key ) : 테이블을 이루는 컬럼들 가운데 유일하게 식별하기에 적합한 단일 후보키가 존재하지 않을 때, 임의의 식별번호로 이루어진 후보키를 추가할 수 있는데 이를 대체키라고 한다.

Primary Key는 두가지 특성을 가지고 있다고 생각한다.
< 제약과 인덱스 >

제약은 원래 관심사항이 아니었고, 인덱스가 중요하다고 생각한다.
Primary Key는 일반적으로 인덱스를 구축한다. 심지어 SQL Server의 경우는 Cluster Index가 되기도 한다.
인덱스가 검색을 위한 것이기 때문에 검색에서 핵심이 되는 값이 Primary Key가 될수록 성능에 지대한 공헌을 하게 된다.

일반적으로 대체키의 도입을 고민하는 경우는, 자연키가 숫자로 이루어지지 않는 경우이다.
만약 자연키가 숫자로 있다면 당연히 대체키를 도입할 필요가 없다.

만약 자연키가 문자열이거나 하나 이상의 컬럼을 결합키로 만드어야 한다면 우리는 대체키를 도입할 것인가 고민해보아야 한다.
이것은 단순히 어느쪽이 좋다라고 할 수 있는 문제는 아닌 것 같다.

integer index와 문자열 인덱스의 성능은 당연히 integer index가 빠르다.
따라서 일반적인 경우 대체키를 사용하는 것이 낫다. 검색의 성능도 빠르고 크기도 작기 때문에 Block IO도 적게 발생할 가능성이 높다.

자연키는 그 자체가 의미를 갖는 검색이 될 수 있지만, 대체키는 레코드의 기본키를 이용한 수정, 삭제, 외래키 참조에만 효율적이다.
자연키는 자신이 정보를 가지고 있으므로, 테이블에서 자연키로 검색하는 경우가 대부분이고 수정, 삭제, 외래키 참조가 없다면 자연키를 인덱스로 사용하는 것이 낫다.
만약 둘 다 빈번하면 자연키를 사용하는 인덱스를 하나 더 만드는 것도 고려할 수 있을 것 같다.
그러나 범위검색의 가능성이 큰 자연키에게 클러스터나 클러스터드 인덱스는 양보하는 편이 좋을 것 같다.

대체키는 대체키를 생성하기 위한 트릭이 필요하다.
Oracle 의 Sequence나 auto increment가 전혀 비용을 수반하지 않는다고 생각하지 않는다. 하지만 입력할때 인덱스를 구축하는 비용은 문자열 인덱스가 클 것 같다. 어쨌거나 위에 언급한 것 같은 시스템이 지원하는 방법이 없어서 SELECT를 한번 더 사용해야 하는 DB의 경우라면 대체키 도입은 좋은 방법이 아닐 수 있다. 물론 검색이 더 중요한 경우가 대부분이므로 입력의 비용은 무시하고 생각할 수도 있을듯 하다.

정리하고자 적어봤는데
결국 뭘 어떻게 하라는 건지
어쨌든 대부분의 경우는 대체키가 좋지 않을까 조심스레 생각해본다.



신고
Posted by 소혼
이전버튼 1 이전버튼

티스토리 툴바