Basaaja ( 바사아자 닷컴 )

SQL에서 UPDATE가 의미 하는것은 실제로 데이터를 업데이트 하는것이 아니라
기존의 데이터를 지우고 해당 내용을 변경하여 다시 넣는다는것으로 알고 있습니다
=> 경우에 따라서 in-place update가 일어나기도 하고 기존의 데이터를 지우고 다시 입력하기도 합니다.
   in-place update의 경우에는 date page자체만을 update하므로 인덱스에는 전혀 영향을 주지 않습니다.

손님이 질의하신 경우에는 in-place update가 발생하므로 기존데이터를 지우고 다시 입력하는것이 아니라
그냥 name 컬럼 값만을 update 합니다.
그러나, 이경우에도 만약 "으하하" 라는 자료가 "음하하" 로만 변경하는것이 아니고
"음하하하하하" 등과 같이 컬럼길이가 기존길이보다 더 커진다면 이때는 in-place가 아니라
기존 데이터를 지우고 다시입력하는 방식으로 처리되겠지요.

그러나, update를 하게되면 순간적이나마 베타적잠금(Exclusive-Lock)이 발생을 합니다.
동시에 다른 곳에서 select를 한다면 lock으로 인하여 잠시 대기시간이 생기게 됩니다.

그래서, 가급적이면 아주 빈번하게 사용되는 마스트성 테이블의 경우에는
insert위주로 설계 해주시는것이 좋습니다.
update가 빈번하게 발생을한다면 그만큼의 lock이 발생하고 그로인해서 시스템이
느려지게 되겠지요.

저는 update용 테이블을 분리하실것을 권장합니다.


> 손님 님이 쓰신  글
> ----------------------------------------------------------
> OS : Microsoft Windows 2000 Server / Advanced Server
> SQL : Microsoft SQL Server 2000 Standard / Enterprise Edition
>
> 안녕하세요 .
>
> 인덱스에 대해서 몇가지 궁금한 점이 있어서 글을 올립니다.
>
> SQL에서 UPDATE가 의미 하는것은 실제로 데이터를 업데이트 하는것이 아니라
>
> 기존의 데이터를 지우고 해당 내용을 변경하여 다시 넣는다는것으로 알고 있습니다
>
> 궁금한것은요 ..
>
> CREATE TABLE test_table (
> idx INT PRIMARY KEY Not Null IDENTITY(1,1),
> name varchar(200) Not Null,
> test int Not Null
> )
>
> 이런 테이블을 만들고 마지막의 test 필드에 인덱스를 걸었다고 가정을 해봅시다.
>
> idx     name     test
> 1       메롱       2
> 2       으하하    4
> 3       어쩌구    5
>
> 라는 내용이 해당 테이블에 들어갔을 경우에요.
>
> idx에는 필드가 IDENTITY로 만들었으니 인덱스가 자동으로 생성되있잖아요 ?
>
> (맞는거죠 ㅡㅡ?) 그러면 여기서 제가 idx 가 2번인 "으하하" name 을 "음하하"로 UPDATE 했다면
>
> 여기에 걸려 있는 idx,test 인덱스가 재구성이 되는것이 맞는것인가요?
>
> 재구성이 된다는것은 인덱스를 다시 작성해야한다는것니깐 시간이 오래걸릴거라고 생각이 드는데 ..
>
> 아니면 인덱스가 b-tree 방식을 이용해서 하기땜시 인덱스와 관련 없는 값은 그냥 업데이트가
>
> 되는것인가요 ..
>
> 즉 "으하하" 라는 자료가 "음하하" 로만 변경이 되어서 기존에 구성되어 있는 인덱스에는 아무런
>
> 영향을 안주게 되는것인가요.
>
> 제가 적용하려는 부분은
>
> 쇼핑몰에서 상품 테이블안에 조회수, 판매수를 넣어야 하나 말아야 하나 그거 때문에 고민이거든요 ㅡ.ㅡ ..
>
> 상품 테이블안에는 인덱스가 많이 걸려 있는데... 조회수나 판매수를 넣으면 업데이트가 너무 자주 일어 나게되어서
>
> 따로 테이블을 만들어서 하는게 좋은건지 아니면 그냥 상품 테이블에 넣으면 될련지 ㅡ.ㅡ
>
> 햇갈려서 이렇게 문의 드립니다. 고수님들의 좋은 답변 바랍니다 .^^
>

/usr/local/mysql/bin/mysqldump -u USER -pPASS DB > /home/USER/dump/dump-2003-02-15.sql

http://www.microsoft.com/korea/sql/techinfo/productdoc/2000/books.asp

INSERT, SELECT INTO 또는 대량 복사 문이 완료되면 @@IDENTITY에는 명령문에 의해 생성된 마지막 ID 값이 들어 있습니다. 명령문이 ID 열이 있는 테이블에 영향을 주지 않은 경우에 @@IDENTITY는 Null 값을 반환합니다. 여러 행이 삽입되어 여러 ID 값이 생성되면 @@IDENTITY는 마지막으로 생성된 값을 반환합니다. 명령문이 ID 값을 생성하는 삽입 작업을 수행하는 트리거를 하나 이상 시작하는 경우 명령문 바로 다음에 @@IDENTITY를 호출하면 트리거로 생성된 마지막 ID 값이 반환됩니다. @@IDENTITY 값은 INSERT나 SELECT INTO 문 또는 대량 복사가 실패하거나 트랜잭션이 롤백하면 이전 설정으로 되돌아가지 않습니다.

@@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT는 테이블의 IDENTITY 열에 삽입된 마지막 값을 반환한다는 점에서 서로 비슷한 함수입니다.

@@IDENTITY와 SCOPE_IDENTITY는 현재 세션의 테이블에서 생성된 마지막 ID 값을 반환합니다. 그러나, SCOPE_IDENTITY는 현재 범위 내에서만 값을 반환합니다. @@IDENTITY는 특정 범위로 제한되지 않습니다.

IDENT_CURRENT는 범위와 세션으로 제한되지 않고, 지정된 테이블로 제한됩니다. IDENT_CURRENT는 임의의 세션과 범위에 있는 특정 테이블에 생성된 ID 값을 반환합니다. 자세한 내용은 IDENT_CURRENT를 참조하십시오.

#!/usr/local/php/bin/php -q
<?
$dbcon = mysql_connect(host,user,password) or die("DB Connection Failed!");
$status = mysql_list_dbs($dbcon);

if( !$status )
{
    echo "Can't Get DB Lists!n";
    exit;
}

$no=0;

while( $no<mysql_num_rows($status) )
{

    $dbname=@mysql_tablename($status,$no);

    if( !$dbname )
    {
        echo "Cat't Get DB Namen";
        exit;
    }

    echo "$dbnamen";

    $tano = mysql_list_tables($dbname,$dbcon);

    $temp = 0;

    $tot = mysql_num_rows($tano);

    if( !$tot )
    {
        echo "Can't Get Number of Tablesn";
        exit;
    }

    while( $temp<$tot )
    {
    
        $tablename = mysql_tablename($tano,$temp);

        if( !$tablename )
        {
            echo "Can't Get Table Namen";
            exit;
        }

        $query = 'optimize table '.$tablename;

        $aft = mysql_query($query);
        if( !$aft )
        {
            echo "Optimizing failed for $tablename n";
        }else{
            echo "  --->  $tablename Optimized! n";
        }

        $temp++;
    }

    $no++;
}

mysql_free_result($status);
mysql_close();
?>

테이블에는 중복 행이나 고유하지 않은 기본 키가 없어야 한다. 중복 PK는 엔티티 무결성을 위반하는 것이며 관계형 시스템에서는 허용되지 않는다. SQL Server에는 엔티티 무결성을 유지하기 위한 인덱스, UNIQUE 제약 조건, PRIMARY KEY 제약 조건, 트리거 등의 다양한 메커니즘이 포함되어 있다.

그럼에도 불구하고 중복된 기본 키가 발생하는 예외적 경우가 있다. 이런 경우에는 중복된 기본 키를 제거해야 한다. 중복 PK가 SQL Server 외부의 비관계형 데이터에 존재하고 PK 고유성이 항상 유지되도록 설정되지 않은 상태에서 데이터를 들여올 경우가 여기에 해당한다. 각 테이블에서의 엔티티 무결성이 항상 유지되도록 설정하지 않는 등의 데이터베이스 디자인 오류로 인해서도 중복된 기본 키가 발생한다.

대개 고유 인덱스를 만들려고 할 때 중복 PK가 발견되는데, 이런 경우에는 다음 메시지와 함께 작업이 중단된다. 이 메시지는 다음과 같다.

Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.

테이블에서 중복된 기본 키를 찾고 관련 중복행을 제거하는 방법에 대해서 알아보자. 그러나 무엇 보다도 중요한 것은 중복을 발생시킨 과정을 면밀히 검사하여 재발을 방지하는 것이다.

여기서는 중복 PK 값이 있는 다음 테이블을 예로 사용한다. 이 테이블에서 기본 키는 두 열(col1, col2)이다. 두 행에 중복 PK가 있기 때문에 고유 인덱스나 PRIMARY KEY 제약 조건을 만들 수 없다. 다음은 중복된 기본 키를 확인하고 제거하는 절차이다.

create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')

첫 단계는 중복된 기본 키 값이 있는 행을 확인하는 것이다.

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

이는 테이블에서 확인된 각 중복 PK 값의 집합에 대해 행을 하나씩 반환한다. 이 결과의 마지막 열은 특정 PK 값에 대한 중복 수이다.

1 1 2

중복 PK 값의 집합이 많지 않은 경우 가장 좋은 방법은 이들 집합을 수동으로 하나씩 삭제하는 것이다. 예를 들면 다음과 같다.

set rowcount 1
delete from t1
where col1=1 and col2=1

rowcount 값은 주어진 키 값의 중복 수에서 1을 뺀 값(n-1)이어야 한다. 이 예에서는 2개의 중복이 있으므로 rowcount가 1로 설정된다. col1/col2 값은 위의 GROUP BY 쿼리 결과에서 가져온 것이다. GROUP BY 쿼리가 여러 행을 반환하는 경우 "set rowcount" 쿼리를 각 행에 대해 한 번씩 실행해야 한다. 이를 실행할 때마다 rowcount를 특정 PK 값의 중복 수에서 1을 뺀 값(n-1)으로 설정한다.

행을 삭제하기 전에 행 전체가 중복인지 확인해야 한다. 가능성이 희박하기는 하지만 PK 값이 중복되고 행 전체는 중복되지 않을 수 있다. 이것의 예로 기본 키가 사원 번호인 테이블에 번호는 같지만 각각 고유한 속성을 갖는 두 명의 다른 사람(또는 행)이 있는 경우를 들 수 있다. 이러한 경우 중복 키로 인해 행에 유효한 고유 데이터가 온 것일 수도 있다. 이 데이터는 나중에 조사 및 조정할 수 있도록 삭제 전에 복사해 놓고 보존해야 한다.

테이블에 서로 다른 중복 PK 값의 집합이 많은 경우 이들 집합을 하나씩 제거하는 것은 너무 시간 낭비일 수 있다. 이러한 경우 다음 절차를 사용할 수 있다.

먼저, 위의 GROUP BY 쿼리를 실행하여 중복 PK 값의 집합 수와 각 집합의 중복 수를 파악한다.

중복 키 값을 선택하여 보관 테이블에 넣다. 예를 들면 다음과 같다.

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

중복 행을 선택하여 보관 테이블에 넣고 처리 중인 중복을 제거한다. 예를 들면 다음과 같다.

SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

이 시점에서 holddups 테이블에 고유한 PK가 있어야 하지만 위의 SSN 예에서와 같이 t1에 중복 PK와 고유한 행이 동시에 있는 경우에는 이 테이블의 PK가 고유하지 않게 된다. holddups의 각 키가 고유한지, 중복 키와 고유 행이 동시에 존재하지 않는지 확인한다. 중복 키와 고유 행이 같이 존재하는 경우에는 일단 작업을 중단하고 주어진 중복 키 값을 보관할 행을 조정해야 한다. 예를 들어, 아래 쿼리는

SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

각 행에 대해 1의 개수를 반환해야 한다. 쿼리가 각 행에 대해 1의 개수를 반환하면 아래의 5단계로 간다. 그렇지 않은 경우에는 중복 키와 고유 행이 같이 존재하는 것이므로 저장할 행을 결정해야 한다. 이를 위해 대개 행을 삭제하거나 이 행에 대한 고유 키 값을 새로 만든다. holddups 테이블의 이러한 각 중복 PK에 대해 이들 절차 중 적절한 방법을 사용한다.

원본 테이블에서 중복 행을 삭제한다. 예를 들면 다음과 같다.

DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

고유 행을 다시 원래 테이블에 넣다. 예를 들면 다음과 같다.

INSERT t1 SELECT * FROM holddups

이 프로시저는 테이블명, 컬럼명, n번째 숫자, 리턴파라미터 등을 입력하면 해당 테이블의 컬럼에서 n번째로 큰 값을 리턴하는 프로시저이다.
예를 들어 'products', 'UnitPrice' , 13 , @res 라고 입력하면 13번째로 큰 값을 리턴하고 찾지 못하면 에러를 리턴한다.

사용방법
declare @res decimal (10,4)
exec max_nth_value 'products','unitprice',13,@res OUTPUT
print @res

create proc max_nth_value (@tablename varchar(50),
                           @column varchar(50),
                           @n int,
                           @res decimal (10,4) OUTPUT)
AS
set nocount on
declare @sqlStatment varchar(200)
set @sqlStatment = 'select a.' + @column + ' from ' + @tableName + ' a ' +
                   'where ' + convert (varchar(10),@n) +
                   '=(select count(distinct ' + @column + ')' +
                   ' from ' + @tableName + ' b ' +
                   ' where ' + 'a.'+ @column + ' <= ' + 'b.' + @column + ')'
create table #tres (x decimal (10,4))
insert into #tres exec (@sqlStatment)
if @@rowcount = 0 print 'No value found!' else select @res = x from #tres
set nocount off
go

Ex>

ADD table Filed int Default ('1')

select * into outfile '/경로/파일.sql' from [DB_Backup]
'/경로/파일.sql -> 만들파일

SELECT 결과를 그대로 테이블로 저장할 수 있다. SQL Server와 Oracle을 구분해서 이해하자.

SQL Server에서는

SELECT 명령으로 실행한다. FROM 구문 다음에 "INTO" 구문을 이용하여 SELECT 결과를 지정된 테이블로 저장한다.
단 이 작업을 진행하기 위해서는 bulkcopy 옵션이 유효로 되어 있어야만 가능하다.
bulkcopy 옵션을 체크하려면 데이터베이스-등록정보를 클릭해 bulkcopy 부분을 체크하면 된다. 하지만 임시테이블에 저장하는 것은 이 옵션이 체크되어 있지 않더라도 가능하다.

SELECT * INTO test FROM testtemp

Oracle에서는

CREATE TABLE 명령으로 구현이 가능하다. 테이블을 생성할 때에는 그 기본이 되는 SELECT 명령을 지정한다. "UNRECOVERABLE" 옵션을 붙이면 로그를 생성하지 않아 빠른 속도로 생성할 수 있다.

CREATE TABLE test [UNRECOVERABLE] AS SELECT * FROM testtemp