Toad World® Forums

Why, How and When to concatenate '' to column values for comparisons to improve or optimize SQL performance and cost?


#1

Hello All,

I am new to using SQL Optimizer for Oracle. I am trying to understand why, how and when to concatenating column values with single quotes (’’) to improve or optimize SQL performance and cost?

For example, I have the following excerpt of the original and optimized query suggested by the Optimizer. An alternative query introduced 2 instances involving concatenation using the pipes ("||") operator and appending ‘’ to column values for comparison.

  1. W.WOCLASS || ‘’ = 'WORKORDER’
  2. W.OWNERGROUP = P2.PERSONGROUP || ''
    What’s the purpose of concatenating ‘’ to values for comparisons? Is it because a column used in the comparison could be NULL thus adding a ‘’ char to the nullable column implicitly makes the comparison quicker by not having to check for NULLs?

Thanks in advance.

Table WORKORDER

Column OWNERGROUP has DataType = VARCHAR2 (20 Byte) and NULL? = YES
Column WOCLASS has DataType = VARCHAR2 (16 Byte) and NULL? = NO

Table PERSONGROUP

Column PERSONGROUP has DataType = VARCHAR2 (20 Byte) and NULL?= NO

SQL Optimizer for Oracle v.8.7.0.2431

Excerpt of Original SQL:

SELECT (SUBSTR(P.DESCRIPTION, 1, INSTR(P.DESCRIPTION, ‘-’) - 2)) SITEID,

W.PERSONGROUP WORKGROUP,
SUBSTR(W.WORKTYPE, 1, 2) WORKTYPE,
ROUND(AVG(W.ACTLABHRS), 2) HOURS

FROM WORKORDER W

JOIN PERSONGROUP P

ON P.OXY_PERSONGROUPTYPE LIKE ‘A%’

AND W.OWNERGROUP = P.PERSONGROUP

WHERE W.WOCLASS = 'WORKORDER’

AND W.ISTASK = 0
AND W.STATUS IN (‘MCOMP’, ‘COMP’, ‘CLOSE’)
AND SUBSTR (W.WORKTYPE, 1, 2) < > ‘NM’
AND W.WORKTYPE IS NOT NULL
AND W.STATUSDATE > SYSDATE - 365

GROUP BY (SUBSTR (P.DESCRIPTION, 1, INSTR (P.DESCRIPTION, ‘-’) - 2)),

W.PERSONGROUP, SUBSTR (W.WORKTYPE, 1, 2)

Excerpt of Optimized Alternative:

SELECT SUBSTR(P2.DESCRIPTION, 1, INSTR(P2.DESCRIPTION, ‘-’) - 2) SITEID,

W.PERSONGROUP WORKGROUP,
SUBSTR(W.WORKTYPE, 1, 2) WORKTYPE,
ROUND(AVG(W.ACTLABHRS), 2) HOURS

FROM PERSONGROUP P2,

WORKORDER W

WHERE W.WOCLASS || ‘’ = 'WORKORDER’

AND W.ISTASK = 0
AND W.STATUS IN (‘MCOMP’, ‘COMP’, ‘CLOSE’)
AND SUBSTR (W.WORKTYPE, 1, 2) <> ‘NM’
AND W.WORKTYPE IS NOT NULL
AND W.STATUSDATE > SYSDATE - 365
AND P2.OXY_PERSONGROUPTYPE LIKE ‘A%’
AND W.OWNERGROUP = P2.PERSONGROUP || ''

GROUP BY SUBSTR (P2.DESCRIPTION, 1, INSTR (P2.DESCRIPTION, ‘-’) - 2),

W.PERSONGROUP, SUBSTR (W.WORKTYPE, 1, 2)


#2

show us plans for both cases, but what can I say is that piping is forcing oracle to se as varchar and multiplying (i.e. emp.salary * 1E-19, what I saw a lot) is forcing a number.


#3

Hi T. Ung,

Sorry that I missed your original post or I should have answered you earlier. (Thanks, Damir for helping here.)

The purpose of the rewrite which added the " || ‘’ " is to make Oracle giving up on using an index on the column. When we add the " || ‘’ " (or sometimes adding the “+ 0”) to a column, Oracle needs to evaluate the final value of the expression before comparing with the value on the other side of the condition. As a result, Oracle cannot use an index on this column to search for the rows. The result of the condition will not change as we are only adding an empty string to the original value but the change will disallow the use of index on this column.

Using your conditions to explain, your original conditions are:

  1. W.WOCLASS = ‘WORKORDER’
  2. W.OWNERGROUP = P2.PERSONGROUP

One of the options in Oracle to retrieve data for your original query is to use an index on “W.WOCLASS” to find all ‘WORKORDER’ (your condition 1) and then possibly using another index on “P2.PERSONGROUP” to locate the corresponding rows (your condition 2). In such case, the driving path to retrieve data from this query is WORKORDER --> PERSONGROUP.

However, in the rewrite, the conditions are changed to:

  1. W.WOCLASS || ‘’ = ‘WORKORDER’
  2. W.OWNERGROUP = P2.PERSONGROUP || ‘’

So Oracle can no longer use an index on “W.WOCLASS” to search the rows (because of the added " || ‘’ " in conditon 1). And same that index on “P2.PERSONGROUP” cannot not be used (because of the changes in condition 2). So this rewrite will more likely have a plan starting with searching the rows from PERSONGROUP (P2) first and then use another index on “W.OWNERGROUP” to locate the corresponding rows. That is, the driving path may change to PERSONGROUP --> WORKORDER.

As Damir suggested, without the actual plans, it would be hard to tell the effect of the changes. You may not see the same plans I “guessed” above for your original SQL and the rewrite. However, I hope the information will help explain the use of the " || ‘’ " in the rewrites.

Thanks,
Alex


#4

Much appreciated Alex! That is a thorough breakdown and makes logical sense.

Below is the results compared with Original and Alternative 142 which yield the quickest outcome.

Scenario Name
Status Icon
Plan Cost
Status
Elapsed Time
Elapsed Time
First Row Time
CPU Used by this Session
Physical Reads
Session Logical Reads
Number of Executions
Number of Records
Table Scan Rows Gotten
Table Scan Blocks Gotten
Sorts (Rows)
Table Fetch by Rowid
Parse Time CPU
Parse Time Elapsed
Table Fetch Continued Row
Consistent Gets
Consistent Changes
Table Scans (Long Tables)
Table Scans (Short Tables)
Sorts (Memory)
Sorts (Disk)
Original
&nbsp
378518
&nbsp
00:14:02.1700000
00:14:02.1700000
00:14:01.2000000
320.66
6291678
9686930
1
93076
37729156
478368
1623120
13165860
0.12
0.13
1
9667482
13248
2
22
154
2
Alt9
&nbsp
719827
&nbsp
00:04:22.6200000
00:04:22.6200000
00:04:21.6500000
214.35
7429439
7070354
1
93076
75488272
959930
919906
16131897
7.43
7.5
0
7070325
0
8
10
10
1
Alt11
&nbsp
719684
&nbsp
00:04:19.9200000
00:04:19.9200000
00:04:18.9500000
210.21
7436213
7075683
1
93076
75488272
959930
919909
16146118
7.42
7.51
0
7075654
0
8
10
10
1
Alt28
&nbsp
720173
&nbsp
00:04:22.0100000
00:04:22.0100000
00:04:21.0300000
214.02
7422394
7064559
1
93076
75488272
959930
1268926
16121187
7.38
7.46
0
7064530
0
8
10
12
1
Alt38
&nbsp
720030
&nbsp
00:04:22.0100000
00:04:22.0100000
00:04:21.0400000
211.9
7435890
7080507
1
93076
75488272
959930
1268935
16150090
7.46
7.53
0
7080478
0
8
10
12
1
Alt65
&nbsp
752654
&nbsp
00:04:12.3000000
00:04:12.3000000
00:04:11.2800000
197.59
6812869
6063241
1
93076
82963563
1339516
919803
12744936
7.44
7.51
0
6063212
0
9
10
10
1
Alt91
&nbsp
753000
&nbsp
00:04:07.2900000
00:04:07.2900000
00:04:06.3400000
196.17
6732594
6073145
1
93076
82963563
1339516
1268826
12765762
7.42
7.5
0
6073115
0
9
10
12
1
Alt116
&nbsp
752654
&nbsp
00:04:12.3700000
00:04:12.3700000
00:04:11.3900000
195.83
6741142
6058907
1
93076
82963563
1339516
919802
12645152
7.42
7.51
0
6058878
0
9
10
10
1
Alt133
&nbsp
388507
&nbsp
00:05:13.5400000
00:05:13.5400000
00:05:12.5100000
203.86
4513323
9667453
1
93076
37729145
478357
1270500
13157420
0.05
0.06
8
9667434
0
2
11
103
1
Alt142
&nbsp
753000
&nbsp
00:04:05.7800000
00:04:05.7800000
00:04:04.7800000
197.67
6671993
6057984
1
93076
82963563
1339516
1268823
12644808
7.41
7.49
0
6057954
0
9
10
12
1
Alt167
&nbsp
718995
&nbsp
00:04:26.1800000
00:04:26.1800000
00:04:25.2200000
215.58
7677569
7061307
1
93076
75488272
959930
919929
16101920
7.44
7.55
0
7061278
0
8
10
10
1
Alt185
&nbsp
719341
&nbsp
00:04:27.3400000
00:04:27.3400000
00:04:26.3500000
215.68
7670603
7054435
1
93076
75488272
959930
1268953
16086552
7.33
7.51
0
7054406
0
8
10
12
1


#5

Hi T. Ung,

You are welcomed. I hope the explanation in my last post answered your question.

Thanks,

Alex