Toad World® Forums

Optimizer rewrites query from A=B to B=A. Why?


#1

My original query has COLUMN_NAME = value.
The optimizer rewrote my query to be value = COLUMN_NAME.
Why is this?


#2

Due to the SQL will go through our parser, the SQL syntax may be restructured, sometimes the predicates order will be changed too, there is no specific tuning reason of this kind of rewrite, but occasionally, the filtering order may be affected and change the SQL performance.


#3

Thank you richardto. However, I am not following why changing the query
from: A=B (ie; column_name = value)
to: B=A (ie; value = column_name)
would improve the performance of the query. Does this go under the optimization theory of writing SQL “small to big”?


#4

It is interesting! if you don’t mind, would you please post your original SQL and rewritten SQL with its query plans here, see, if I can tell you why!


#5

Thank you Richard!

I cannot give you the entire query, but here are the WHERE clauses (original and alternate), along with their Explain Plans.

/* ===== ORIGINAL ===== */
WHERE a.person_id = u.person_id (+)
AND (A.campus = :vCampus
OR A.xfer_campus = :vCampus2)
AND INSTR(A.person_group, ‘LOSS’) = 0
AND (A.person_id = U.person_id (+))

/* ===== ALTERNATE ===== elapsed time improved 9.76 times ===== */
WHERE a.person_id = U1.person_id (+)
AND 0 = INSTR(A.person_group, ‘LOSS’)
AND (:vCampus = A.campus
OR :vCampus2 = A.xfer_campus)

/* ===== ORIGINAL PLAN ===== */
1 One or more rows were retrieved using index SMU.idx_personGrp_xferCampus_Y . The index was scanned in ascending order.
2 Rows retrieved in steps 1 were converted to bitmaps.
3 One or more rows were retrieved using index SMU.idx_personGrp_campus_Y . The index was scanned in ascending order.
4 Rows retrieved in steps 3 were converted to bitmaps.
5 A logical OR operation was performed on the bitmaps from steps 2, 4.
6 One or more rows were retrieved using index SMU.idx_personGrp_instrLoss_X . The index was scanned in ascending order.
7 Rows retrieved in steps 6 were converted to bitmaps.
8 A logical AND operation was performed on the bitmaps from steps 5, 7.
9 The bitmaps returned from steps 8 were converted to rowids.
10 Rows from table SMU.person_grp were accessed using rowid got from an index.
11 One or more rows were retrieved using index SMU.idx_classes_course_X . The index was scanned in ascending order.
12 Rows from table SMU.classes were accessed using rowid got from an index.
13 HASH GROUP BY
14 A view definition was processed, either from a stored view SMU. or as defined by steps 13.
15 Rows from step 10 which matched rows from step 14 were returned (hash join).
16 The results were sorted to support the ORDER BY clause.
17 Rows were returned by the SELECT statement.

/* ===== ALTERNATE PLAN ===== */
1 One or more rows were retrieved using index SMU.idx_personGrp_campus_Y . The index was scanned in ascending order.
2 Rows retrieved in steps 1 were converted to bitmaps.
3 One or more rows were retrieved using index SMU.idx_personGrp_xferCampus_Y . The index was scanned in ascending order.
4 Rows retrieved in steps 3 were converted to bitmaps.
5 A logical OR operation was performed on the bitmaps from steps 2, 4.
6 One or more rows were retrieved using index SMU.idx_personGrp_instrLoss_X . The index was scanned in ascending order.
7 Rows retrieved in steps 6 were converted to bitmaps.
8 A logical AND operation was performed on the bitmaps from steps 5, 7.
9 The bitmaps returned from steps 8 were converted to rowids.
10 Rows from table SMU.person_grp were accessed using rowid got from an index.
11 One or more rows were retrieved using index SMU.idx_classes_course_X . The index was scanned in ascending order.
12 Rows from table SMU.classes were accessed using rowid got from an index.
13 HASH GROUP BY
14 A view definition was processed, either from a stored view SMU. or as defined by steps 13.
15 Rows from step 10 which matched rows from step 14 were returned (hash join).
16 The results were sorted to support the ORDER BY clause.
17 Rows were returned by the SELECT statement.


#6

Lola,

Thank you for your information, since the SQL is not a completed syntax SQL and the query plan is not come with sufficient information like a standard tree plan layout(cost, cardinality and predicates info) , but based on what you provided, I found there are two reasons that makes the alternative SQL 9.76 times faster than the original SQL:

  1. The redundant condition AND (A.person_id = U.person_id (+) was eliminated by our Quest SQL Optimizer, remove this redundant condition will help to the overall performance.

  2. The access order has been changed due to the new syntax as listed in the following:

/* ===== ORIGINAL PLAN ===== */
1 One or more rows were retrieved using index SMU.idx_personGrp_xferCampus_Y . The index was scanned in ascending order.
2 Rows retrieved in steps 1 were converted to bitmaps.
3 One or more rows were retrieved using index SMU.idx_personGrp_campus_Y . The index was scanned in ascending order.

1 One or more rows were retrieved using index SMU.idx_personGrp_campus_Y . The index was scanned in ascending order.
2 Rows retrieved in steps 1 were converted to bitmaps.
3 One or more rows were retrieved using index SMU.idx_personGrp_xferCampus_Y . The index was scanned in ascending order.

/* ===== ORIGINAL ===== */
WHERE a.person_id = u.person_id (+)
AND (A.campus = :vCampus
OR A.xfer_campus = :vCampus2)
AND INSTR(A.person_group, ‘LOSS’) = 0
AND (A.person_id = U.person_id (+)) - Removed

/* ===== ALTERNATE ===== elapsed time improved 9.76 times ===== */
WHERE a.person_id = U1.person_id (+)
AND 0 = INSTR(A.person_group, ‘LOSS’)
AND (:vCampus = A.campus
OR :vCampus2 = A.xfer_campus)