Index tracking is useful to know if the index is being used or not and then delete it.
In this post I’ll be creating a table called techdatabasket_customers, an index called techdatabasket_index and later activating index monitoring through the command “ALTER INDEX <index_name> MONITORING USAGE;” to finally use the view dba_object_usage (In previous versions of 12.1, the view was called V$OBJECT_USAGE) to get the information in the USED field with the value “YES” meaning that the index is being used and “NO” meaning that the index is not being used.
- Creating the table
SQL> CREATE TABLE techdatabasket_customers ( customer_id number(10) NOT NULL, customer_name varchar2(50) NOT NULL, city varchar2(50) ); 2 3 4 5 Table created.
- Creating the index
SQL> CREATE INDEX techdatabasket_index ON techdatabasket_customers (customer_id, customer_name) COMPUTE STATISTICS; 2 3 Index created.
- Enabling index monitoring
SQL> SQL> ALTER INDEX techdatabasket_index MONITORING USAGE; Index altered.
- Checking whether the index is being used or not
SQL> SET PAGES 2000 SET LINES 2000 SELECT * FROM dba_object_usage WHERE index_name = '&INDEX_NAME';SQL> SQL> 2 3 Enter value for index_name: techdatabasket_index old 3: WHERE index_name = '&INDEX_NAME' new 3: WHERE index_name = 'TECHDATABASKET_INDEX' OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- --- ------------------- ------------------- SYS TECHDATABASKET_INDEX TECHDATABASKET_CUSTOMERS YES NO 02/18/2018 11:50:35
- Disabling Index Monitoring
SQL> SQL> ALTER INDEX techdatabasket_index NOMONITORING USAGE; Index altered.
O monitoramento de index é útil para saber se o índice está sendo usado ou não para posteriormente deletá-lo.
Nest post estarei criando uma tabela chamada techdatabasket_customers, um índice chamado techdatabasket_index e posteriormente ativando o monitoramento do índice através do comando “ALTER INDEX <index_name> MONITORING USAGE;” para por fim utilizar a view dba_object_usage (Nas versões anteriores da 12.1, a view se chamava V$OBJECT_USAGE ) para obter as informações no campo USED com o valor “YES” significando que o índice está sendo utilizado e “NO” significando que o índice não está sendo utilizado.
- Criação da tabela
SQL> CREATE TABLE techdatabasket_customers ( customer_id number(10) NOT NULL, customer_name varchar2(50) NOT NULL, city varchar2(50) ); 2 3 4 5 Table created.
- Criação do index
SQL> CREATE INDEX techdatabasket_index ON techdatabasket_customers (customer_id, customer_name) COMPUTE STATISTICS; 2 3 Index created.
- Ativando o monitoramento do index
SQL> SQL> ALTER INDEX techdatabasket_index MONITORING USAGE; Index altered.
- Verificando se o índice está sendo utilizado ou não
SQL> SET PAGES 2000 SET LINES 2000 SELECT * FROM dba_object_usage WHERE index_name = '&amp;INDEX_NAME';SQL&gt; SQL&gt; 2 3 Enter value for index_name: techdatabasket_index old 3: WHERE index_name = '&amp;INDEX_NAME' new 3: WHERE index_name = 'TECHDATABASKET_INDEX' OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --- --- ------------------- ------------------- SYS TECHDATABASKET_INDEX TECHDATABASKET_CUSTOMERS YES NO 02/18/2018 11:50:35
- Desativando o monitoramento do índice
SQL> SQL> ALTER INDEX techdatabasket_index NOMONITORING USAGE; Index altered.
*The views expressed here are my own and do not represent those of my employer.*
Hello, I’m Bruno — a dual citizen of Brazil and Sweden. I bring a global perspective shaped by experiences in both South America and Europe, with a strong focus on collaboration and innovation across cultures. I am a Computer Scientist, PhD Candidate in Information and Communication Technologies, focusing on Data Science and Artificial Intelligence, and hold dual Master’s degrees in Data Science and Cybersecurity. With over fifteen years of international experience spanning Brazil, Hungary, and Sweden, I have collaborated with global organizations such as IBM, Playtech, and Oracle, as well as contributed remotely to projects across multiple regions. My professional interests include Databases, Cybersecurity, Cloud Computing, Data Science, Data Engineering, Big Data, Artificial Intelligence, Programming, and Software Engineering, all driven by a deep passion for transforming data into strategic business value.