Oracle Performance and Engineering

I often get questions like - "If a=2 and b= 2 should we also write 'a=b' in the query?"

The answer is, just like everything else, yes and no ;)

If cola = constant and colb = constant, then Oracle optimizer evaluates cola = colb.

i.e., a query like -

SELECT * FROM TABLE TAB
WHERE
TAB.COL1 = 2 AND
TAB.COL2 = 2;

will be applied the rule of transitivity and re-written by the Optimizer as -

SELECT * FROM TABLE TAB
WHERE
TAB.COL1 = 2 AND
TAB.COL2 = 2 AND
TAB.COL1 = TAB.COL2;

*But*, and I didn't know this till pretty recently, if -

col1 = col 3 AND
col2 = col3 =>
Optimizer doesn't automatically apply transitivity and assume
"and col1 = col2"

In other words, transitivity works for literals and not for columns.

Even if transitivity is applied for literals against columns -- we should be careful in coding. Since using different bind variables OR not binding at all (and using a cursor_sharing = force type global replacement of unbound literals) when the data model does not specify a good entity relationship (lack of foreign keys in schema) --- could let the transitivity go for a six. I just had one such issue today --

SELECT bla_bla_bla
FROM
requests r
WHERE r.STATUS_ID in (:b0,:b1) AND
r.ASSIGNED_TO_USER_ID = :b2 AND
r.REQUEST_TYPE_ID in (:b3) AND
exists (SELECT '' FROM
CHECK_V pcv
WHERE
pcv.request_id = r.request_id and
pcv.user_id = :b4)

The query above was stalling all available resources in a load test. The statistics is as follows -

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9 0.01 0.02 0 0 0 0
Fetch 18 45.37 205.56 0 3336639 0 102
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 28 45.38 205.59 0 3336639 0 102

BINDS #67:

bind 0: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=256 offset=0 bfp=b69cf018 bln=32 avl=05 flg=05 value="30049"
bind 1: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=0 offset=32bfp=b69cf038 bln=32 avl=05 flg=01 value="30023"
bind 2: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=0 offset=160 bfp=b69cf0b8 bln=32 avl=05 flg=01 value="30542"
bind 3: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=0 offset=192 bfp=b69cf0d8 bln=32 avl=05 flg=01 value="30024"
bind 4: dty=1 mxl=32(05) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0010 size=0 offset=224 bfp=b69cf0f8 bln=32 avl=05 flg=01 value="30542"

Taking a quick look in the SQL and trying to figure out if there's any missing join (something I ALWAYS do first!) -- it seemed to me that "b2" and "b4" both are "USER_ID"s and both are passed the same values - though through different bind variables.

Hmmm...a quick call to the data modeler established my suspicion into a fact.

ASSIGNED_TO_USER_ID is indeed a "foreign key" to USER_ID and in this particular business case - they will always be the same for this query! (This schema does not have any foreign keys. Yeah, yeah. I know. But don't blame me!!).

So, I modified the query by supplying the missing join as (in bold) --

SELECT bla_bla_bla
FROM
requests r
WHERE r.STATUS_ID in (:b0,:b1) AND
r.ASSIGNED_TO_USER_ID = :b2 AND
r.REQUEST_TYPE_ID in (:b3) AND
exists (SELECT '' FROM
CHECK_V pcv
WHERE
pcv.request_id = r.request_id and
pcv.user_id = r.ASSIGNED_TO_USER_ID
pcv.user_id = :b4) ;

The whole world changed after. LIO (Consistent Gets, Logical I/O, query in traced data) was down to 72 from 350K.

Thus even though we were supplying the same constant for both USER_ID and ASSIGNED_TO_USER_ID --- the transitivity was not applied.

Reasons -
(1) We were using different bind variables for both constants. Using same bind variable for both, however, let Optimizer apply the transitivity.

and / or

(2) There is no entity relationship between both columns in database. Though that is not the direct reason transitivity was not applied, but that probably is the reason the developer treated both differently while writing the query.

CR talk :

Watched 'SHARK TALE' last night. Not as good as SHREK2, but definitely worth a see. I liked the physical similarity between the screen characters and actors. Agreed, it is difficult to make a vegeterian and friendly shark look like Jack Black (last movie I watched him in was Envy. That sucked!) - but they did a great job to make Skyes the swordfish totally look like Martin Scorcese. Lola - the gold digger lady fish - also had giant, droopy lips a la Angelina. Onscreen De Nero was the most impactful with his "Don Leone", the Godfather of Sharks. The DVD has a pretty cool dancing lesson section.

However, I wish they could show the billboard of the pretty obvious "Coral Cola - Always Real" a bit less. I hate surrogate advertising. Specially in kids' movies.