Oracle 大型表和类似查询的问题

askTom 2018-04-09

157

问题描述

嗨，

我们有一个使用Oracle数据库保存公司数据的应用程序。
其中一个名为PWORKSPACEOBJECT的表在GUI客户端中保存所有 “可显示” 对象。
大多数情况下，用户仅通过在GUI中键入 * 某些文本 * 来搜索某些数据。系统产生的查询是:

SELECT DISTINCT t_02.puid FROM PWORKSPACEOBJECT t_01 , PITEM t_02 WHERE ( UPPER(t_01.pobject_name) LIKE UPPER( '%some text%' ) AND ( t_01.puid = t_02.puid ) ) ;复制

给出表PWORKSPACEOBJECT保存25 078 045条记录和pobject_name列的类型为VARCHAR2(128字节) 的事实，我们得到如下执行计划:

Execution Plan
----------------------------------------------------------
Plan hash value: 2506165203

------------------------------------------------------------------------------------------------
| Id  | Operation           | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                  |  1253K|    62M|       |   101K  (1)| 00:20:21 |
|   1 |  HASH UNIQUE        |                  |  1253K|    62M|    72M|   101K  (1)| 00:20:21 |
|   2 |   NESTED LOOPS      |                  |  1253K|    62M|       | 85588   (1)| 00:17:08 |
|*  3 | TABLE ACCESS FULL| PWORKSPACEOBJECT |  1253K|    44M|       | 85583   (1)| 00:17:07 |
|*  4 |    INDEX UNIQUE SCAN| PIPITEM          |     1 |    15 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(UPPER("POBJECT_NAME") LIKE '%BL%')
   4 - access("T_01"."PUID"="T_02"."PUID")
----------------------------------------------------------复制

索引如下:

PIPWORKSPACEOBJ_2 NONUNIQUE NORMAL NO NO POBJECT_NAME
PIPWORKSPACEOBJ_3 NONUNIQUE FUNCTION-BASED NORMAL NO NO SYS_NC00031$

PIPWORKSPACEOBJ_3 is -> ASC UPPER("POBJECT_NAME")复制

有什么方法可以通过更改索引或其他方式来改善此查询？

最好的问候
彼得

专家解答

可悲的是，如果您使用前导通配符，Oracle数据库将无法对索引进行范围扫描。这是因为您的搜索可以匹配索引中的任何条目。

要解决这个问题，请考虑创建Oracle文本索引。然后更改您的查询以使用contains运算符而不是像

有关更多详细信息，请阅读开发人员指南:

https://docs.oracle.com/en/database/oracle/oracle-database/18/ccapp/toc.htm

或者来自Tim Hall的这个例子:

https://oracle-base.com/articles/9i/full-text-indexing-using-oracle-text-9i

本站也有几个例子。