GaussDB中analyze关键字

本文介绍了一个用于收集数据库表统计信息的命令ANALYZE及其语法格式。ANALYZE可以帮助执行计划生成器确定最优执行计划,支持收集普通表、分区表、外表及多列统计信息,并能检测数据文件是否损坏。

1、功能描述

用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC下。执行计划生成器会使用这些统计数据,以确定最有效的执行计划。

  • 如果没有指定参数,analyze会分析当前数据库中的每个表和分区表
  • 同时也可以通过指定table_name、column和partition_name参数把分析限定在特定的表、列或分区表中

能够执行analyze特定表的用户,包括表的所有者、表所在数据库的所有者或通过GRANT被授予该表上analyze权限的用户以及有SYSADMIN属性的用户。

  • 在百分比采样收集统计信息时,用户需要被授予analyze和select权限。
  • analyze|analyze verify 用于检测数据库中普通表(行存表、列存表)的数据文件是否损坏,目前此命令暂不支持HDFS表

2、语法格式

  • 收集表的统计信息

    { ANALYZE | ANALYSE } [ VERBOSE ]     
    	[ table_name [ ( column_name [, ...] ) ] ];
    
  • 收集分区表的统计信息

    { ANALYZE | ANALYSE } [ VERBOSE ]     
    	[ table_name [ ( column_name [, ...] ) ] ]   PARTITION ( patrition_name ) ;  
    
  • 收集外表的统计信息。

    { ANALYZE | ANALYSE } [ VERBOSE ]     
    	{ foreign_table_name | FOREIGN TABLES };
    
  • 收集多列统计信息

    {ANALYZE | ANALYSE} [ VERBOSE ]    
    	table_name (( column_1_name, column_2_name [, ...] ));
    
  • 检测当前库的数据文件

    {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE};
    
  • 检测表和索引的数据文件

    {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE} table_name|index_name [CASCADE];
    
  • 检测表分区的数据文件

    {ANALYZE | ANALYSE} VERIFY {FAST|COMPLETE} table_name PARTITION {(patrition_name)}[CASCADE];
    

3、示例

--- 创建表。
CREATE TABLE customer_info
(
WR_RETURNED_DATE_SK       INTEGER                       ,
WR_RETURNED_TIME_SK       INTEGER                       ,
WR_ITEM_SK                INTEGER               NOT NULL,
WR_REFUNDED_CUSTOMER_SK   INTEGER
)
DISTRIBUTE BY HASH (WR_ITEM_SK);

--- 创建分区表
CREATE TABLE customer_par
(
WR_RETURNED_DATE_SK       INTEGER                       ,
WR_RETURNED_TIME_SK       INTEGER                       ,
WR_ITEM_SK                INTEGER               NOT NULL,
WR_REFUNDED_CUSTOMER_SK   INTEGER
)
DISTRIBUTE BY HASH (WR_ITEM_SK)
PARTITION BY RANGE(WR_RETURNED_DATE_SK)
(
PARTITION P1 VALUES LESS THAN(2452275),
PARTITION P2 VALUES LESS THAN(2452640),
PARTITION P3 VALUES LESS THAN(2453000),
PARTITION P4 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

--- 使用ANALYZE语句更新统计信息。
ANALYZE customer_info;

--- 使用ANALYZE VERBOSE语句更新统计信息,并输出表的相关信息。
ANALYZE VERBOSE customer_info;
INFO:  analyzing "cstore.pg_delta_3394584009"(cn_5002 pid=53078)
INFO:  analyzing "public.customer_info"(cn_5002 pid=53078)
INFO:  analyzing "public.customer_info" inheritance tree(cn_5002 pid=53078)
ANALYZE

--- 删除表。

DROP TABLE customer;
DROP TABLE customer_par;
<think> Hmm, the user is asking about GaussDB and libeSDKOBS.so.3 specifically. Let me analyze this carefully. First, looking at the provided references: Reference [1] shows the ldd output listing libraries linked to GaussDB, but libeSDKOBS.so.3 isn't mentioned there. Reference [2] is about port identification and debugging, not relevant here. Reference [3] discusses environment variables for GaussDB setup, while reference [4] talks about adding custom library paths. The user's core question seems to be about GaussDB's relationship with libeSDKOBS.so.3. Given that: - The library name suggests it's related to Object Storage Service (OBS), likely Huawei's cloud storage - GaussDB has OBS foreign table functionality for external data access - But this appears to be an optional component I recall that Huawei's documentation mentions libeSDKOBS as part of their OBS SDK. The ".so.3" version suggests it's the shared object for Linux. Important considerations: 1) This library wouldn't be in the standard GaussDB dependencies 2) It's only needed if using OBS foreign tables 3) The library must be manually placed in GaussDB's library path 4) Environment configuration (like LD_LIBRARY_PATH) is critical The user might be encountering a missing library error, or planning to use OBS integration. I should provide clear troubleshooting steps while emphasizing the OBS connection. Since they referenced ldconfig, they may already be dealing with library path issues. For the response structure: - First confirm the library's purpose - Explain when it's required - Give precise installation steps - Mention version compatibility - Provide verification methods Also need to remind about proper permissions since GaussDB runs under specific system users. Should include both deployment and troubleshooting guidance. </think> 根据引用内容和GaussDB相关知识,libeSDKOBS.so.3是华为对象存储服务(OBS)的客户端动态库,与GaussDB的关联及使用方式如下: ### 一、核心关系说明 1. **功能关联** libeSDKOBS.so.3 是GaussDB实现**OBS外表功能**的依赖库,用于支持数据库直接读写OBS对象存储中的数据[^1][^3]。 2. **部署要求** - 需将库文件放置在GaussDB的库加载路径中(默认`$GAUSSHOME/lib`) - 必须配置环境变量(如引用[3]所示): ```bash export LD_LIBRARY_PATH=$GAUSSHOME/lib:$LD_LIBRARY_PATH ``` - 若自定义路径,需按引用[4]方法更新配置: ```bash echo "/custom/path" >> /etc/ld.so.conf && ldconfig ``` ### 二、关键使用场景 1. **创建OBS外表** 通过该库实现SQL语法直接访问OBS数据: ```sql CREATE FOREIGN TABLE obs_table (...) SERVER obs_server OPTIONS (encoding 'utf8', location 'obs://bucket/path/'); ``` 2. **数据迁移** 支持`gsql`的`\COPY`命令与OBS间高速导入导出数据。 ### 三、故障排查步骤 当出现`libeSDKOBS.so.3: cannot open shared object file`错误时: 1. **验证库存在性** ```bash find $GAUSSHOME/lib -name libeSDKOBS.so.3 ``` 2. **检查加载路径** ```bash ldd $GAUSSHOME/bin/gaussdb | grep libeSDKOBS ``` 3. **确认环境变量** ```bash echo $LD_LIBRARY_PATH # 应包含$GAUSSHOME/lib ``` 4. **更新链接缓存** ```bash sudo ldconfig ``` > **注意版本兼容性**:需匹配GaussDB与OBS SDK的版本(如GaussDB 3.x对应libeSDKOBS.so.3.0.0)[^3]。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值