QUERY TRANSFORMATION in Java

Painting Code 3 of 9 in Java QUERY TRANSFORMATION

This was followed by just six plans (3 * 2 * 1) that all had the fact_tab in the last position in other words, the optimizer only tried to find an execution plan that was a proper star join, and didn t consider any others. The star hint doesn t help the optimizer to find that star join execution plan; it stops the optimizer from wasting time on other options. Hints, generally, are designed to limit the actions of the optimizer, and the star hint demonstrates this very clearly. As a little follow-up, I also created a test case with seven dimension tables. Interestingly, when hinted with the star hint, the optimizer examined all 5,040 possible join orders ignoring the fact that the parameter _optimizer_max_permutations was set to 2,000. In the absence of the star hint, 9i examined 10 join orders under the heading general plans, one join order under the head star plans (8i did all 5,040 at this point), and four join orders under the heading additional plans. It seems the star hint allows the optimizer to bypass the normal short-circuit code that makes a trade-off between the extra time required to check more join orders and the risk of missing a better execution plan. Mind you, the optimizer managed to get through all 5,040 join orders in 0.27 seconds (they were all simple join orders with no extra options for indexed access paths, and it was a 2.8 GHz CPU).

Sometimes, you can see where Oracle is going by looking at hidden parameters and undocumented functions. This section describes a couple of features that you should not use on a production system but you might like to know about them so that you can understand what s happening when they hit you in a future release. There could be many ways in SQL to specify a single query, and as time passes the optimizer is enhanced to find new ways to convert safely and efficiently between texts that are structured very differently but produce the same result. Consider the following query (see intersect_join.sql in the online code suite): select n2 from t1 where n1 < 3 intersect select n2 from t2 where n1 < 2 ; In principle, provided you deal with the problems of checking equality between null columns, you could rewrite this as follows: select from where and and ; distinct t1.n2 t1, t2 t1.n1 < 3 t2.n1 < 2 t2.n2 = t1.n2

Fortunately, the problem of comparing nulls can be solved (at least internally) with the undocumented function sys_op_map_nonnull(), which seems to return a value that is deemed to be of the type that matches its input parameter even though it is a value that is not normally considered to be legal for that type (typically a single 0xFF).

In the preceding example, we need only change the last predicate to sys_op_map_ nonnull(t2.n2) = sys_op_map_nonnull(t1.n2) to make the second form of the query a valid rewrite of the first query. Check the execution plan for the first query, and you get Execution Plan (10.1.0.4 autotrace) ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=30 Card=30 Bytes=600) 1 0 INTERSECTION 2 1 SORT (UNIQUE) (Cost=10 Card=45 Bytes=360) 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card=45 Bytes=360) 4 3 INDEX (RANGE SCAN) OF 'T1_I1' (INDEX) (Cost=2 Card=45) 5 1 SORT (UNIQUE) (Cost=20 Card=30 Bytes=240) 6 5 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=14 Card=30 Bytes=240) But set the hidden parameter _convert_set_to_join to true (this is a 10g parameter only), and the plan changes to Execution Plan (10.1.0.4 autotrace) ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=25 Card=33 Bytes=528) 1 0 SORT (UNIQUE) (Cost=25 Card=33 Bytes=528) 2 1 HASH JOIN (Cost=19 Card=33 Bytes=528) 3 2 TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=14 Card=30 Bytes=240) 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card=45 Bytes=360) 5 4 INDEX (RANGE SCAN) OF 'T1_I1' (INDEX) (Cost=2 Card=45) The hash join execution plan is exactly what you get if you rewrite the query manually from the intersection form to the join form. And if you run a full execution plan report against the plan table, you will find that the access_predicates on the hash join is, indeed sys_op_map_nonnull("t2"."n2") = sys_op_map_nonnull("t1"."n2") With this new parameter set, the optimizer can also transform queries using the minus set operator into queries using anti-joins. The transformation effectively changes the first query that follows into the second query and aren t you glad you won t have to do it by hand in the future, as it s too easy to make mistakes with this type of rewrite. select n2 from t1 where n1 < 3 minus select n2 from t2 where n1 < 2 ; select from where distinct n2 t1 n1 < 3