Use Case Studies to optimize -- IndexFULLSCAN and IndexFASTFULLSCAN

Use Cases to learn how to optimize -- IndexFULLSCAN and IndexFASTFULLSCANIndexFULLSCAN and ndexFASTFULLSCAN: IndexFULLSCAN and IndexFASTFULLSCAN: applicable

Use Cases to learn how to optimize -- IndexFULLSCAN and IndexFASTFULLSCANIndexFULLSCAN and ndexFASTFULLSCAN: IndexFULLSCAN and IndexFASTFULLSCAN: applicable

Index fast full scan:
HINT Syntax: INDEX_FFS (table name index name)
Principle: Starting from the field header, read bitmap blocks, ROOT blocks, all BRANCH blocks, and LEAF blocks. The read sequence is determined by the physical storage location, and multiple reads are performed, read DB_FILE_MULTIBLOCK_READ_COUNT blocks each time. When querying the total number of records in a table, the index fast full scan based on the primary key is the most effective.

Fast Full Index Scans:
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the not null constraint. A fast full scan accesses the data in the index itself, without accessing the table. it cannot be used to eliminate a sort operation, because the data is not ordered by the index key. it reads the entire index using multiblock reads, unlike a full index scan, and can be parallreceived.

Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be encoded med against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

Full Table Scans:
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. during a full table scan, all blocks in the table that are under the high water mark are scanned. each row is examined to determine whether it satisfies the statement's WHERE clause.

When Oracle performs a full table scan, the blocks are read sequentially. because the blocks are adjacent, I/O calllarger than a single block can be used to speed up the process. the size of the read callrange from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. using multiblock reads means a full table scan can be saved med very efficiently. each block is read only once.

Case Analysis:

1. Create tables and Indexes

At 16:02:10 SYS @ prod> create table t as select * from dba_objects where 1 = 2; Table created.16: 05: 43 SYS @ prod> insert into t select * from dba_objects where object_id is not null; 73025 rows created.16: 06: 46 SYS @ prod> select count (*) from t; COUNT (*) ---------- 73025 16:06:56 SYS @ prod> commit; Commit complete.16: 13: 48 SYS @ prod> exec dbms_stats.gather_table_stats ('sys ', 't', cascade => true ); PL/SQL procedu Re successfully completed.16: 14: 33 SYS @ prod> set autotrace trace16: 15: 32 SYS @ prod> select object_id from t; 73025 rows selected. execution Plan hash value: 1601196873 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | -------------------------------------- ---------------------------------- | 0 | select statement | 73025 | 356K | 284 (1) | 00:00:04 | 1 | table access full | T | 73025 | 356K | 284 (1) | 00:00:04 | Statistics -------------------------------------------------------- 141 recursive cballs 0 db block gets 5857 consistent gets 1038 physical reads 0 redo size 1060958 Tes sent via SQL * Net to client 53963 bytes provisioned ed via SQL * Net from client 4870 SQL * Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 73025 rows processed: according to the preceding execution plan, a full table scan is performed. -- Since the column we want to query is object_id, theoretically we only need to read the index to return all data. Why is full table scan? -- This is determined by the NULL value and the index feature. That is, the null value is not stored in the B-tree index. Therefore, the not null constraint should be added for the object_id column of Table t. At 16:16:14 SYS @ prod> desc t; Name Null? Type reserved -------- invalid OWNER VARCHAR2 (30) OBJECT_NAME VARCHAR2 (128) SUBOBJECT_NAME VARCHAR2 (30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2 (19) created date updated date timestamp VARCHAR2 (19) STATUS VARCHAR2 (7) TEMPORARY VARCHAR2 (1) GENERATED VARCHAR2 (1) SECONDARY VARCHAR2 (1) Namespace number EDITION_NAME VARCHAR2 (30) Add the not null constraint on object_id 16:16:42 SYS @ prod> alter table t modify (object_id not null); Table altered. elapsed: 00:00:00. 3416: 16: 46 SYS @ prod> desc t Name Null? Type reserved -------- invalid OWNER VARCHAR2 (30) OBJECT_NAME VARCHAR2 (128) SUBOBJECT_NAME VARCHAR2 (30) OBJECT_ID not null number DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2 (19) created date updated date timestamp VARCHAR2 (19) STATUS VARCHAR2 (7) TEMPORARY VARCHAR2 (1) GENERATED VARCHAR2 (1) SECONDARY VARCHAR2 (1) namespace number EDITION_NAME VARCHAR2 (30)

2. Comparison of Index_FS and Index_FFS