-- 인덱스 조회
set line 200;
col table_name for a10;
col column_name for a10;
col index_name for a20;
select table_name, column_name, index_name
from user_ind_columns
where table_name = 'INX_TEST';
select table_name, index_name
from user_indexes
where table_name = 'INX_TEST';
---------
-- 모니터링 시작
alter index IDX_INXTEST_NO monitoring usage;
-- 모니터링 중단
alter index IDX_INXTEST_NO nomonitoring usage;
-- 인덱스 사용유무 확인하기
select index_name, used
from v$object_usage
where index_name = 'IDX_INXTEST_NO';
-- 인덱스 사용하기 예
select * from inx_test
where no>'0';
----------
-- 테이블 생성
create table inx_test
(no number);
-- 데이터 삽입
begin
for i in 1..10000 loop
insert into inx_test values (i);
end loop;
commit;
end;
/
-- 인덱스 생성
create index idx_inxtest_no on inx_test(no);
-- 인덱스 상태 조회
analyze index idx_inxtest_no validate structure;
select (del_lf_rows_len / lf_rows_len) * 100 balance
from index_stats
where name = 'IDX_INXTEST_NO';
-- 10000건 중 4000건 삭제 후 인덱스 상태 조회
delete from inx_test
where no between 1 and 4000;
select count(*) from inx_test;
select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats
where name = 'IDX_INXTEST_NO';
-- analyze 후 인덱스 상태 조회
analyze index idx_inxtest_no validate structure;
select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats
where name = 'IDX_INXTEST_NO';
-- rebuild
alter index idx_inxtest_no rebuild;
analyze index idx_inxtest_no validate structure;
select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats
where name = 'IDX_INXTEST_NO';
---------------------
-- invisible index
create index idx_emp_sal on emp(sal);
select table_name, index_name, visibility
from user_indexes
where table_name = 'EMP';
alter index idx_emp_sal invisible;
select table_name, index_name, visibility
from user_indexes
where table_name = 'EMP';
-- index 다시 사용 설정 1
alter index idx_emp_sal visible;
select table_name, index_name, visibility
from user_indexes
where table_name = 'EMP';
-- index 다시 사용 설정 2
select /*+ index (emp idx_emp_sal)*/ ename
from emp
where ename > '0';
---------------------
create table new_emp4
(no number,
name varchar2(10),
sal number
);
insert into new_emp4 values (1000, 'smith', 300);
insert into new_emp4 values (1001, 'allen', 250);
insert into new_emp4 values (1002, 'king', 430);
insert into new_emp4 values (1003, 'blake', 220);
insert into new_emp4 values (1004, 'james', 620);
insert into new_emp4 values (1005, 'miller', 810);
commit;
select * from new_emp4;
create index idx_newemp4_name
on new_emp4(name);
select name from new_emp4;
-- 인덱스로 정렬된 값 출력됨
select name from new_emp4
where name > '0';
-----------------
-- 최소값
select name from new_emp4
where name > '0'
and rownum = 1;
-- 최대값 max 함수 사용
select max(name)
from new_emp4;
-- hint 기능 사용한 max값 구하기
select /*+ index_desc(e idx_newemp4_name) */ name
from new_emp4 e
where name > '0'
and rownum = 1;
-- first_row(max/min)방법
select /*+ index_desc(e idx_newemp4_name) */ max(name)
from new_emp4 e
where name > '0';
---------------------
-- rowid
select rowid, empno, ename
from emp
where empno = 7902;
alter index IDX_INXTEST_NO visible;