Page tree
Skip to end of metadata
Go to start of metadata
Purpose of Query

This page proposes a way to VACUUM FULL all the tables of a schema. This SQL command can help to improve the performance while executing queries, for more detailed description refer to Postgres - VACUUM

Applicable CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
Query for CSS

 

  1. Generate the Full vaccum queries:

    SELECT 'vacuum full ' 
           || table_name 
           || ';' 
    FROM   information_schema.tables 
    WHERE  table_schema = 'delta_local' 
    ORDER  BY table_schema, 
              table_name; 

    Here is an example of results vacumm_sample.txt

     

  2. Create an sql file

  3. Put the following in the created file:

    set search_path = :schema;
  4. Copy past the result of the query generated in point 1 in the sql file

  5. Remove all the guillemets from the file
  6. Trace the beginning of the vacuum full tables

  7. Open CMD

  8. Go to the location <CAST_install_director>\CSSAdmin\3rdParties\x64

  9. Run the following command line:

    psql --host <hostname> -p<port> -U <username> -v schema=<schema_name> -f "<sql file generated in step 1>" -d postgres -L <log file>

    where <hostname> is the host, <username> is the username, <schema_name> is the name of the schema <sql file generated in step 1> is the sql file generated in step 1, <log file> is log file that you can scpecified
    Exemple:

     

    psql --host localhost -p2280 -U operator -v schema=delta_local -f "C:\Users\myname\Documents\test.sql" -d postgres -L C:\Users\myname\Documents\test.log
  10. In order to validate that the operation is successful, refer to the generated log file.

Query for Oracle
Enter the SQL query
Query result example
 
Query result interpretation
 
Query for SQL server
Enter the SQL query
Query result example
 
Query result interpretation
 
Notes/comments
 

 

 

Related Pages
 

 


  • No labels