ANALYZE

Purpose

This statement is used to collect statistics for a table or a table partition.

Syntax

ANALYZE
  TABLE [ schema. ]table_name
    [ PARTITION (partition) | SUBPARTITION (subpartition) ]
  { COMPUTE STATISTICS [ for_clause ]
  | ESTIMATE STATISTICS [ for_clause ]
      [ SAMPLE integer { ROWS | PERCENTAGE } ]
  }

for_clause:
  FOR TABLE
  | ALL COLUMNS
  | COLUMNS column [, column]...

Parameters

Examples

• Create an employee information table and collect statistics.

  -- Create a departments table
  CREATE TABLE departments (
    dept_id     NUMBER(4) PRIMARY KEY,
    dept_name   VARCHAR2(30) NOT NULL,
    location    VARCHAR2(50)
  );
  
  -- Create an employees table
  CREATE TABLE employees (
    emp_id      NUMBER(6),
    emp_name    VARCHAR2(50) NOT NULL,
    job_title   VARCHAR2(50),
    manager_id  NUMBER(6),
    hire_date   DATE,
    salary      NUMBER(8,2),
    dept_id     NUMBER(4) NOT NULL,
    email       VARCHAR2(100),
    CONSTRAINT pk_employees PRIMARY KEY (emp_id, dept_id),
    CONSTRAINT fk_dept FOREIGN KEY (dept_id)
      REFERENCES departments(dept_id)
  ) PARTITION BY RANGE (dept_id) (
    PARTITION p_dept_10 VALUES LESS THAN (20),
    PARTITION p_dept_20 VALUES LESS THAN (30),
    PARTITION p_dept_other VALUES LESS THAN (MAXVALUE)
  );
  -- Create indexes
  CREATE INDEX idx_emp_dept ON employees(dept_id);
  CREATE INDEX idx_emp_name ON employees(emp_name);
  
  -- Collect complete statistics for the employees table
  ANALYZE TABLE employees COMPUTE STATISTICS;
  
  -- Collect complete statistics for the departments table
  ANALYZE TABLE departments COMPUTE STATISTICS;
  

• Collect statistics for a specific partition.

  -- Collect statistics for the partition with department ID less than 20
  ANALYZE TABLE employees PARTITION(p_dept_10) COMPUTE STATISTICS;
  

• Collect statistics for specific columns.

  -- Collect statistics for the salary and hire_date columns
  ANALYZE TABLE employees COMPUTE STATISTICS
  FOR COLUMNS salary, hire_date;
  

• Collect statistics using sampling.

  -- Collect statistics by sampling 1000 rows
  ANALYZE TABLE employees ESTIMATE STATISTICS
  SAMPLE 1000 ROWS;
  
  -- Collect statistics by sampling 5% of the data
  ANALYZE TABLE employees ESTIMATE STATISTICS
  SAMPLE 5 PERCENTAGE;
  

• Collect statistics for all indexed columns.

  -- Collect statistics for all indexed columns, setting the histogram bucket count to 150
  ANALYZE TABLE employees COMPUTE STATISTICS
  FOR ALL INDEXED COLUMNS SIZE 150;
  

• Collect statistics for all columns.

  -- Collect statistics for all columns
  ANALYZE TABLE employees COMPUTE STATISTICS
  FOR ALL COLUMNS SIZE 100;
  

• Collect histogram statistics for specific columns.

  -- Collect more detailed histogram statistics for the salary column
  ANALYZE TABLE employees COMPUTE STATISTICS
  FOR COLUMNS SIZE 254 salary;