![]() 4E_ST_NUM #E_CITY E ST E_ZF Dean Stacy Wood_50 200 Cha IL 6029 Bean James Fry_Rd €00 L.A CA 9000 Gubanov Vladimir Terry_Ave 2000 L.A CA 9000 Pie Karen Pipe Ave 1050 NYC NY 1000 Bently Eric Forest Rd 15502 NYC NY 1000 Mack Jerry Sible_Ave 100 Da: TX 7500 Tyler Mary Dakota_St 300 Pierrew SD 5750 Jones George Stampede. Filter: EMP_ID EMP_JOE_CODE 4E_LASTNAME E_FIRSTNAME E_ADDRE. Eile Edit View Navigate Run Team Tools Window Help 9 Connections CUSTOMER TABLE Columns Data ModelConstraints Grants Statistics|Triggers Flashback IDependencies Det EXO Sort. 200_Vista Visalia ky 5053216880 E Oracle SQL Developer: Table ADMIN.CUSTOMER TAB. C-STATE *C_PHDavid_Jone 500_W_St Terry TX 8326862Sherry_. File Edit View Navigate Run Team Tools Window Help BPC die Connections D CUSTOMER_INFO Columns Data Model constraints | Grants Statistics Triggers | Flashback Dependencies Det EXO Sort. ![]() Get it here: tuningsql.Oracle SQL Developer : Table ADMIN.CUSTOMER_INF. Free Oracle SQL Tuning GuideĬheckout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life! If those are the same, you can probably expect similar performance. Main point: If you have suspicions that they might be different, look at the execution plan and the filters, and verify those aren’t different. The filter SOME_COLUMN = ‘X’ is applied after the two tables are joined, any “all null” records in TABLE_B will be discarded as a result of the filter, and what was probably supposed to be an outer join is, as a result, effectively converted to an INNER JOIN. The second query isn’t really an outer join at all. The first query says “take all the recods in TABLE_A and try to join them to TABLE_B if they meet the SOME_COLUMN = ‘X’ criteria, otherwise return an “all nulls” value for the record from TABLE_B. Those two queries are extremely different. Left outer join table_b on (a.id = b.id and b.some_column = 'X') Suppose what you have is an OUTER join, not an inner join….putting the filter in the JOIN criteria will often yield a totally different result. in the JOIN clause on an inner join makes any difference…but to know for sure, you would want to check out the execution plan of both queries (including the filters), and compare them. I’ve never seen a situation where adding a filter in the WHERE clause vs. Well, the truth is I’ve seen instances where it absolutely does matter what order you list tables in within a FROM clause and listing the tables in different orders will yield different plans (and yes, to specify, I’m talking queries that don’t have an /*+ ordered */ hint). I say “should”…sometimes, things that “should” be true in a database, aren’t.įor example, it shouldn’t matter what order you list your tables in the FROM clause, right? ![]() “No” it doesn’t matter, in the sense that if you have a inner join between two different tables, and you put your filter criteria in the join portion, Oracle should do the same thing either way. When it Doesn’t (or at least Shouldn’t) Matter ![]() Inner join table_b b on (a.id = b.id and b.some_column = 'X')Īnswer: No…and Yes. Here’s a question I’ve been asked multiple times from multiple people: “Does it matter I put filters in the join clause vs in the WHERE clause? And if so, which one is better?” ![]()
0 Comments
Leave a Reply. |