Hi,
we have an application that uses Oracle database to hold company data.
One of it's table, called PWORKSPACEOBJECT holds all 'displayable' objects in GUI client.
Most of the time users are searching for some data only by typing *some text* in GUI. The query produced by system is:
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 ) ) ;
giving the fact that table PWORKSPACEOBJECT holds 25 078 045 records and pobject_name column is of type VARCHAR2(128 BYTE) we get execution plans like:
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 |<b> TABLE ACCESS FULL| PWORKSPACEOBJECT | 1253K| 44M| | 85583 (1)| 00:17:07 |</b>
|* 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")
----------------------------------------------------------Indexes are as follow:
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")Is there any way to improve this query by changing indexes or sth else?
Best regards
Peter