오라클의 인덱스 유형에는 고유하지 않은 인덱스, 고유 인덱스, 비트맵 인덱스, 로컬 접두어 파티션 인덱스, 로컬 비접두어 파티션 인덱스, 글로벌 접두어 파티션 인덱스, 해시 파티션 인덱스 및 함수 기반 인덱스가 포함됩니다. 테이블에 데이터를 삽입한 후 인덱스를 생성해야 합니다. "create Unique index" 문을 사용하여 고유 인덱스를 생성할 수 있습니다.
이 튜토리얼의 운영 환경: Windows 7 시스템, Oracle 11g 버전, Dell G3 컴퓨터.
인덱스란 무엇인가요?
인덱스는 루트 노드, 가지 노드 및 리프 노드로 구성됩니다. 상위 레벨 인덱스 블록에는 하위 노드의 인덱스 데이터가 포함됩니다. -레벨 인덱스 블록, 리프 노드에는 인덱스 데이터가 포함되어 있으며 행 ID의 실제 위치를 결정합니다.
색인 설명
1) 색인은 책의 색인과 유사하게 데이터 검색 속도를 높이는 데 사용되는 데이터베이스 개체 중 하나입니다. 데이터베이스에서 색인을 생성하면 데이터베이스 프로그램이 결과를 쿼리할 때 읽어야 하는 데이터의 양을 줄일 수 있습니다. 이는 책에서 책 전체를 읽지 않고도 원하는 정보를 찾기 위해 색인을 사용할 수 있는 방법과 유사합니다.
2) 인덱스는 테이블에 구축된 선택적 개체입니다. 인덱스의 키는 기본 전체 테이블 스캔 검색 방법을 정렬된 인덱스 키 집합으로 대체하여 검색 효율성을 향상시키는 것입니다.
3) 인덱스는 논리적으로나 물리적으로, 관련 테이블 및 데이터와는 아무런 관련이 없습니다. 인덱스를 생성하거나 삭제할 때 기본 테이블에는 영향을 미치지 않습니다.
4) 인덱스가 설정되면 테이블에 대한 DML 작업(예: 삽입, 수정)을 수행할 때; 또는 삭제) 관련 작업 중에 Oracle은 자동으로 인덱스를 관리하며 인덱스 삭제는 테이블에 영향을 미치지 않습니다.
5) 인덱스는 테이블에 인덱스가 있는지 여부에 관계없이 사용자에게 투명합니다.
6) Oracle 생성 기본 키가 사용될 때 이 열에 인덱스가 자동으로 생성됩니다.
인덱스 사용 목적:
인덱스 사용 시기:
인덱스 유형
인덱스 관리 지침
import
도구를 사용하여 데이터 삽입 또는 로드 마지막으로 인덱싱이 더 효과적입니다. import
工具插入或装载数据后,建立索引比较有效;索引正确的表和列
为性能而安排索引列
dx_groupid_serv_id(groupid,serv_id)
,在where
条件中使用groupid
或groupid,serv_id
,查询将使用索引,若仅用到serv_id
올바른 테이블과 열을 인덱싱하세요.
자주 대규모 정렬 테이블에서 행의 40% 또는 정렬되지 않은 테이블에서 7%를 검색하는 경우 인덱스를 구축하는 것이 좋습니다.
값 범위(큰: B* 트리 인덱스, 작은: 비트맵 인덱스)
인덱싱에 적합하지 않은 열
성능을 위해 인덱스 열 배열
여러 필드를 함께 사용하여 레코드를 검색하는 경우가 많으며, 단일 인덱스보다 결합된 인덱스가 더 효율적입니다.
dx_groupid_serv_id(groupid,serv_id)
). where
조건에서 groupid
또는 groupid를 사용하세요. serv_id
, 쿼리는 인덱스를 사용합니다. serv_id
필드만 사용하면 인덱스가 유효하지 않습니다. 테이블에는 수백 개의 인덱스가 있을 수 있지만(이렇게 하시겠습니까?) 테이블에 자주 삽입하고 업데이트하는 경우 시스템 CPU에 인덱스가 많아지면 I/ O 부담이 클수록 🎜🎜🎜🎜 각 테이블에는 5개 이하의 인덱스가 있는 것이 좋습니다. 🎜🎜🎜🎜🎜더 이상 필요하지 않은 인덱스 삭제🎜🎜🎜🎜🎜잘못된 인덱스. 주로 B* 트리 인덱스 대신 함수 기반 인덱스 또는 비트맵 인덱스를 사용하기 때문입니다. 🎜🎜🎜🎜애플리케이션의 쿼리가 유효하지 않습니다. 인덱스 사용 🎜🎜🎜🎜인덱스를 재구축하기 전에 인덱스를 삭제해야 합니다. 인덱스를 재구축하기 위해 alter index...rebuild를 사용하는 경우에는 인덱스를 삭제할 필요가 없습니다. 🎜🎜🎜🎜🎜인덱스 데이터 블록 공간 사용량🎜🎜
병렬 인덱스 생성은 대규모 테이블에 사용할 수 있습니다. 인덱스를 병렬로 생성하는 경우 저장 매개변수는 각 쿼리 서버 프로세스에서 별도로 사용됩니다. 예:
initial
은 1M
이고 병렬 처리는 8
이며 인덱스 생성 중에 최소 8M
공간이 소비됩니다. initial
为1M
,并行度为8
,则创建索引期间至少要消耗8M
空间;考虑用nologging
创建索引
怎样建立最佳索引?
明确地创建索引
create index index_name on table_name(field_name) tablespace tablespace_name pctfree 5 initrans 2 maxtrans 255 storage ( minextents 1 maxextents 16382 pctincrease 0 );
创建基于函数的索引:
常用与UPPER、LOWER、TO_CHAR(date)
等函数分类上,例:
create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;
创建位图索引:
对基数较小,且基数相对稳定的列建立索引时,首先应该考虑位图索引,例:
create bitmap index idx_bitm on class (classno) tablespace tablespace_name;
明确地创建唯一索引
可以用create unique index
语句来创建唯一索引,例:
create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;
创建与约束相关的索引
可以用using index
字句,为与unique
和primary key
约束相关的字段创建索引,例如:
alter table table_name add constraint PK_primary_keyname primary key (field_name) using index tablespace tablespace_name;
如何创建局部分区索引
Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID) Pctfree 5 Tablespace TBS_AK01_IDX Storage ( MaxExtents 32768 PctIncrease 0 FreeLists 1 FreeList Groups 1 ) local /
如何创建范围分区的全局索引
基础表可以是全局表和分区表。
create index idx_start_date on tg_cdr01(start_date) global partition by range(start_date) (partition p01_idx vlaues less than (‘0106’) partition p01_idx vlaues less than (‘0111’) … partition p01_idx vlaues less than (‘0401’ )) /
重建现存的索引
重建现存的索引的当前时刻不会影响查询;
重建索引可以删除额外的数据块;
提高索引查询效率;
alter index idx_name rebuild nologging;
对于分区索引:
alter index idx_name rebuild partition partiton_name nologging;
要删除索引的原因
<br/> drop index idx_name; <br/> drop index idx_name drop partition partition_name; <br/>
建立索引的代价
基础表维护时,系统要同时维护索引,不合理的索引将严重影响系统资源,主要表现在CPU和I/O上;
插入、更新、删除数据产生大量db file sequential read
锁等待;
一个表中有几百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这主要可能是oracle
的索引限制造成的。
oracle
的索引有一些索引限制,在这些索引限制发生的情况下,即使已经加了索引,oracle
还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差。
扩展知识:常见的索引限制问题
1、使用不等于操作符(<>, !=)
下面这种情况,即使在列dept_id
有一个索引,查询语句仍然执行一次全表扫描
select * from dept where staff_num <> 1000;
但是开发中的确需要这样的查询,难道没有解决问题的办法了吗?
有!
通过把用 or 语法替代不等号进行查询,就可以使用索引,以避免全表扫描:上面的语句改成下面这样的,就可以使用索引了。
select * from dept shere staff_num < 1000 or dept_id > 1000;
2、使用 is null 或 is not null
使用 is null
或is nuo null
也会限制索引的使用,因为数据库并没有定义null
值。如果被索引的列中有很多null,就不会使用这个索引(除非索引是一个位图索引,关于位图索引,会在以后的blog文章里做详细解释)。在sql语句中使用null
会造成很多麻烦。
解决这个问题的办法就是:建表时把需要索引的列定义为非空(not null
)
3、使用函数
如果没有使用基于函数的索引,那么where
nologging
인덱스 생성🎜🎜🎜🎜nologging을 사용하면 대규모 테이블에 대한 인덱스를 생성할 때 리두 로그를 줄일 수 있습니다. 🎜🎜리두 로그 파일의 공간을 절약하고 🎜🎜인덱스 생성 시간을 단축합니다. 큰 인덱스를 병렬로 생성할 때. 🎜🎜🎜최고의 색인을 만드는 방법🎜🎜명시적으로 색인 만들기🎜select * from staff where trunc(birthdate) = '01-MAY-82';
UPPER, LOWER, TO_CHAR(date)
및 기타 함수와 함께 사용됩니다. 예: 🎜select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
select * from dept where dept_id = 900198;
create Unique index
문을 사용하여 고유 인덱스를 생성할 수 있습니다. 예: 🎜select * from dept where dept_id = '900198';
using index
절을 사용하여 고유
및 기본 키
와 관련된 필드에 대한 인덱스를 생성할 수 있습니다. 제약 조건은 다음과 같습니다. 🎜Column1 like ‘aaa%’ 是可以的 Column1 like ‘%aaa%’用不到
select count(*) from person_info where xb in (select xb_id from dic_sex); Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …); Select * from person_info where zjhm=3101….;--将会对person_info全表扫描 Select * from person_info where zjhm =‘3101…’;--才能用到索引
Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1
<br/> drop index idx_name; > drop index idx_name drop partition partition_name <br/>
🎜🎜🎜인덱싱 비용🎜🎜Basic 테이블 유지 관리 중에 시스템은 인덱스를 동시에 유지해야 합니다. 비합리적인 인덱스는 주로 CPU 및 I/O의 시스템 리소스에 심각한 영향을 미칩니다. 🎜🎜데이터 삽입, 업데이트 및 삭제는 많은 양의 db 파일을 생성합니다. 순차 읽기
잠금 대기 🎜🎜테이블에 수백만 개의 데이터가 있고 특정 필드에 인덱스가 추가되었지만 쿼리 성능이 향상되지 않은 것은 주로 인덱스 제한 때문일 수 있습니다. 오라클
. 🎜🎜oracle
의 인덱스에는 몇 가지 인덱스 제한이 있습니다. 이러한 인덱스 제한이 발생하면 인덱스가 추가되었더라도 oracle
는 여전히 전체 테이블 스캔 및 쿼리를 수행합니다. 인덱스를 추가하지 않는 것에 비해 성능이 향상되지는 않지만, 데이터베이스에 인덱스를 유지하는 시스템 오버헤드로 인해 성능이 저하될 수 있습니다. 🎜🎜확장된 지식: 일반적인 색인 제한 문제🎜🎜1 부등식 연산자(<>, !=)🎜🎜다음 상황에서는 dept_id
컬럼에 인덱스가 있어도 쿼리문은 여전히 전체 테이블 스캔을 수행합니다🎜Order byGroup byDistinctIn
is null
또는 is nuo null
을 사용하면 인덱스 사용도 제한됩니다. 데이터베이스가 null
값을 정의하지 않기 때문입니다. 인덱스된 열에 Null이 많으면 인덱스가 사용되지 않습니다(인덱스가 비트맵 인덱스인 경우는 제외). 자세한 내용은 향후 블로그 기사에서 설명합니다. SQL 문에서 null
을 사용하면 많은 문제가 발생합니다. 🎜🎜이 문제를 해결하는 방법은 테이블을 생성할 때 인덱스가 필요한 열을 null이 아닌 (null이 아님
)🎜🎜함수 사용으로 정의하는 것입니다. >🎜🎜함수 기반 인덱스를 사용하지 않는 경우 인덱스가 있는 열의 where
절에 함수를 사용하면 최적화 프로그램이 이러한 인덱스를 무시하게 됩니다. 다음 쿼리는 인덱스를 사용하지 않습니다: 🎜select * from staff where trunc(birthdate) = '01-MAY-82';
但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。
select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);
4、比较不匹配的数据类型
比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中,dept_id
是一个varchar2
型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。
select * from dept where dept_id = 900198;
这是因为oracle
会自动把where
子句转换成to_number(dept_id)=900198
,就是3所说的情况,这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引
select * from dept where dept_id = '900198';
5、使用like子句
使用like子句查询时,数据需要把所有的记录都遍历来进行判断,索引不能发挥作用,这种情况也要尽量避免。
Like
的字符串中第一个字符如果是‘%’
则用不到索引
Column1 like ‘aaa%’ 是可以的 Column1 like ‘%aaa%’用不到
6、使用IN
尽管In
写法要比exists
简单一些,exists
一般来说性能要比In要高的多
用In
还是用Exists
的时机
当in的集合比较小的时候,或者用Exists
无法用到选择性高的索引的时候,用In要好,否则就要用Exists
例:
select count(*) from person_info where xb in (select xb_id from dic_sex); Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …); Select * from person_info where zjhm=3101….;--将会对person_info全表扫描 Select * from person_info where zjhm =‘3101…’;--才能用到索引
假定TEST表的dt
字段是date
类型的并且对dt
建了索引。
如果要查‘20041010’
一天的数据.下面的方法用不到索引
Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;
而以下将会用到索引。
select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1
7、如果能不用到排序,则尽量避免排序。
用到排序的情况有
集合操作。Union ,minus ,intersect
等,注:union all
是不排序的。
Order byGroup byDistinctIn
有时候也会用到排序
确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行,有文章说,内存排序的速度是硬盘排序的1万倍。
在排序的字段上创建索引,让排序在内存中执行,加快排序速度。
8、在基于CBO的优化器(花费)下,表的统计数据过期。也可能导致不使用索引。
解决:执行表分析。获取表的最新信息。
9、获取的数据量过大,全部扫描效率更高
10、索引字段的值分散率太低,值太集中,如类型字段都是1,2, 状态类型Y-有效/N-无效。这类型的字段最好别建索引。
尽管在这些字段上建立了索引,但对全表数据区分度不大。最后还是会全表扫描。
推荐教程:《Oracle教程》
위 내용은 Oracle에는 어떤 인덱스가 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!