[Database/ibatis] iBatis – Basic Meta Data


mybatis-config.xml

핵심 MetaData.

properties 관리 : 애플리케이션에 배포 할 때, 

                   공통적인 부분이나 환경별 다른 값들은 Properties

                       독립적으로 관리

                       대개 DBMS에 접속하는 정보를 작성한다.

ClassName 관리 : FQCN(Full Qualified Class Name) 을 별칭(Alias)으로 관리

기타 설정 : 트랜잭션처리, 커넥션풀 설정, SQL 등 여러 환경설정을 등록,설정한다.



UserMapper.xml

SQL 를 갖는 MetaData

MyBatis Framework는 XML MetaData 를 사용하여 SQL구문을 관리한다.

(SQLxml 을 통해 캡슐화 되어 애플리케이션 외부로 분리함)


namespace 이용하여 Application 에서 하위의 SQL 을 참조한다.  

Query를 수행 후 resultType에 선언된 해당 VO의 Field에 Binding한다.

Query Alias, VO Field의 이름이 동일한 것 확인한다.




MyBatis Framework 의 이해

 - SQL, Connection, Transaction을 Meta Data Encapsulation 하였으며,

   (mybatis-config.xml / UserMapper.xml)

   Query의 수행 결과를 VO에 바인딩한다.

   (JDBC API를 사용하여 반복적으로 수행 하는 것을 한 줄로 표현한 것이다.)


List<User> list = sqlSession.selectList("UserMapper.getUserList");


 - MyBatis Framework 의 장점

    단순하다 (다른 라이브러리와 의존적 관계가 없다. )

    SQL 과 VO를 바인딩한다.

    생산성 / 성능 / 작업의 분배 (소스코드와 SQL 의 분리)

    관심사의 분리

       (DBMS 에 독립적인 API제공 및 

        JDBC API가 아닌 비지니스 객체만 가지고 작업)       

결론 : MyBatis Framework 는 JDBC 절차를 간결화, 추상화, 캡슐화한 library다.



   

'Database > ibatis' 카테고리의 다른 글

[Database/ibatis] iBatis – Basic Meta Data  (0) 2016.11.06
[Database/ibatis] Only JDBC  (0) 2016.11.06
[Database/ibatis] 일괄처리  (0) 2016.08.24

[Database/ibatis] Only JDBC


'Database > ibatis' 카테고리의 다른 글

[Database/ibatis] iBatis – Basic Meta Data  (0) 2016.11.06
[Database/ibatis] Only JDBC  (0) 2016.11.06
[Database/ibatis] 일괄처리  (0) 2016.08.24

[Database/ibatis] 일괄처리


대량의 데이터에 대하여 insert와 update의 속도를 높히는 목적으로 사용한다.

일괄처리하는 작업도중 delete method와 같은 중간의 결과를 알 수 없음.

수많은 데이터를 작업하게 되면 메모리에 부담이 있음.


startBatch()

https://ibatis.apache.org/docs/java/user/com/ibatis/sqlmap/client/SqlMapExecutor.html#startBatch()


executeBatch()

https://ibatis.apache.org/docs/java/user/com/ibatis/sqlmap/client/SqlMapExecutor.html#executeBatch()


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
try {
    tgtsqlMap.startTransaction();
    tgtsqlMap.startBatch();
 
    tgtsqlMap.delete("관련된 SQL문""매개변수");
    
    for (int i = 0; i < "insert 할만큼의 작업 수"; i++) {
        tgtsqlMap.insert("관련된 SQL문""집어넣을 VO");
    }                    
    
    tgtsqlMap.executeBatch();
    tgtsqlMap.commitTransaction();
    
catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    System.out.println("\nDB Exception appeared. all ongoing datas were rollbacked");
}finally {
    try {
        tgtsqlMap.endTransaction();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
 
cs


'Database > ibatis' 카테고리의 다른 글

[Database/ibatis] iBatis – Basic Meta Data  (0) 2016.11.06
[Database/ibatis] Only JDBC  (0) 2016.11.06
[Database/ibatis] 일괄처리  (0) 2016.08.24

[Database/Theory] 15. WHERE 1=1


1
2
select * from tst_tbl
where 1=1
cs

조건문에 있는 1=1은 있어도 되고 없어도 되는 구문이다. 그러나 이와 같이 1=1을 사용하는 이유는 간단하다.

차후 조건문을 용이하게 작성하기 위한것 뿐이다.

1
2
3
4
5
6
7
8
9
StringBuffer sql = new StringBuffer();
 
// sql에 쿼리문 가져온 것으로 가정.
 
if(true){
    sql.append("and tbl.aaa=1");
}else{
    sql.append("and tbl.aaa=2");
}
cs


위와 같은 경우를 사용할 때 주의할 점은 저 조건으로 인하여 의도치 않는 결과가 나오지 못하게 작성하도록 한다.

MyBatis는 xml파일에서 내부적으로 처리가 가능.

[Database/Theory] 14. view

테이블과 동일하게 사용되는 개체다. 사용자 입장에서 테이블과 동일하다.

1
2
create view v_users
as select id, name from users;
cs


 - 보안성이 좋다.

뷰에 있는 내용만 보여지게 되고, 테이블에 있는 실제 데이터들이 노출되지 않는다.

그냥 테이블을 분리하면 된다 생각되지만 일관성이 떨어진다거나 관리가 어려워진다.


- 복잡한 쿼리의 단순화가 된다.

1
2
3
4
5
6
7
8
9
select   e1.last_name 사원이름, d1.department_name 부서이름, j1.급여 급여
from     employees e1, departments d1, (select   department_id 부서아이디, min(salary) 급여 
                                        from     employees 
                                        group by department_id) j1
where    d1.department_id = e1.department_id and 
         j1.부서아이디 = e1.department_id and 
         j1.급여 = e1.salary
order by d1.department_name, e1.last_name
 
cs

위와 같은 쿼리가 있다면 view로 만들어서 편리하게 사용하면 된다.

([Database/Practice]02. Oracle hr account table(2) 참조)


- create, alter, drop문으로 생성, 수정이 가능하다.

update, insert도 가능하다.

테이블과 마찬가지로 view를 insert나 update 할 경우 

NULL이 허용되지 않는것에 주의하도록한다.


그러나 집계함수[AVG(), MIN(), MAX(), COUNT(), COUNT_BIG(), STDEV()], UNION ALL,

Cross Join, group by를 사용한 View는 수정이 불가능하다.


 - View의 종류

표준뷰, 분할뷰, Indexed 뷰가 있다.


- 표준뷰

1개 이상의 테이블을 이용하여 만든 뷰다. 사용빈도가 가장 높다.


- 분할뷰(Partitioned View)

1대 이상의 서버에 있는 여러 테이블들을 join하여 1개의 테이블터럼 보이도록 된 뷰


- indexed view

복잡한 쿼리를 뷰로 만들어 사용하지만 실제로는 원래 테이블에 접근하는 것이기 때문에,

원래의 쿼리와 비유하면 속도는 높아지지 않는다.


이렇듯이 자주 사용되는 뷰에 실제 데이터를 대입하여 테이블까지 접근하지 않고 뷰까지만

접근하므로 쿼리의 성능이 높아진다.


즉 실제 데이터가 존재하고 고유의 clustered index에 의해 정렬되어 있는 뷰다.


계산하는데 사용되는 집계함수를 사용하거나 join하는 경우에 사용된다.


실제 테이블의 변경에 의하여 indexed view의 변경도 수행해야되는 번거로움과 시스템 부하라는

단점이 있기 때문에 변동이 드문 테이블에 사용되는 것이 좋다.


1
2
3
4
5
create view v_users with schemabinding
as select id, name, (math_score - science_score) from users;
 
create unique clustered index v_users_idx
in v_users(id);
cs

위와 같은 방법으로 생성이 된다.


만약에 뷰를 생성하는 구문에서 where절에 범위를 지정한다면 table scan을 할 것이다.


그리하여 계산식이 포함된 indexed view를 이용하여 범위를 이용한 쿼리가 효과적이다.

그러나 생성되는 index의 양은 증가하게 된다.

1
select id from v_users where (math_score - science_score) > 50;
cs














13. index(3) - 생성 시기

index는 열단위로 생성된다. 또한 2개 이상의 열을 조합하고 생성할 수 있다.


 - where절에서 사용되는 열을 index로 만든다.

그 만큼 자주 사용할 가치가 있는 index로 만든다. 

select문보다 insert문이 자주 사용되면 그만큼 페이지 분할이 될 것이고 시스템의 성능이

저하될 것이다.


 - 중복이 많은 열은 index의 가치가 없다.

non-clustered index일지라도, 중복률이 테이블의 1% ~ 3% 이상이면 사용되지 않는다.


 - 외래키나 Join에 사용되는 열을 사용하는 것이 좋다.

그만큼 설계해야 될 중요성이 크다. 제약조건에 의하여 생성된 index는 삭제가 불가능하고

대량의 데이터의 변동이 발생된다.


 - insert, update, delete의 빈도수를 고려한다.

index는 읽는 것에 대하여 DB의 성능을 높히지만 변경작업은 그 반대의 영향을 끼친다.


- clustered index는 하나만 생성할 수 있다.

clustered index는 데이터 페이지를 최소로 읽기 때문에, 

조건절에서 가장 많이 사용되는 열에 생성하는 것이 바람직하다.

또한 조건절에서 Between과 같은 범위나 집계함수를 사용하는 경우가 좋다.


order by절에 자주 사용되는 열을 clustered index로 사용되는 것이 좋다.

clustered index의 leaf는 이미 정렬되어 있기 때문이다.


clustered index는 테이블에 하나의 열에서만 생성이 가능한데,

만약에 또 하나의 열을 범위로 조회 한다면 

포괄열이 있는 인덱스(index with included columns)를 생성하도록 한다.


 - clustered index가 아예 없는 경우도 좋다.

대용량의 데이터들이 무작위로 입력되다 보면 페이지 분할이 자주 발생될 수 있다.

차라리 non-clustered index로 지정해도 나쁘지 않다.


 - 사용하지 않는 index는 제거한다.

where 조건에서 사용되지 않는 열의 index는 제거할 필요가 있다.

DB공간의 확보 뿐만 아니라 데이터 삽입에 발생하는 부하도 많이 줄일수 있다.


 - 계산 열에서도 index를 활용할 수 있다.

예시를 들어본다.

1
2
3
4
5
6
7
8
9
10
11
create table tbl (
    int1 int,
    int2 int,
    sum  as int1 + int2 persisted
);
 
insert into tbl values(100100);
insert into tbl values(200200);
insert into tbl values(300300);
 
select * from tbl;
cs

int1    int2     sum

-----------------

100    100     200

200    200    400

300    300    300


sum이라는 열을 index를 사용하려면 아래와 같은 구문을 사용하면 된다.

1
create clustered index idx_tbl_sum on tbl(sum);
cs


- 포괄열이 있는 non-clustered index를 활용하면 

   쿼리 성능이 높아진다.

포괄열이 있는 인덱스(index with included column)는 non-clustered index의

leaf page에 데이터를 포함하고 있는 열을 의미한다. 


[Database/Theory] 12. index(2) - 내부적 동작에 의하면,

clustered index의 속도는 non-clustered index보다 빠르다.

추가로 where절에 범위를 입력할 경우, clustered index는 그 기준을 찾아서 탐색하지만

non-clustered index는 전체 테이블을 검색하게 된다. clustered index는 leaf page가

곧 데이터이고 정렬되어 있기 때문이다. 


그러나 non-clustered index는 leaf page를 거치고 다시 데이터 페이지에서 검색한다.

만약에 leaf page와 데이터가 공존한다면 굳이 데이터 페이지를 찾지 않아도

non-clusered index에서 모든 작업이 종료되므로 clustered index와 같은 효과가 나타난다.


예시를 위해 기존에 작성한 테이블을 다르게 작성해 보겠다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table users(
   number int,  
   id     varchar,
   name   varchar,
   phone  varchar
);
 
insert into users values(9'KYY''김윤영''011');
insert into users values(6'KEH''김은혜''016');
insert into users values(3'HKM''홍경모''011');
insert into users values(11'YHH''유형환''017');
insert into users values(12'YMH''유민호''016');
insert into users values(10'LTG''이태검''017');
insert into users values(2'BSH''백승현''011');
insert into users values(8'KKC''김경철''018');
insert into users values(4'JHJ''장희정''016');
insert into users values(5'JHM''정희민''011');
insert into users values(7'KHS''김희선''018');
insert into users values(1'BCE''배찬익''016');
cs


여기서 index with included column을 생성한다.

1
2
3
create      nonclustered index idx_users_include
    on      users(number)
    include (id, name); 
cs


아래와 같은 구조가 될 것이다.



이러한 구조에서 다음과 같은 쿼리를 수행할 경우, root와 leaf page에서

해당된 데이터만 읽게 된다.

1
select number, id, name from users where number < 10;
cs

즉 leaf page에 100번과 101번만 읽게 되면 select문에 있는 number, id, name이

모두 있게되므로 clustered index와 같은 효과를 낼 수 있다.


그러나 다음과 같은 쿼리를 수행할 경우 모든 data page까지 읽게되는

table scan을 하게 된다.

1
select number, id, name, phone from users where number < 10;
cs

이유인 즉슨 select문에 phone은 leaf page에 phone이 없기 때문에,

RID를 참조하여 data page에서 탐색할 수밖에 없다.


- 결론

포괄열이 있는 index는 non-clustered index에만 생성할 수 있다. 

clustered index에는 생성할 수 없을 뿐만 아니라 생성해도

clustered index에는 leaf page 자체가 data이기 때문에 생성의 의미가 없다.


포괄열이 있는 index는 index의 크기가 커지는 단점이 있다.


포괄열이 있는 index를 생성하면 쿼리의 성능이 급격히 향상된다.


포괄열이 있는 index가 있어도 select의 열이 그 포괄열에 포함되지 않으면

index는 사용되지 않는다.

12. index(2) - 내부적 동작

 - 내부적 동작

   * B-Tree(Balanced Tree)라는 구조에 의해 동작한다.

   * 자료구조의 Tree와 같은 구조이며 Root, Leaf와 같은 지칭이 있다.

   * 자료구조에서는 자료를 가진 것을 node라 부르지만 DB의 B-Tree에서는

     Page라 부른다.

   * 한 Page에 8Kbyte의 공간을 차지한다.

   * 특정 Data를 검색할 때, Root page를 시점으로 연결된 Page를 찾아나아간다.

   * table scan보다 확연한 탐색 속도의 차이가 있다.

   * 그러나 Data의 변경작업이(insert, delete, update => CUD라 부르겠다.) 잦으면      역효과가 나타난다.

   * 페이지 분할 작업이 발생되기 때문이다.

   * 가령 특정 페이지에 데이터가 삽입되면 index 특성상 정렬이 먼저 수행되고

     페이지에 공간이 부족하면 새로운 페이지가 생성되어 분할된다.

   * 그렇게 되면 tree의 구조가 크게 변경될 수 있다. 이에 따라 탐색이 기존보다

     복잡해질 수 있다.

   * 그러니까 결론은 분할이 많이 발생되면 시스템의 성능이 떨어진다.


   예시



  이 구조에서 iii와 ggg를 삽입하게 되면 페이지들이 분할된다. 




 - Clustered Index

    * 예시로 users라는 테이블을 생성해서 아래와 같은 데이터들을 삽입하겠다.

    * 데이터를 입력한 순서에 유의한다.

    * 참고로 입력하는 데이터들은 페이지 용량에 맞춰진다. 이러한 페이지들을

      Data Page 혹은 Heap area라 부른다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs



    * Clustered index는 영단어 사전과 유사하다.

    * heap area를 보면 입력한 데이터의 순서가 아닌 index로 지정된 열에 따라

      정렬이 된 후에 위와 같은 구조를 표현하고 있다.

    * 11. index(1) - 개요, 장단점, 종류 에 설명된 바에 의하면 clustered index는

      데이터가 순차적으로 정렬되어 있다는 성질이 있다.

    * Root, Leaf의 구조를 가지고 있으며 Leaf page 자체가 Data임을 알 수 있다.

    * non-clustered index보다 비교적으로 검색속도가 빠르지만 변경작업은 느리다.


 - Non-Clustered Index

    * clustered index와 같은 테이블로 예시를 들어본다.

    * 다시 위로 올리기 귀찮아 할까봐 다시 적어본다.

    * 역시 입력한 데이터의 순서에 유의한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs



   * clustered index와 달리 입력한 순서대로 Data page가 구성된다.

   * non-clustered index는 Data page를 건드리지 않고 

     별도의 index page를 생성한다.

   * data page를 참조하여 non-clustered index page의 leaf에서 index로 설정한

     열에 따라 데이터들이 정렬된다.

   * leaf에 1000 + #2와 같은 표기는 RID(Row ID, 데이터 위치 포인터)라 부른다.

     페이지 번호와 offset에 따라 실제 data로 이동된다.

   * clustered index와 비교하면, 데이터를 탐색할 때 

     거치는 페이지 수는 non-clusetered index가 1회가량 더 거치게 된다.

   * 물론 간단한 예시이지만, 데이터들이 방대해지면 큰 차이가 일어날 것이다.

   * 고로 Clustered index가 더 탐색속도가 빠르다.



 - 데이터의 추가(Clustered Index,  Non-Clustered Index)

   * 역시 위로 올리기 귀찮으니 한번 더 예시의 테이블을 작성한다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table users(
   id   varchar primary key,
   name varchar
);
 
insert into users values('KYY''김윤영');
insert into users values('KEH''김은혜');
insert into users values('HKM''홍경모');
insert into users values('YHH''유형환');
insert into users values('YMH''유민호');
insert into users values('LTG''이태검');
insert into users values('BSH''백승현');
insert into users values('KKC''김경철');
insert into users values('JHJ''장희정');
insert into users values('JHM''정희민');
insert into users values('KHS''김희선');
insert into users values('BCE''배찬익');
cs

   * 여기에서 두 개의 데이터를 추가한다.

1
2
insert into users values('SMK''서민기');
insert into users values('KJH''김주현');
cs


   * Clustered Index와 Non-Clustered Index의 B-Tree 구조를 본다.





   * 다시 한번 더 정리한다.

   * Clustered Index는 생성될 때 데이터 페이지 전체를 다시 정렬한다.

   * 대용량의 데이터가 있다면 시스템 부하가 발생될 수 있다.

   * Leaf가 곧 데이터다 따라서 인덱스 자체에 데이터가 포함된 것이라 볼 수 있다.

   * Non-Clustered Index와 비교할 때 검색속도는 빠르지만 변경작업은

     페이지 분할에 의하여 느리다.

   * 테이블 한개에 Clustered Index 하나만 생성이 가능하므로,

     어느 열에 Clustered Index를 지정하느냐에 따라 시스템 성능이 좌우된다.


   * Non-Clustered Index는 생성될 때 페이지를 그대로 둔 채,

      Index를 구성한다.

   * Leaf가 RID다.

   * Clustered Index와 비교할 때 검색속도는 느리지만 변경작업은 빠르다.

     (Heap Area뒤에 추가만 하면 되고 Leaf에서는 약간의 변동만 하면 되니까)

   * Non-Clustered Index는 한 테이블에 여러개를 생성할 수 있지만 

     지속적인 남용은 시스템의 성능이 저하되므로 필요한 것만 지정하도록 한다.



 - Clustered Index,  Non-Clustered Index의 혼합

   * 한 가지 추가한 예시로 설명을 본다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table users(
   id    varchar primary key,
   name  varchar unique,
   phone varchar
);
 
insert into users values('KYY''김윤영''011');
insert into users values('KEH''김은혜''016');
insert into users values('HKM''홍경모''011');
insert into users values('YHH''유형환''017');
insert into users values('YMH''유민호''016');
insert into users values('LTG''이태검''017');
insert into users values('BSH''백승현''011');
insert into users values('KKC''김경철''018');
insert into users values('JHJ''장희정''016');
insert into users values('JHM''정희민''011');
insert into users values('KHS''김희선''018');
insert into users values('BCE''배찬익''016');
cs

   * 구조는 아래와 같은 그림으로 형성되고 빨간색 글자를 보면

     데이터를 탐색하는 과정을 볼 수 있다.

   * Non-Clustered Index를 탐색하고 Clustered의 Root Page로 찾아가

     찾고자 하는 데이터를 탐색하는 순서로 되어있다.

   * 기존에 있는 Non-Clustered Index의 Leaf는 RID로 되어 있었지만

     혼합이 되면서 Clustered Index의 Key Value로 되어있다.

   * 이유 : 혼합하지 않고 분리되어 RID를 그대로 사용된다면 검색하는데 있어

     더 빠르겠지만, 

     새로운 데이터가 추가되면 Clustered Index의 Data page가 재구성되고

     RID의 표기가 모두 변경된다. 

     그 결과로 Non-Clustered Index 전체가 재구성되면서 

     엄청난 시스템 부하가 발생될 소지가 있다.

   * 그러니까 결론은 Non-Clustered Index, Clustered Index가 혼합되면 

     RID표기는 삼가한다.

   * 검색의 손해는 있지만 CUD의 손해보다는 확연히 작기 때문에 감안할 수 있다.


 - 용어

   * table scan : 테이블의 모든 데이터를 처음부터 끝까지 탐색하는 것.

   * index seek : Non-Clustered Index에서 데이터를 찾는다는 의미.

   * Clustered Index Seek : Clustered Index에서 데이터를 찾는다는 의미.

   * Clustered Index Scan : table scan과 동일한 의미. 

                                   Clustered Index의 Leaf가 Data Page이기 때문이다.

   ( Seek과 Scan은 다른 의미다.

     인덱스 검색을 위해 where절에 해당 인덱스의 이름을 작성하도록 한다.)


11. index(1) - 개요, 장단점, 종류


 - 인덱스의 개요

DB의 성능을 향상시키는 것이 목적.

적절한 사용으로 데이터에 빠르게 접근한다.

DB 튜닝의 큰 효과를 볼 수 있다.

과다한 사용은 성능이 떨어진다.

필요없는 인덱스가 많아지면 DB에 차지되는 공간이 많아져 table scan보다 속도가 느려질 수 있다.

(table scan : 인덱스를 사용하지 않고 DB를 처음부터 끝까지 탐색하는 것)


- 장점과 단점

무조건은 아니지만, 데이터 검색속도가 향상된다.

시스템 부하가 줄어들고 시스템의 전체 성능이 높아진다.


DB의 10%정도 공간이 요구된다.

인덱스를 생성하는 시간이 크게 요구된다.

DB의 변경작업(Insert, Update, Delete)이 잦으면 성능이 저하된다.


- Index의 종류

  Clustered Index

   사전처럼 데이터가 순차적으로 정렬되어 있다.

   테이블당 한 개만 생성이 가능하다.

   행 데이터를 인덱스로 지정한 열에 맞춰 자동으로 정렬한다.

   테이블 생성시 제약조건이 없다면 인덱스를 만들 수 없다.

   기본키를 생성시 기본키에 Clustered Index가 된다.


   Non-Clustered Index

   테이블당 여러개를 생성 할 수 있다.


 - 예제를 통해 확인하는 Index의 특징

기본키로 지정하면 기본키에 Clustered Index가 생성된다.

1
2
3
4
create table users
( id   varchar not null primary key,
  name varchar,
 .....
cs

     

* 기본키를 지정하면서 Non-Clustered Index를 생성할 수 있다.

  그 결과 Clustered Index의 여분이 있데 된다.

1
2
3
4
create table users
( id   varchar not null primary key nonclusterd,
  name varchar,
 .....
cs


 * unique로 지정한 행마다 Non-Clustered Index가 생성된다.

   (한 테이블에 여러개의 Non-Clustered Index가 생성될 수 있다.)

1
2
3
4
5
6
create table users
( id      varchar not null primary key,
  name    varchar unique,
  digit   varchar unique,
  address varchar
)
cs


  * 강제로 Clustered Index와 Non-Clustered Index를 지정할 수 있다.

    (기본키로 무조건  Clustered Index를 지정되는 것이 아니다. 단지 Default로 지정될 뿐이다.)

1
2
3
4
5
6
create table users
( id      varchar not null primary key nonclustered,
  name    varchar unique clustered,
  digit   varchar unique,
  address varchar
)


cs

    

        * Clustered Index로 지정된 열에 따라 테이블이 정렬된다.

1
2
3
4
5
6
7
8
9
10
11
create table users(
  id   varchar primary key,
  name varchar
);
 
insert into users('ccc''111');
insert into users('ddd''222');
insert into users('bbb''333');
insert into users('aaa''444');
 
select * from users;
cs

      결과

      id        name

      ----------------

      aaa      444

      bbb     333

      ccc      111

      ddd     222

      입력된 순서대로 보여질거라 생각했지만 Clustered Index에 의해 정렬되어 표현한다.

10. Join(1)

2개 이상의 테이블이 하나로 묶여 집합을 만들어내는것.


 - Inner Join

일반적인 Join

서로 관계가 맺어진 테이블에서 각자의 속성들을 추출하는것.

Join하는 조건에 일치한 테이블들이 결합된다.

select 뒤에는 어떤 테이블의 속성을 추출할 것인지 명시해야된다.

Join의 조건하에, 양쪽 테이블에 모두 존재하는것만 추출된다.

Join의 조건은 순차적으로 검색된다.


- Outter Join

Inner Join의 결과와 한쪽 테이블에만 존재하는 데이터도 추출하는 것.

Join 조건에 만족하지 아니한 행도 추출된다.

Left, Right의 경우 각 측에 있는 테이블의 데이터를 추출한다는 의미다.

Full Outter는 Left와 Right를 모두 수행한다는 의미다.


- Self Join

한 테이블에 같은 데이터들이 존재하되, 서로 다른 의미로 표현되는 경우 사용된다.


02. Oracle hr account table(2)

5. 부서별로 가장 적은 급여를 받고 있는 직원의 이름, 부서이름, 급여를 출력하시오. 이름은 last_name만 출력하며, 부서이름으로 오름차순 정렬하고, 부서가 같을 경우 이름을 기준으로 오름차순 정렬하세요.

1
2
3
4
5
6
7
8
select   e1.last_name 사원이름, d1.department_name 부서이름, j1.급여 급여
from     employees e1, departments d1, (select   department_id 부서아이디, min(salary) 급여 
                                        from     employees 
                                        group by department_id) j1
where    d1.department_id = e1.department_id and 
         j1.부서아이디 = e1.department_id and 
         j1.급여 = e1.salary
order by d1.department_name, e1.last_name
cs



6. 각 도시(City)에 있는 모든 부서 직원들의 평균급여를 조회하고자 한다. 평균급여가 가장 낮은 도시부터 도시명(City)과 평균연봉, 해당 도시의 직원 수를 출력하시오. 단, 도시에 근무하는 직원이 10명 이상인 곳은 제외하고 조회한다.

1
2
3
4
5
6
select   l1.city 도시이름, round(avg(e1.salary),0) 평균급여, count(*) 직원수
from     locations l1, employees e1, departments d1
where    e1.department_id = d1.department_id and d1.location_id=l1.location_id
group by l1.city
having   count(*)< 10
order by round(avg(e1.salary),0)
cs

7. 30번 부서에 근무하는 사원들의 직책(job_title)별 평균 급여가 2000 이상인 직책(job_title)과 그 직책의 평균 급여.

1
2
3
4
5
select   j1.job_title 직책, to_char(avg(e1.salary),'999,999') 평균급여
from     employees e1, jobs j1
where    e1.job_id = j1.job_id and e1.department_id=30
group by j1.job_title
having   avg(e1.salary)>2000
cs


8. 'Public Account' 의 직책(job_title)으로 과거에 근무한 적이 있는 모든 사원의 사번과 이름을 출력하세요. (현재 'Public  Account'의 직책(job_title)으로 근무하는 사원은 고려하지 않습니다.)

1
2
3
select e1.employee_id 사번, e1.first_name || ' ' || e1.last_name 이름
from   employees e1, jobs j1, job_history jh1
where  e1.employee_id = jh1.employee_id and jh1.job_id = j1.job_id and j1.job_title='Public Accountant'
cs


+ Recent posts

티스토리 툴바