Reference: https://eco.dameng.com
Guidelines for managing indexes
Index: provides a faster path to access table data
DM8 common types
| type | describe |
|---|---|
| Clustered index | Each ordinary table has and has only one clustered index |
| unique index | The index data is unique according to the index key |
| Functional index | Contains the precomputed value of the function / expression |
| Bitmap index | Creates a bitmap index on low cardinality columns |
| bitmap join indexes | Bitmap indexes for two or more table joins are mainly used in data warehouses |
| Full text index | Index created on the text column of the table |
Create an index after inserting data into a table
In general, it is more efficient to create indexes for tables after inserting or loading data. If this is not done, DM8 must change and maintain each index when inserting each row of data, affecting the insertion efficiency.
Correctly indexed tables and columns
Guidelines for creating indexes:
- If you need to retrieve a small number of the rows in a large table frequently, create an index for query key
- To improve the performance of multi table joins, you can create indexes for joined columns
- Primary keys and unique keys automatically have indexes, and indexes are also created on foreign keys in many cases
- Small tables do not require indexes
The following points can be considered when selecting index columns in a table
- The values in the column are relatively unique
- It has a wide range of values and is suitable for indexing
- CLOB and TEXT can only create full-TEXT indexes, BLOB cannot create any indexes
Scheduling index columns for performance
In the CREATE INDEX statement, the sorting of columns will affect the performance of the query. Usually, the most commonly used columns are placed first.
If there are multiple field combination positioning in the query, you should not create an index for each field separately, but create a combined index.
When two or more fields are equivalent queries, the relationship between the columns in the composite index is irrelevant; If there is a non equivalent query, in order to make effective use of the combined index, the equivalent field should be put in front of the index, because only one non equivalent field can be used in the query.
Controls the number of indexes in the table
A table can have any number of indexes, but the more indexes, the more expensive it is to modify the table data
When inserting or modifying rows, all indexes on the table need to be "updated"
When you change a column, all indexes that contain that column also need to be changed
Therefore, it is necessary to weigh the number of indexes between the retrieval table data and the update table
If the table is mainly used for reading, more indexes can be created; If the table is updated frequently, less indexes are created
Create index
stay employee Tabular ename Create a column named emp_ename Index, stored in users In tablespace CREATE INDEX emp_ename ON employee(ename) STORAGE( INITIAL 50, NEXT 50, ON users );
Create clustered index
You can re create a new clustered index after creating the table, which will sort the table data according to the new clustered index.
CREATE CLUSTER INDEX clu_emp_name ON employee(ename);
Creating a new clustered index will rebuild all indexes of the table and table (including secondary index and functional index), which is expensive. Therefore, it is best to determine the clustered index when creating the table.
Constraints for creating a clustered index:
slightly
Explicitly create unique indexes
A unique index ensures that there are no duplicate values on the index key (equivalent to adding a unique constraint on the column of the table)
create unique index unique_test20_c1 on test20(c1);
If you define a UNIQUE integrity constraint on a table column, DM8 automatically defines a UNIQUE index on that column.
Automatically create unique indexes related to constraints
DM8 implements UNIQUE KEY or PRIMARY KEY integrity constraints by (automatically) creating a unique index on a UNIQUE KEY or PRIMARY KEY.
When you add a primary key, a unique index is automatically created on the primary key CREATE TABLE tb1(c1 int); ALTER TABLE tb1 ADD CONSTRAINT pk_tb1_c1 PRIMARY KEY(c1);
Create function based indexes
For a functional index, the value on the index column is obtained through a function or expression, which can improve the query efficiency of the function or expression to be executed in the query column or where condition
It is equivalent to executing the function or expression in advance. When querying, it can be obtained directly from the function index without re calculation
Create test table
create table tb_emp(
name varchar(20),
salary int
);
Create stored procedure, insert 100 w Row data (randomly generated), name 1-5 Characters, salary is 1 W within
create or replace procedure pro_insert_emp() as
begin
for i in 1..1000000 loop
insert into tb_emp values(
DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
TRUNC(DBMS_RANDOM.VALUE(0,10000))
);
commit;
end loop;
end;
/
Call stored procedure
call pro_insert_emp();
Check data volume
select count(*) from tb_emp;
Create a normal index or a full table scan( CSCN2) create index idx_emp_sal on tb_emp(salary); explain select name,salary*3 from tb_emp where salary*3 > 20000; 1 #NSET2: [126, 50000, 60] 2 #PRJT2: [126, 50000, 60]; exp_num(3), is_atom(FALSE) 3 #SLCT2: [126, 50000, 60]; TB_EMP.SALARY*3 > 20000 4 #CSCN2: [126, 1000000, 60]; INDEX33555647(TB_EMP)
Create a functional index and turn it into a secondary index scan( SSEK2)，That is, scan the index first, and then use the primary key, clustered index rowid Wait for information to scan the table create index idx_emp_3sal on tb_emp(salary*3); explain select name,salary*3 from tb_emp where salary*3 > 20000; 1 #NSET2: [50, 50000, 60] 2 #PRJT2: [50, 50000, 60]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [50, 50000, 60]; IDX_EMP_3SAL(TB_EMP) 4 #SSEK2: [50, 50000, 60]; scan_type(ASC), IDX_EMP_3SAL(TB_EMP), scan_range(20000,max]
In some cases, the function index cannot be created. Please check for details
https://eco.dameng.com/docs/zh-cn/pm/manage-index.html
create bitmap index
Bitmap indexes are mainly created for columns with a large number of the same values (that is, the cardinality of columns is low, such as gender and grade)
Bitmap indexes are widely used in data warehouses. Creating bitmap indexes for low cardinality (few different values) columns can effectively improve the query efficiency based on the column, AND the efficiency is more obvious when the where clause of the query statement is executed with AND and AND OR predicates.
Add gender column to table tb_emp to compare the difference between bitmap index and ordinary index
Create test table and add gender column (i.e. low cardinality column)
create table tb_emp(
name varchar(20),
salary int,
sex smallint
);
Randomly generate 100 w A stored procedure of records, with the numbers 0 and 1 representing gender
create or replace procedure pro_insert_emp() as
begin
for i in 1..1000000 loop
insert into tb_emp values(
DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
TRUNC(DBMS_RANDOM.VALUE(0,10000)),
TRUNC(DBMS_RANDOM.VALUE(0,2))
);
commit;
end loop;
end;
/
Call stored procedure
call pro_insert_emp();
Query users with gender 1 and compare the difference between ordinary index and bitmap index explain select name,salary from tb_emp where sex=1; 1 #NSET2: [127, 25000, 62] 2 #PRJT2: [127, 25000, 62]; exp_num(3), is_atom(FALSE) 3 #SLCT2: [127, 25000, 62]; TB_EMP.SEX = 1 4 #CSCN2: [127, 1000000, 62]; INDEX33555657(TB_EMP) Create a general index on the gender column idx_emp_sex_normal create index idx_emp_sex_normal on tb_emp(sex); explain select name,salary from tb_emp where sex=1; 1 #NSET2: [25, 25000, 62] 2 #PRJT2: [25, 25000, 62]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [25, 25000, 62]; IDX_EMP_SEX_NORMAL(TB_EMP) 4 #SSEK2: [25, 25000, 62]; scan_type(ASC), IDX_EMP_SEX_NORMAL(TB_EMP), scan_range[1,1] Creates a bitmap index on the gender column idx_emp_sex_bitmap drop index idx_emp_sex_normal; create bitmap index idx_emp_sex_bitmap on tb_emp(sex); explain select name,salary from tb_emp where sex=1; 1 #NSET2: [1, 25000, 62] 2 #PRJT2: [1, 25000, 62]; exp_num(3), is_atom(FALSE) 3 #BLKUP2: [1, 25000, 62]; INDEX33555657(TB_EMP) 4 #BMCVT: [1, 25000, 62] 5 #BMSEK: [1, 25000, 62]; scan_type(ASC), INDEX33555660(BM$_33555659), scan_range[(1,null2),(1,max))
From the triples [127, 25000, 62], [25, 25000, 62], [1, 25000, 62], it can be seen that the cost of bitmap index is the lowest (1)
Creating bitmap indexes is also constrained. Please refer to
https://eco.dameng.com/docs/zh-cn/pm/manage-index.html
Rebuild index
The addition, deletion and modification of the table will fragment the table and index (that is, the data is not compact and the number of pages becomes more), thus affecting the access speed.
The reconstructed index can be used to reorganize the indexed data, make the data more compact and release the unnecessary space, so as to improve the access efficiency and space efficiency.
Create a stored procedure to tb_emp Table insert n Row data
create or replace procedure pro_insert_emp(loop_times in int) as
begin
for i in 1..loop_times loop
insert into tb_emp values(
DBMS_RANDOM.STRING('1',TRUNC(DBMS_RANDOM.VALUE(1,5))),
TRUNC(DBMS_RANDOM.VALUE(0,10000)),
TRUNC(DBMS_RANDOM.VALUE(0,2))
);
commit;
end loop;
end;
/
Delete test table
drop table tb_emp;
Rebuild test table
create table tb_emp(
name varchar(20),
salary int,
sex smallint
Insert 10 w Row record
pro_insert_emp(100000);
Create index
create bitmap index idx_emp_sex_bitmap on tb_emp(sex);
);
View space usage of tables and indexes
View first insertion 10 w Size of records
SQL> SELECT TABLE_USED_SPACE('SYSDBA', 'TB_EMP');
LINEID TABLE_USED_SPACE('SYSDBA','TB_EMP')
---------- -----------------------------------
1 192
SQL> SELECT TABLE_USED_PAGES('SYSDBA', 'TB_EMP');
LINEID TABLE_USED_PAGES('SYSDBA','TB_EMP')
---------- -----------------------------------
1 105
SQL> select id,name from sysobjects where name='IDX_EMP_SEX_BITMAP';
LINEID ID NAME
---------- ----------- ------------------
1 33555662 IDX_EMP_SEX_BITMAP
SQL> select index_used_space(33555662);
LINEID INDEX_USED_SPACE(33555662)
---------- --------------------------
1 96
SQL> select index_used_pages(33555662);
LINEID INDEX_USED_PAGES(33555662)
---------- --------------------------
1 4
Simulate a large number of delete and insert operations to see the impact on the index space (repeat the operation several times) delete tb_emp where rownum<=10000; commit; pro_insert_emp(10000);
Check that after a large number of delete and insert operations are completed, the data volume is still 10 W that 's ok select count(*) from tb_emp; Note that the number of pages used in the index here is 1 more (from 4 to 5). Why? The amount of data stored in the index remains unchanged (10) w Rows), the value stored in each row remains unchanged (either 0 or 1), and the pages used should remain unchanged (still 4 pages) The extra pages should be the disk fragments caused by a large number of insertion, deletion and modification operations SQL> select index_used_pages(33555662); LINEID INDEX_USED_PAGES(33555662) ---------- -------------------------- 1 5
Too many pages will affect the efficiency of index scanning. Therefore, it is necessary to rebuild the index to eliminate disk fragments and reduce the number of pages used by the index
Rebuild index IDX_EMP_SEX_BITMAP，The number of pages changed from 5 to 4
SQL> SP_REBUILD_INDEX('SYSDBA', 33555662);
SQL> select index_used_pages(33555662);
LINEID INDEX_USED_PAGES(33555662)
---------- --------------------------
1 4
Delete index
Generally, the index is deleted in this way
DROP INDEX IF EXISTS IDX_EMP_SEX_BITMAP;
However, indexes related to enabled UNIQUE KEY or PRIMARY KEY constraints cannot be deleted directly. To delete a constraint related index, you must deactivate or delete the constraint itself. As shown in the following statement, delete the PK constraint_ emp_ Name, and delete its corresponding index.
To delete an index related to a UNIQUE KEY or PRIMARY KEY, deactivate or delete the constraint itself
For example, the following statement deletes the PK constraint_ emp_ Name, the system will automatically delete the corresponding index
new table emp，Primary key is pk_emp_name create table emp( name varchar(20), constraint pk_emp_name primary key (name) ); The system automatically creates an index in the primary key column INDEX33555665 SQL> SELECT TABLE_OWNER schema_name,TABLE_NAME ,COLUMN_NAME ,INDEX_NAME from DBA_IND_COLUMNS WHERE TABLE_NAME='EMP'; LINEID SCHEMA_NAME TABLE_NAME COLUMN_NAME INDEX_NAME ---------- ----------- ---------- ----------- ------------- 1 SYSDBA EMP NAME INDEX33555665 Deleting the constraint related index directly will result in an error SQL> drop index if exists INDEX33555665; drop index if exists INDEX33555665; [-5540]:Error in line: 1 No drop index privilege of table [EMP]. used time: 1.137(ms). Execute id is 0. If the constraint corresponding to the index is deleted, the index will be automatically deleted SQL> alter table "SYSDBA"."EMP" drop constraint "PK_EMP_NAME"; SQL> SELECT TABLE_OWNER schema_name,TABLE_NAME ,COLUMN_NAME ,INDEX_NAME from DBA_IND_COLUMNS WHERE TABLE_NAME='EMP'; no rows
Deleting a table automatically deletes all indexes associated with it
SQL> create table tb22(c1 int); SQL> create index idx_tb22_c1 on tb22(c1); SQL> select id,name from sysobjects where name='IDX_TB22_C1'; LINEID ID NAME ---------- ----------- ----------- 1 33555668 IDX_TB22_C1 SQL> drop table tb22; SQL> select id,name from sysobjects where name='IDX_TB22_C1'; no rows
View index information
View index ID
SQL> select id,name from sysobjects where name='IDX_TB22_C1'; LINEID ID NAME ---------- ----------- ----------- 1 33555670 IDX_TB22_C1
View the index definition. The first parameter is the index ID, and the second parameter can be 0 or 1 (for details, select 1)
SQL> select INDEXDEF(33555670, 1);
LINEID INDEXDEF(33555670,1)
---------- -----------------------------------------------------------------------------------------
1 CREATE INDEX "IDX_TB22_C1" ON "SYSDBA"."TB22"("C1" ASC) STORAGE(ON "MAIN", CLUSTERBTR) ;
Damon cloud adaptation Center:
https://eco.dameng.com