| Very slow wildcard query [message #64854] |
Thu, 19 February 2004 15:54  |
Rohan
Messages: 6
Registered: February 2004
|
Junior Member |
|
|
I have a query internal to an application that is taking an exceptionally long time to execute. It is a wildcard search in a complex query.
Following are execution time stats for various combinations of the criteria causing the problem.
The part of the query incorporating the wildcard is:
--AND UPPER(shp_flex11) LIKE UPPER('texu%') -- this takes approx 35-48 secs
If we change the query around slightly we get the following:
--AND UPPER('texu%') LIKE UPPER(shp_flex11) -- this takes approx <1 sec
--AND shp_flex11 LIKE UPPER('texu%') -- this takes approx 2 secs
--AND upper(shp_flex11) LIKE 'texu%' -- this takes approx 2 secs
--AND UPPER(shp_flex11) LIKE UPPER('texu') -- this takes pprox 2 secs
We have a functional index on UPPER(shp_flex11), but it does not seem to help this query.
As we cannot change the query, is anyone aware of any db parameters or anything else that may affect how quickly it is executed?
Thanks in advance.
|
|
|
|
| Re: Very slow wildcard query [message #64861 is a reply to message #64854] |
Fri, 20 February 2004 09:53  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
"We have a functional index on UPPER(shp_flex11), but it does not seem to help this query"
Are you certain of this ? Have you gathered the statistics for the concerned tables? . Could you trace the session and post the execution plan/statistics,here ?
-Thiru
|
|
|
|
|
|
| Re: Very slow wildcard query [message #64867 is a reply to message #64861] |
Sun, 22 February 2004 14:19 |
Rohan
Messages: 6
Registered: February 2004
|
Junior Member |
|
|
Thanks very much for the replies.
Below is the execution plan. It shows that the UPPER(SHP_FLEX11) index is being used. The query joins to a table called VL_DELIVERY, and the plan shows that the major cost is a full table scan on that table. What is puzzling to me is that, as stated in the original posting, when the query criteria are changed (unrelated to the VL_DELIVERY table) the performance improves. Any thoughts?
SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1130 Card=1 Bytes=21799)
..COUNT (STOPKEY)
....VIEW (Cost=1130 Card=1 Bytes=21799)
......SORT (ORDER BY STOPKEY) (Cost=10 Card=1 Bytes=683)
........FILTER
..........NESTED LOOPS (OUTER) (Cost=8 Card=1 Bytes=683)
............NESTED LOOPS (Cost=8 Card=1 Bytes=516)
..............NESTED LOOPS (Cost=7 Card=1 Bytes=474)
................NESTED LOOPS (Cost=6 Card=1 Bytes=437)
..................NESTED LOOPS (OUTER) (Cost=5 Card=1 Bytes=379)
....................NESTED LOOPS (Cost=5 Card=1 Bytes=296)
......................NESTED LOOPS (Cost=4 Card=1 Bytes=254)
........................TABLE ACCESS (BY INDEX ROWID) OF VL_USER (Cost=1 Card=1 Bytes=45)
..........................INDEX (UNIQUE SCAN) OF USR_UK1 (UNIQUE)
........................TABLE ACCESS (BY INDEX ROWID) OF VL_SHIPMENT (Cost=3 Card=1 Bytes=209)
..........................INDEX (RANGE SCAN) OF SHP_FLEX11_UPPER_IDX (NON-UNIQUE) (Cost=1 Card=1)
......................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=1 Card=1 Bytes=42)
........................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE)
....................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION_ML
......................INDEX (UNIQUE SCAN) OF ORGML_UK1 (UNIQUE)
..................TABLE ACCESS (BY INDEX ROWID) OF VL_EVENT (Cost=1 Card=1 Bytes=58)
....................INDEX (UNIQUE SCAN) OF EVNT_PK (UNIQUE)
................TABLE ACCESS (BY INDEX ROWID) OF VL_LOCATION (Cost=1 Card=1 Bytes=37)
..................INDEX (UNIQUE SCAN) OF LOC_PK (UNIQUE)
..............TABLE ACCESS (BY INDEX ROWID) OF VL_EVENTCODE (Cost=1 Card=1 Bytes=42)
................INDEX (UNIQUE SCAN) OF EVNTC_PK (UNIQUE)
............TABLE ACCESS (BY INDEX ROWID) OF VL_EVENTCODE_ML
..............INDEX (UNIQUE SCAN) OF EVNTCML_PK (UNIQUE)
..........FILTER
............TABLE ACCESS (FULL) OF VL_DELIVERY (Cost=1120 Card=48340 Bytes=1836920)
............FILTER
..............CONNECT BY (WITH FILTERING)
................NESTED LOOPS
..................FILTER
....................INLIST ITERATOR
......................INDEX (RANGE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=7 Bytes=140)
..................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
................NESTED LOOPS
..................BUFFER (SORT)
....................CONNECT BY PUMP
..................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
..............SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
................UNION-ALL
..................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
....................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
..................FILTER
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
............FILTER
..............TABLE ACCESS (BY INDEX ROWID) OF VL_SHIPMENTLEG (Cost=4 Card=1 Bytes=39)
................INDEX (RANGE SCAN) OF SHPL_SHP_OID (NON-UNIQUE) (Cost=3 Card=1)
..............FILTER
................CONNECT BY (WITH FILTERING)
..................NESTED LOOPS
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
....................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
..................NESTED LOOPS
....................BUFFER (SORT)
......................CONNECT BY PUMP
....................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
......................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
................SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
..................UNION-ALL
....................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
......................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
....................FILTER
......................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
............FILTER
..............CONNECT BY (WITH FILTERING)
................NESTED LOOPS
..................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
..................TABLE ACCESS (BY USER ROWID) OF VL_ORGANIZATION
................NESTED LOOPS
..................BUFFER (SORT)
....................CONNECT BY PUMP
..................TABLE ACCESS (BY INDEX ROWID) OF VL_ORGANIZATION (Cost=2 Card=1 Bytes=40)
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=12572)
..............SORT (UNIQUE) (Cost=6 Card=2 Bytes=49)
................UNION-ALL
..................TABLE ACCESS (BY INDEX ROWID) OF VL_USERAFFILIATE (Cost=1 Card=1 Bytes=29)
....................INDEX (RANGE SCAN) OF UAFF_UK1 (UNIQUE)
..................FILTER
....................INDEX (UNIQUE SCAN) OF ORG_PK (UNIQUE) (Cost=1 Card=1 Bytes=20)
|
|
|
|
| Re: Very slow wildcard query [message #64869 is a reply to message #64867] |
Mon, 23 February 2004 04:58 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
oh Ok,I was under the impression that the function based index wasnt used when your query takes longer. So,when you modify the query a little bit, the index gets skipped ? and/or the whole plan changes ,yielding a much faster response? If thats the case, can you skip the index via NOINDEX hint or completely drop the index if it isnt used elsewhere ? Also,does the table VL_DELIVERY have a usable index on it ?
There are optimizer settings like optimizer_index_caching, optimizer_index_cost_adj that you can adjust to influence the optimizer behaviour(ie choosing/ignoring index) , in case you cant modify the query to ignore the index. For more info about these parameters Optimizer influence
Hope this helps
Thiru
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
