Index (learning notes), index learning notes

Index (learning notes), index learning notes

The tablespace can be omitted.

B-Tree Index

Reverse Key Index

Function Index

Bitmap Index

Delete Index

 

B-Tree Index

Example 1,Create a table and use the PL/SQL data generator to import 0.1 million records

-- Create table depositor (actid INTEGER not null, identify INTEGER not null, lastname VARCHAR2 (10) not null, firstname VARCHAR2 (10) not null, address1 VARCHAR2 (200) not null, address2 VARCHAR2 (200), address3 VARCHAR2 (200), account NUMBER () not null, constraint PK_DEPOSITOR primary key (actid); -- Query SELECT * FROM depositor;

Use a data generator to import 0.1 million records

Execute query in explain Plan window

select identify  from depositor WHERE identify BETWEEN 10000 AND 30000;

273 of system resources consumed

Add an index for the identify column

 

CREATE INDEX deptor_index ON depositor(identify) TABLESPACE tbs_test;

 

Execute the query again in the explain Plan window.

Consumption has decreased significantly

Example 2: Create a composite index

-- No composite index query SELECT d. identify FROM depositor d WHERE d. identify> 5000 AND d. actid> 100500;

-- Create index dp_complex_index ON depositor (identify, actid) TABLESPACE tbs_test;

Return

 Reverse Key Index

Reverse key index,

The reverse key index is mainly used in large-scale cluster systems and is updated by multiple users at the same time.

Reverse key index syntax

 

Query when no index is available

Create reverse key index

-- Create index dpt_reverse_index ON depositor (identify) reverse tablespace tbs_test;

 Return

Function Index,

 

Run the following query:

-- Query all firsetnames and convert them to all lowercase select lower (d. firstname) FROM depositor d;

Create a lower Function Index

-- Create index dpt_lowerfirstname_index ON depositor (lower (firstname) TABLESPACE tbs_test;

Execute the preceding query again

Return

Bitmap index:

 

Execute statistics Query

-- Select count (*) FROM depositor d WHERE d. firstname = 'luke ';

Create a bitmap Index

-- Create bitmap index dpt_bitmap_index ON depositor (firstname) TABLESPACE tbs_test;

 

Return

Delete Index
Drop index name

Delete the previous index

-- Delete the index drop index deptor_index; drop index dp_complex_index;