6-5 Performance Management - Administer and tune schema objects to support various access methods

/* When querying data from the database usually one of three types of access methods is performed: 1) Full Table Scan (FTS) 2) ROWID access 3) Index lookup. */

/* When a table has an index and the indexed column is used, optimizer decides to perform an index lookup by looking up key values in the index and returns ROWIDs. Each ROWID uniquely identifies each row in the data block. */

 

 

<Full table scans>

/* When the FTS is performed, all blocks of the table up to the high water mark are placed in the buffer cache. FTS is performed if the query requires most of the blocks, or the table is small, or when it can’t use indexes or indexes are unavailable. */

CREATE TABLE tbl_exp_test AS SELECT * FROM dba_objects;
SELECT COUNT(1) FROM tbl_exp_test;

 

 

<Index Unique Scan>

CREATE TABLE tbl_idx_unq_scan AS SELECT * FROM dba_objects WHERE rownum < 100;
ALTER TABLE tbl_idx_unq_scan ADD PRIMARY KEY (object_id);
SELECT * FROM tbl_idx_unq_scan WHERE object_id = 5;

 

 

<Index Range Scan>

SELECT * FROM tbl_idx_unq_scan WHERE object_id < 50;

 

 

<Index Skip Scan>

CREATE TABLE tbl_idx_skip_scan AS SELECT * FROM dba_segments;
CREATE INDEX idx_ss_test ON tbl_idx_skip_scan (owner, segment_name, segment_type);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'tbl_idx_skip_scan');
SELECT * FROM tbl_idx_skip_scan WHERE segment_name = 'TEST';

 

 

<Full Index Scan>

/* When all columns referenced in the query and all columns in order by clause are indexed, and the order of the columns match the order of leading index columns, then full index scan access method is used to retrieve the data. */

 

 

<Fast Full Index Scan>
/* If the statement contains all indexed columns, instead of performing full table scan, optimizer might choose to perform fast full scan access method to retrieve the data. */

 

CREATE TABLE tbl_idx_ss_test AS SELECT object_id, object_name FROM dba_objects;
CREATE INDEX idx_ss_test ON tbl_idx_ss_test (object_id);
SELECT COUNT(*) FROM tbl_idx_ss_test WHERE object_id > 40;

 

 

<Index Join>
/* Index Join access method is used to avoid full table lookup if an index contains all of the columns used in a SQL. In this case, both indexes are read and hash join is performed to join them. To force optimizer to use an index join method, use INDEX_JOIN hint. */

CREATE TABLE tbl_index_join AS SELECT * FROM dba_objects;
CREATE INDEX idx_join_1 ON tbl_index_join (object_id);
CREATE INDEX idx_join_2 ON tbl_index_join (object_name);
SELECT	/*+ INDEX_JOIN (tbl_index_join idx_join_1 idx_join_2) */
		object_id, object_name
FROM	tbl_index_join
WHERE	object_id < 70 AND object_name = 'TEST';