主页 / user-22677

haki's questions

Martin Hope
haki
Asked: 2019-01-20 05:50:45 +0800 CST
  • 1

我正在尝试对某些东西进行基准测试。我想创建一个索引并在 plpgsql 块中获取它的大小。

这有效：

haki=# create index ix on t(n);
CREATE INDEX

haki=# select pg_relation_size('ix');
 pg_relation_size 
------------------
         22487040

当我在 plpgsql 块中尝试相同时，我得到ERROR: relation "ix" does not exist：

haki=# DO $$DECLARE
haki$#     s int;
haki$# BEGIN
haki$#     PERFORM 'create index ix on t(n)';
haki$#     SELECT pg_relation_size('ix') INTO s;
haki$#     RAISE NOTICE 'size is %', s;
haki$# END$$;
ERROR:  relation "ix" does not exist
LINE 1: SELECT pg_relation_size('ix')

我猜这与 DDL 是事务性的并且块未提交这一事实有关（我也可能是错的！）。但是，我无法找到解决方法。

是否可以获取在 plpsql 块中创建的索引的大小？

postgresql plpgsql
Martin Hope
haki
Asked: 2013-08-14 23:01:35 +0800 CST
  • 5

这是场景

SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true)

PL/SQL procedure successfully completed.

SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / 1024 MB , BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN ('TM', 'TM_LD_IX');

SEGMENT_NAME            SEGMENT_TYPE        MB        BLOCKS
------------------------------------------ ---------- ----------
TM                      TABLE                 296      37888
TM_LD_IX                INDEX                  46       5888

SQL> select index_name , column_name from user_ind_columns where index_name = 'TM_LD_IX';

INDEX_NAME   COLUMN_NAME
------------ ------------------------------
TM_LD_IX     LD

SQL> explain plan for select distinct LD from TM;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

9 rows selected.

SQL> explain plan for select /*+ index(x , TM_LD_IX) */ distinct LD from TM x;

Explained.

SQL> @ex

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4241255022

--------------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   1 |  HASH UNIQUE       |                 |   693 |  4158 |  7920   (8)| 00:01:36 |
|   2 |   TABLE ACCESS FULL| TM              |  2549K|    14M|  7486   (3)| 00:01:30 |
--------------------------------------------------------------------------------------

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

如您所见，oracle 没有使用索引，LD而是选择了全表扫描。我什至不能让他使用带有历史记录的索引。

在上面的简单查询中，我希望对TM_LD_IX. mydb_file_multiblock_read_count设置为 32，所以我预计成本约为 5888 / 32 = 184（使用索引我还可以节省哈希唯一的成本）。

那么，我在这里缺少什么？

oracle optimization