[Oracle]-[Index] [HINT] [execution plan]-index execution plan with HINT

[Oracle]-[index] [HINT] [execution plan]-Example of an index execution plan with HINT: create table t (id int); create index t_idx on t (id ); SQL> select/* + index (t t_idx) */count (*) from t; Execution Plan ------------------------------------------------------ Plan hash value: 4075463224 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | --------------------------------------- ---------------------------- | 0 | select statement | 1 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | table access full | T | 3 | 2 (0) | 00:00:01 | ------------------------------------------------------------------- Note ------dynamic sampling used for this statement the total number of records in the table is ignored, the index T we created does not specify the index Field T. Therefore, if the CBO chooses to COUNT the index, when the index field has a null value, the COUNT result will inevitably be inaccurate. SQL> select/* + index (t, t_idx) */count (id) from t; Execution Plan ------------------------------------------------------ Plan hash value: 4235589928 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | 2 | index full scan | T_IDX | 3 | 39 | 1 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement HINT is used here. The explanation is: Because we only COUNT the X field and the id field is the index field, this action is equivalent to the key value of all IDs on the COUNT index. The result is the same as COUNT for the id field on the table. I think this is not very accurate. For a unique index, count (*) = count (index field ). If it is not a non-unique index, the NULL value in the column is not stored in the index, so count (*)> = count (index field ). Create table TBL_SMALL (id number, NAME VARCHAR2 (5); SQL> create index t_s_idx on tbl_small (id); create table tbl_big as select rownum id, object_name name from dba_objects where rownum <1000; SQL> create index t_ B _idx on tbl_big (id); insert into tbl_big values ('', '') >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>> SQL> select * from tbl_small; Id name ---------- ----- 2 B 1 a SQL> select count (*) from tbl_small; COUNT (*) ---------- 3 SQL> select count (id) from tbl_small; COUNT (ID) ---------- 2 SQL> select count (*) from tbl_big; COUNT (*) ---------- 1000 SQL> select count (id) from tbl_big; COUNT (ID) ---------- 999 SQL> set autot trace exp >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> TBL_SMALL Table: SQL> select count (*) from tbl_small; Execution Plan ---------------------------------------------------------- Plan hash value: 1452584873 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | ------------------------------------------------------------------------ | 0 | select statement | 1 | 2 (0) | 00:00:01 | 1 | SORT AGGREG ATE | 1 | 2 | table access full | TBL_SMALL | 3 | 2 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement SQL> select count (id) from tbl_small; Execution Plan -------------------------------------------------------- Plan hash value: 1539159417 bytes ---------------------------------------------------------------- ------------ | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | 2 | index full scan | T_S_IDX | 3 | 39 | 1 (0) | 00:00:01 | ---------------------------------------------------------------------------- Note ------dynamic sampli Ng used for this statement SQL> select/* + index (tbl_small, t_s_idx) */count (*) from tbl_small; Execution Plan into Plan hash value: 1452584873 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | ---------------------------------------------------------------------- | 0 | SE Lect statement | 1 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | table access full | TBL_SMALL | 3 | 2 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement SQL> select/* + index (tbl_small, t_s_idx) */count (id) from tbl_small; Execution Plan Execution Pl An hash value: 1539159417 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ---------------------------------------------------------------------------- | 0 | select statement | 1 | 13 | 1 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | 2 | index full scan | T_S_IDX | 3 | 39 | 1 (0) | 00:00:01 | ------------- Notice Note ------dynamic sampling used for this statement >>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>> TBL_BIG table: SQL> select count (*) from tbl_big; Execution Plan ------------------------------------------------------------ Plan hash value: 475686685 ----------------------------------------- --------------------------- | Id | Operation | Name | Rows | Cost (% CPU) | Time | hour | 0 | select statement | 1 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | table access full | TBL_BIG | 1000 | 3 (0) | 00:00:01 | ---------------------------------------------------------------------- Note ------dynamic sampling used for t His statement SQL> select count (id) from tbl_big; Execution Plan hash value: 2252048431 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time -------------------------------------------------------------------------------- | 0 | select statement | 1 | 13 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | 2 | index fast full scan | T_ B _IDX | 1000 | 13000 | 3 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement SQL> select/* + index (tbl_big, t_ B _idx) */count (*) from tbl_big; Execution Plan ha Sh value: 475686685 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | percent | 0 | select statement | 1 | 3 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | table access full | TBL_BIG | 1000 | 3 (0) | 00:00:01 | ------------------------------------------------- --------------------- Note ------dynamic sampling used for this statement SQL> select/* + index (tbl_big, t_ B _idx) */count (id) from tbl_big; Execution Plan hash value: 1004523789 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | ----------------------- --------------------------------------------------- | 0 | select statement | 1 | 13 | 5 (0) | 00:00:01 | 1 | sort aggregate | 1 | 13 | 2 | index full scan | T_ B _IDX | 1000 | 13000 | 5 (0) | 00:00:01 | notice Note ------dynamic sampling used for this statement different points: the id field count in a small table, whether or not hint is used, is index full scan. For large tables, the id field count, without hint, is index fast full scan, and the id field count with hint is index full scan. (In this case, I don't feel like hint. CBO can select FFS, but it may be better. However, if hint is included, it is mandatory to use an unoptimal FS ).