=> Query table customers with three filters on columns cust_gender, cust_postal_code, cust_credit_limit with Full Table Scan hint and review the execution plan and statistics
Cost = 423
[oracle@dbhost01 ]$ sqlplus sh/sh@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 16 11:52:24 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Thu Mar 16 2023 11:51:16 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> set lines 1000
SQL> set autotrace traceonly explain statistics
SQL> SELECT /*+ FULL(c) */ c.*
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 40804
5 AND cust_credit_limit = 10000
6 /
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1134 | 423 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS | 6 | 1134 | 423 (1)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=10000 AND
TO_NUMBER("CUST_POSTAL_CODE")=40804 AND "CUST_GENDER"='M')
Statistics
----------------------------------------------------------
189 recursive calls
13 db block gets
1846 consistent gets
1520 physical reads
2204 redo size
3474 bytes sent via SQL*Net to client
497 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
=> Create three separate b-tree indexes on the above three columns used in the where clause
SQL> CREATE INDEX cust_cust_gender_idx
2 ON customers(cust_gender)
3 NOLOGGING COMPUTE STATISTICS;
Index created.
SQL>
SQL>
SQL> CREATE INDEX cust_cust_postal_code_idx
2 ON customers(cust_postal_code)
3 NOLOGGING COMPUTE STATISTICS;
Index created.
SQL>
SQL>
SQL> CREATE INDEX cust_cust_credit_limit_idx
2 ON customers(cust_credit_limit)
3 NOLOGGING COMPUTE STATISTICS;
Index created.
SQL>
=> Verify the indexes present on the customers table
SQL> col table_name for a30
SQL> col index_name for a50
SQL> SELECT ui.table_name
2 , decode(ui.index_type
3 ,'NORMAL', ui.uniqueness
4 ,ui.index_type) AS index_type
5 , ui.index_name
6 FROM user_indexes ui
7 WHERE ui.table_name = 'CUSTOMERS'
8 ORDER BY ui.table_name
9 , ui.uniqueness desc;
TABLE_NAME INDEX_TYPE INDEX_NAME
------------------------------ --------------------------- --------------------------------------------------
CUSTOMERS UNIQUE CUSTOMERS_PK
CUSTOMERS NONUNIQUE CUST_CUST_POSTAL_CODE_IDX
CUSTOMERS NONUNIQUE CUST_CUST_CREDIT_LIMIT_IDX
CUSTOMERS NONUNIQUE CUST_CUST_GENDER_IDX
SQL>
=> Enable Index monitoring on the newly created indexes and monitor index usage
SQL> ALTER INDEX CUSTOMERS_PK MONITORING USAGE;
Index altered.
SQL>
SQL> ALTER INDEX CUST_CUST_POSTAL_CODE_IDX MONITORING USAGE;
Index altered.
SQL>
SQL> ALTER INDEX CUST_CUST_GENDER_IDX MONITORING USAGE;
Index altered.
SQL>
SQL> ALTER INDEX CUST_CUST_CREDIT_LIMIT_IDX MONITORING USAGE;
Index altered.
SQL>
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
-------------------------------------------------- ------------------------------ --- --- ------------------- -------------------
CUSTOMERS_PK CUSTOMERS YES NO 03/16/2023 12:01:24
CUST_CUST_POSTAL_CODE_IDX CUSTOMERS YES NO 03/16/2023 12:01:24
CUST_CUST_GENDER_IDX CUSTOMERS YES NO 03/16/2023 12:01:24
CUST_CUST_CREDIT_LIMIT_IDX CUSTOMERS YES NO 03/16/2023 12:01:24
SQL>
=> Run the same query using INDEX Hint without specifying any particular index name and notice what index is being picked by the optimizer
SQL> set autotrace traceonly explain statistics
SQL> SELECT /*+ INDEX(c) */ c.*
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 40804
5 AND cust_credit_limit = 10000
6 /
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1389358116
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1134 | 218 (1)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 6 | 1134 | 218 (1)| 00:00:01 |
|* 2 | INDEX FULL SCAN | CUST_CUST_POSTAL_CODE_IDX | 89 | | 134 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=10000 AND "CUST_GENDER"='M')
2 - filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)
Statistics
----------------------------------------------------------
104 recursive calls
3 db block gets
398 consistent gets
132 physical reads
756 redo size
3275 bytes sent via SQL*Net to client
498 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
6 rows processed
=> Index used is CUST_CUST_POSTAL_CODE_IDX
Cost = 218
=> Because the selectivity of using the index CUST_CUST_POSTAL_CODE_IDX is better than using any other individual index or combination of any other indexes, lets verify that.
=> Run the same query by using INDEX_COMBINE Hint, without actually specifying the index names, so that the best possible combination is automatically chosen
SQL> SELECT /*+ INDEX_COMBINE(c) */ c.*
2 FROM customers c
3 WHERE cust_gender = 'M'
4 AND cust_postal_code = 40804
5 AND cust_credit_limit = 10000
6 /
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3819311714
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 1134 | 480 (1)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS | 6 | 1134 | 480 (1)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 5 | INDEX RANGE SCAN | CUST_CUST_CREDIT_LIMIT_IDX | | | 14 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 7 | INDEX RANGE SCAN | CUST_CUST_GENDER_IDX | | | 51 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("CUST_POSTAL_CODE")=40804)
5 - access("CUST_CREDIT_LIMIT"=10000)
7 - access("CUST_GENDER"='M')
Statistics
----------------------------------------------------------
4 recursive calls
7 db block gets
920 consistent gets
81 physical reads
1052 redo size
3275 bytes sent via SQL*Net to client
506 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL>
Cost = 480, cost is much higher that using single index
=> Verify the index usage, values YES will be updated in the USED column for the corresponding INDEX
SQL> set lines 1000
SQL> col index_name for a40
SQL> col table_name for a20
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------------------------------------- -------------------- --- --- ------------------- -------------------
CUSTOMERS_PK CUSTOMERS YES NO 03/16/2023 12:01:24
CUST_CUST_POSTAL_CODE_IDX CUSTOMERS YES YES 03/16/2023 12:01:24
CUST_CUST_GENDER_IDX CUSTOMERS YES YES 03/16/2023 12:01:24
CUST_CUST_CREDIT_LIMIT_IDX CUSTOMERS YES YES 03/16/2023 12:01:24
SQL>
No comments:
Post a Comment