Oracle_ Advanced Features (2) index

1.oracle Optimizer
The goal of optimization is divided into 4 categories:
Choose (optional)
Rule (rules-based)
First rows (line one)
All rows (all rows)
Description: Describe the execution plan for SQL
Object owner: Objects Schema
Object Name: Objects name
Cost: Spent (in time)
Cardinality: cardinality, approximately equal to the number of rows
Bytes: space (access to storage space)

How to access 2.table
2.1 All-table scan (table access full)
A full table scan is the sequential access to each record in the table.
Oracle optimizes full-table scanning in a way that reads multiple data blocks (database block).
Example:
SELECT * from EMP;

2.2 Accessing the table via ROWID (table access by User/index rowID)
You can use ROWID-based access to improve the efficiency of your access tables.
The ROWID contains the physical location information for the records in the table.
Oracle employs an index to achieve the connection between data and the physical location (ROWID) where the data resides.
Usually the index provides a quick way to access rowid, so those queries based on indexed columns can get a performance boost.
Example:
Select rowid,emp.* from emp where rowid= ' aaar3saaeaaaacxaaa ';
SELECT * from EMP where empno=7788;

--Clear the database memory buffer
alter system flush Buffer_cache;

Index
1. Index definition The
index is an optional structure that is associated with a table and can sometimes improve data access speed.
by creating an index on one or more columns in a table
, it is often possible (quickly) to retrieve a small subset of rows from a randomly distributed table row. The
Index is one of many ways to reduce disk I/O.
If a table does not have an index, the database must perform a full table scan to find values.
Test:
Select * from All_objects;
CREATE TABLE Tab_all_objects as SELECT * from All_objects;
Select * from Tab_all_objects;
35s-->26s
Select * from tab_all_objects where object_name= ' STUDENT ';
0.531s-->0.015s
CREATE index idx_object_name on tab_all_objects (object_name);
Exercise:
Select owner,view_name,text_length,editioning_view,read_only from All_views;
CREATE TABLE Tab_all_views as select owner,view_name,text_length,editioning_view,read_only from All_views;
Select * from Tab_all_views;
0.515s-->0.498s
Select * from tab_all_views where view_name= ' view_emp ';
0.499s-->0.062s
CREATE index idx_view_name on tab_all_views (view_name);

SELECT * from emp,dept where emp.deptno=dept.deptno;
0.109s-->0.031s
CREATE INDEX Idx_emp_deptno on EMP (DEPTNO);
Typically, you might consider creating an index on a column in the following situations:
A column is often used as a query condition, and the query results return only a small subset of rows in the table. 5% >20%
Referential integrity constraints (which are foreign key columns) exist on the column or column set.

Second, the grammar
2.1 Creating an Index
Create [unique | bitmap] index index name on table name {([<expr>] <col> [ASC | desc] [, ...])
Example:
CREATE INDEX Idx_emp_deptno on EMP (DEPTNO); Normal index
Create unique index idx_emp_ename on EMP (ename); Unique index
CREATE INDEX idx_emp_job_sal on EMP (job,sal); Composite Index
CREATE INDEX idx_emp_sal on EMP (SAL*12); Function index

SELECT * from EMP where deptno=20;
SELECT * from emp where ename= ' FORD ';
SELECT * from emp where job= ' MANAGER ' and sal>=2500;
SELECT * FROM EMP where sal*12>=20000;

SELECT * from tab_all_objects where object_id=74975;
Create INDEX idx_object_id on tab_all_objects (object_id);

--select * from tab_all_objects where object_type= ' synonym ';
--create index Idx_object_type on tab_all_objects (object_type);

2.2 Modifying indexes
Alter index <ind> {enable | disable};
Alter index <ind> Rename to <new>;
Example:
Alter index idx_emp_sal disable;
Alter index IDX_EMP_DEPTNO disable;
Alter index idx_emp_sal rename to Idx_emp_sal1;
Alter index IDX_EMP_SAL1 rename to Idx_emp_sal;

2.3 Deleting an index
Drop INDEX <ind>;
Example:
--drop index idx_emp_sal;

2.4 Analysis Index
Analyze index <ind> validate structure online|offline;
Example:
Analyze index idx_emp_sal validate structure online;

2.5 Rebuilding the Index
Alter INDEX <ind> rebuild
Example:
Alter index IDX_EMP_SAL rebuild;

--Authorization
Connect sys/123 as SYSDBA;
Grant Create any index,drop any index,alter all index to Scott;

Third, index characteristics
An index is a pattern object that is logically and physically independent of its associated table object's data.
Therefore, you can delete or create an index without actually affecting the related table.
If you delete an index, the application will still work. However, data accessed through the index before access may become slower.
The presence or absence of an index does not require any change in the wording of any SQL statement.
An index is a quick access path to a single row of data. It only affects the speed of execution.
For a given data value that has been indexed, the index points directly to the location of the row that contains the value.
Having too many indexes on a table can degrade DML performance because the database must also update the index.
Primary keys and unique keys automatically generate indexes, but you need to manually create indexes on foreign keys.
Create INDEX Ind_emp_deptno on emp_test (DEPTNO);
SELECT * from emp,dept where emp.deptno=dept.deptno;

Four, composite index
A composite index, also known as a join index, is an index on multiple columns in a table.
The columns in the composite index should appear in the most meaningful order in the query that retrieves the data, but not necessarily adjacent in the table.
If the WHERE clause references all columns or leading columns in the composite index,
Composite indexes can speed up the retrieval of data for SELECT statements.
Therefore, the order of the columns used in the definition is important. Generally, the columns that are most frequently accessed are placed in front.

V. Unique and non-unique indexes
The index can be unique or non-unique.
A unique index guarantees that there are no rows with duplicate values on the table's key column or key column set.
In a unique index, there is a rowid for each data value. The data in the leaf block is sorted only by the key.
A non-unique index allows duplicate values in the indexed column or column set.
For non-unique indexes, the ROWID is included in the key and is sorted.
Therefore, non-unique indexes are sorted by index key and rowID (ascending).

Vi. type of index
Index types are divided into: B-Tree index, bitmap index, function-based index

6.1 B-Tree Index
This is the standard type of index. Ideal for primary keys and highly selective indexes.
A balanced tree, referred to as B-tree, is the most common type of database index.
A B-Tree index is a list of sorted values that are divided into multiple ranges.
By associating a key with a row or row range, the B-tree can provide excellent retrieval performance for multiple types of queries,
Includes exact match and range search.

Binary search algorithm is one of the more frequent algorithms used in ordered arrays.
When the binary lookup algorithm is not contacted,
The most common practice is to iterate over an array, comparing each element with an O (n) time.
The binary lookup algorithm, however, is better because it has a lookup time of O (LGN).
For example, array {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}, looking for element 6,
The binary lookup algorithm is executed in the order of:
1. Find the median, using the median and 6, if the median is greater than 6, look in the left-hand array of the median, or, if the median is smaller than 6, find in the right-hand array of the median;
2. Loop Step 1 until it is equal to the value you want to find and return, or the value you want to find is not found, then empty.

Select Index_type,count (1) from dba_indexes where 1=1 Group by Index_type Order by count (1) desc;
1NORMAL 3681
2LOB 901 BLOB, CLOB column generated, non-individual index (view plan_table$;)
3iot-top 158 Index The primary key index of the organization table (view sqllog$;)
4function-based NORMAL40 Function-based index (view dbfs$_mounts;)
5BITMAP 16-bit graph index
6CLUSTER 10
7function-based DOMAIN4
8DOMAIN 1

Create Bitmap index SH. Sales_prod_bix on SH. SALES (prod_id);

6.2-bit Graph index
In the bitmap index, index entries use bitmaps to point to multiple rows.
CREATE INDEX Idx_emp_deptno on EMP (DEPTNO);
Drop index Idx_emp_deptno;
Create bitmap index Idx_emp_deptno on EMP (DEPTNO);

6.3 Function-based indexes
This type of index includes columns that have been transformed by a function (such as the upper function) or that are included in an expression.
CREATE INDEX idx_emp_ename_lower on EMP (lower (ename));

Seven, Index Scan
In an index scan, the database uses the index columns specified by the statement to retrieve rows by traversing the index.
The database scans the index and uses n I/O to find the value it is looking for, where n is the height of the B-tree index.
This is the rationale behind database indexing.
If the SQL statement accesses only the columns that are indexed, the database simply reads the values directly from the index without reading the table.
If the statement also needs to access columns other than the indexed column, the database uses ROWID to find rows in the table.
Typically, in order to retrieve table data, the database reads the index block alternately, and then reads the corresponding table block.

7.1. Fully indexed scan index full scan
Select emp.* from emp,dept where Emp.deptno=dept.deptno;
In a full index scan, the database sequentially reads the entire index.
If the predicate (WHERE clause) in the SQL statement refers to an indexed column,
Or, in some cases, no predicates are specified, a full index scan may be used.
A full scan eliminates sorting because the data itself is ordered based on the index key.

7.2 Fast full index Scan index fast fully scan
Select View_name from Tab_all_views;
--all rows
A fast full index scan is a full index scan, and the database does not read the index block in a specific order.
The database accesses only the data in the index itself, without having to access the table.
A fast full index scan can replace a full table scan when the index contains all the columns required by the query, and at least one column in the index key has a NOT NULL constraint.

7.3 Index range Scanning index range scan
CREATE INDEX Idx_emp_deptno on EMP (DEPTNO);
SELECT * from EMP where deptno=10;
An indexed range scan is an ordered scan of an index that has the following characteristics:
One or more index leading columns are specified in the condition.
The condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value (True, false, or unknown).
An index key may correspond to 0, one, or more values.
Typically, the database uses an index range scan to access selective data.
Selectivity is the percentage of data selected by the query as the total number of rows, 0 means that there is no row, and 1 represents all rows.
Selectivity is related to one (or more) query predicates, such as where last_name like ' a% '.
A predicate with a value closer to 0 is more selective, whereas a predicate that is closer to 1 is less selective.

7.4 Unique index Scan index unique scan
SELECT * from EMP where empno=7788;

A unique index scan must have 0 or 1 rowid associated with an index key relative to the index range scan.
When a predicate uses the equality operator to reference all columns of a unique index key, the database performs a unique scan.
As soon as the first record is found, the unique index scan stops processing because it is not possible to have a second record that satisfies the condition.

7.5 Index Hop Scan index Skip scan
If there are a small number of different values in the leading key column of the composite index, and there are many different values in the non-leading key columns, it is useful to use a jump scan.
--Build a table
CREATE TABLE Stu
(
Sex VARCHAR2 (1) NOT NULL,
Sid Number (8)
);
--Build composite Index
Create index Idx_stu_sexandsid on Stu (SEX,SID);
--Interpolation data
Declare
Begin
For I in 1..1000 loop
Insert into Stu values (' M ', i);
End Loop;
For I in 1001..2000 loop
Insert into Stu values (' F ', i);
End Loop;
End
--Analysis Table
Analyze table Stu Compute statistics;
--Analyze SQL execution plan
SELECT * from Stu where sid=100;
An index hop scan uses a logical sub-index of a composite index.
The database "jumps" through a single index as if it were searching in multiple separate indexes.
When a leading column of a composite index is not specified in the query predicate, the database may choose an index hop scan.

Indexes are based on the order in which the specified database table columns are set up.
It provides a way to quickly access data, and can supervise the table's data so that the data in the column to which it is indexed is not duplicated.
Divided into single-column indexes and combined indexes.
The primary key, unique key system automatically creates an index, and the foreign key system does not automatically create an index.
A foreign key that does not create an index causes a full table scan of the child table when the parent-child table joins the query.

Pseudo-Column rowID
1. Definition:
Each row of data in a table in an Oracle database has a unique identifier, or ROWID,
Within Oracle, it is often used to access data.
The ROWID requires 10 bytes of storage space and is displayed with 18 characters.
This value indicates the physical location of the row in the Oracle database.
You can use ROWID in a query to indicate that the value is included in the query results.
Select rowid,emp.* from emp where rowid= ' aaar3saaeaaaacxaaa ';
73196

2. Storage
Saving a ROWID requires 10 bytes or 80 bit bits.
These 80 bits are:
1. Data object number indicating the number of the database object to which this row belongs, each data object when the database is established
are uniquely assigned a number, and this number is unique. The data object number occupies approximately 32 bits.
2. The corresponding file number indicates the number of the file in which the row resides, and each file label in the tablespace is unique.
The file number occupies a location of 10 bits.
3. Block number, indicating that the block number of the file where the row is being changed requires 22 bits.
4. The row number, which indicates that the row number in the line directory is 16 bits.
This adds up to 80 bits.

3. Display
Oracle's physical expansion ROWID has 18 bits, each with 64-bit encoding, denoted by a~z, A~z, 0~9, +,/64 characters.
A means that 0,b represents 1, ... Z indicates that 25,a indicates that 26,......z indicates that the 51,0 represents the 61,+ representation of the 52,......,9 that 62,/represents 63.
The 64-bit encoding indicates that the ROWID has 18 bits, wherein:
The data object number occupies 6 bits;
The file number occupies 3 bits;
Block number accounted for 6 bits;
The row number occupies 3 bits.
Select Log (2,64), log (2,64) *18 from dual;

4. For example:
Select Rowid,empno,ename from EMP;
Will get the result:
Aaar3saaeaaaacxaaa
Description
Aaar3s is the database object number, AAE is the file designator, Aaaacx is the block number, and the last three-bit AAA (SMITH) is the line number.

5. Verification
5.1 Verify line numbering
SMITH--AAA
ALLEN--AAB
The number of different names is incremented.
5.2 Verification Number File label
Select file_id as Fid,file_name from Dba_data_files where tablespace_name= ' USERS ';
FID file_name
---------- ---------------------------------------------
4 D:\APP\WANGXUWEI\ORADATA\ORCL\USERS01. Dbf
File_id=4, is rowid in the AAE.
5.3 Verifying database object numbers
SELECT * from dba_objects where object_name= ' EMP ';
Objectid
73196
73196 = Aaar3s???
Select ASCII (' R ')-ascii (' A ') from dual;
R=17=17x64x64

3=26+26+3=55
Select ASCII (' s ')-ascii (' a ') +26 from dual;
S=44
Aaar3s=17x64x64+55x64+44
Select 17*64*64+55*64+44 from dual;
73196

5.4 Validation block number
SELECT * from dba_extents where segment_name= ' EMP ';
extent_id =0
file_id =4
block_id =144
Bytes =65536
Blocks =8
144 = Aaaacx???
Select ASCII (' X ')-ascii (' A ') from dual;
23
aaaacx=2x64+23=
Select 2*64+23 from dual;
151
aaaacx=151<>144???

6.dbms_rowid Bag
Through the Dbms_rowid package, you can directly get the specific information contained in the ROWID:
Select Dbms_rowid.rowid_object (ROWID) object_id,
DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) file_id,
Dbms_rowid.rowid_block_number (ROWID) block_id,
Dbms_rowid.rowid_row_number (ROWID) Row_number,
rowid,emp.*
from EMP;

Through the Dbms_rowid package, you can also query the file where the table or record resides
Select File_id,file_name from Dba_data_files
where file_id in (select DISTINCT DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) from scott.emp);
file_id file_name
4 D:\APP\ORADATA\ORCL\USERS01. Dbf

Oracle_ Advanced Features (2) index