Guess what? The execution plan has now changed with the addition of constraints and now Oracle’s execution path goes from a suboptimal plan to the optimal path. Now what does Oracle do with the added constraint info: We can now see who the parent and child is, so we can determine the optimal query path which is to start at B, the only filter and join to the child C then to the parent A. Now let’s diagram the query with DB Optimizer: Well, let’s keep the same indexes and just add some constraints: alter table c add constraint c_pk_con unique (id) Īlter table b add constraint b_pk_con unique (id) The super cool thing with DB Optimizer 3.0 is we can overlay the diagram with the actual execution path (I think this is so awesome)įor the digram we can see Oracle starts with B and joins to A. Well, what does Oracle decide to do? This is where the cool part of DB Optimizer 3.o comes in. Guess what ? It’s also hard for Oracle to figure it out. Now where do we go from B? Who is the parent and who is the child? It’s not defined in the constraints nor indexes on these tables so it’s hard for us to know. Table B has a filter criteria in the query “b.val2=100”. There is one filter in the diagram, represented by the green F on table B.
start at the most selective filter table.Question is “what is the optimal execution path for this query?” The red lines with crows feet mean that as far as the definitions go, the relations could be many to many. Diagramming the query in DB Optimizer gives Let’s take an example query: SELECT COUNT (*) The new release of DB Optimizer, version 3.o, from Embarcadero has awesome new feature : explain plan overlay onto VST diagrams! Visual SQL Tuning available in DB Optimizer $429 seat from Idera ( I have no association with Idera but I did design the product when working at Embarcadero where it was originally created).
0 kommentar(er)
