SQL scripts for indexes and partitioning in Oracle

advertisements

_____________________________________________________________________________________________________________________

1. Identify Indexes on Partitioned Tables or Specific Partitions 

 This script identifies indexes on partitioned tables or specific partitions. Replace 'YOUR_SCHEMA_NAME' and 'YOUR_TABLE_NAME' with the appropriate schema and table names you want to query. The script queries the all_ind_partitions data dictionary view to find indexes associated with partitions of the specified table.

   

-- Query to identify indexes on partitioned tables or specific partitions
SELECT index_name, table_name, partition_name, partition_position
FROM all_ind_partitions
WHERE table_owner = 'YOUR_SCHEMA_NAME'
  AND table_name = 'YOUR_TABLE_NAME'
  AND index_name NOT LIKE 'SYS_P%'
ORDER BY index_name, partition_position;

advertisements

 

2. Show the Partitioning Scheme of an Index 

This script shows the partitioning scheme of a specific index. Replace 'YOUR_SCHEMA_NAME' and 'YOUR_INDEX_NAME' with the appropriate schema and index names you want to query. The script queries the all_indexes data dictionary view to retrieve information about the partitioning and subpartitioning (if applicable) of the specified index.

-- Query to show the partitioning scheme of an index
SELECT index_name, table_name, partitioning_type, partition_count, subpartitioning_type, subpartition_count
FROM all_indexes
WHERE table_owner = 'YOUR_SCHEMA_NAME'
  AND index_name = 'YOUR_INDEX_NAME';

3. Analyze Index Usage and Performance on Partitioned Tables 

 This script analyzes index usage and performance on partitioned tables using AWR (Automatic Workload Repository). Replace 'YOUR_TABLE_NAME' with the actual table name you want to monitor. The script joins the dba_hist_sqlstat and dba_hist_sql_plan views to retrieve SQL performance statistics related to the specified index on the partitioned table. Please customize 'YOUR_SCHEMA_NAME', 'YOUR_TABLE_NAME', and 'YOUR_INDEX_NAME' with the appropriate schema, table, and index names in the scripts. Adjust the queries as needed for schema-specific information or additional details.

-- Query to analyze index usage and performance on partitioned tables
SELECT sql_id, plan_hash_value, executions, elapsed_time, buffer_gets, rows_processed
FROM dba_hist_sqlstat s
JOIN dba_hist_sql_plan p ON s.sql_id = p.sql_id AND s.plan_hash_value = p.plan_hash_value
WHERE p.object_type = 'INDEX' AND p.table_name = 'YOUR_TABLE_NAME'
ORDER BY sql_id;

_____________________________________________________________________________________________________________________

Website Stats