Module 14: Analyzing QueriesOverviewQueries That Use the AND OperatorQueries That Use the OR OperatorQueries That Use Join OperationsSelectivity and Density of a JOIN ClauseHow Joins Are ProcessedHow Nested Loop Joins Are ProcessedSlide 9Considerations When Merge Joins Are UsedSlide 11ReviewModule 14: Analyzing QueriesOverviewQueries That Usethe AND Operatorthe OR OperatorJoin OperationsQueries That Use the AND OperatorProcessing the AND OperatorReturns rows that meet all conditions for every criterion specified in the WHERE clauseProgressively limits the number of rows returned with each additional search condition Can use an index for each search condition of the WHERE clause Indexing Guidelines and Performance Considerations Define an index on one highly selective search criterionEvaluate performance between creating multiple, single-column indexes and creating a composite indexQueries That Use the OR OperatorProcessing the AND OperatorReturns Rows That Meet Any of the Conditions for Every Criterion Specified in the WHERE ClauseProgressively Increases the Number of Rows Returned with Each Additional Search Condition Can Use One Index or Different Indexes for Each Part of the OR OperatorAlways Performs a Table Scan or Clustered Index Scan If One Column Referenced in the OR Operator Does Not Have an Index or If the Index Is Not Useful Can Use Multiple IndexesQueries That Use Join OperationsSelectivity and Density of a JOIN Clause How Joins Are ProcessedHow Nested Loop Joins Are ProcessedMultimedia: How Joins Are ProcessedConsiderations When Merge Joins Are UsedHow Hash Joins Are ProcessedSelectivity and Density of a JOIN ClauseSelectivity of a JOIN Clause Based on index density, if statistics are availableBased on a number of considerations, if statistics are unavailableDensity of a JOIN Clause An index with large number of duplicates has high join densityA unique index has low join densitySelectivityPercentage of Rows ReturnedHow Joins Are Processedmembermembermembermembermember_nomember_no……...5678......5678......Chen......Chen...ResultUnique nonclustered indexNonclustered indexchargechargechargechargecharge_nocharge_no...15259...16351...17673......15259...16351...17673...member_nomember_no...5678...5678...5678......5678...5678...5678...……member_nomember_no56785678567856785678567856785678567856785678567856785678567856785678567856785678567856785678567856785678charge_nocharge_no3025717673152591635132778488976061166794743967684086173879029960730257176731525916351327784889760611667947439676840861738790299607……(13 row(s) affectedUSE creditSELECT m.member_no, c.charge_no, c.charge_amt, c.statement_noFROM member AS m INNER JOIN charge AS cON m.member_no = c.member_noWHERE c.member_no = 5678USE creditSELECT m.member_no, c.charge_no, c.charge_amt, c.statement_noFROM member AS m INNER JOIN charge AS cON m.member_no = c.member_noWHERE c.member_no = 5678How Nested Loop Joins Are ProcessedUSE creditSELECT m.member_no, c.charge_no, c.charge_amt, s.statement_noFROM member AS m INNER JOIN charge AS cON m.member_no = c.member_no INNER JOIN statement AS sON c.member_no = s.member_noWHERE m.member_no = 5678USE creditSELECT m.member_no, c.charge_no, c.charge_amt, s.statement_noFROM member AS m INNER JOIN charge AS cON m.member_no = c.member_no INNER JOIN statement AS sON c.member_no = s.member_noWHERE m.member_no = 5678Joins the results with thequalifying rows of the charge tableRetrieves qualifying rows from both tables and joins them11112222chargechargechargechargecharge_nocharge_no...15259...16351...17673......15259...16351...17673...member_nomember_no...5678...5678...5678......5678...5678...5678...statementstatementstatementstatementstatement_nostatement_no...567815678......567815678...member_nomember_no...56785678......56785678...……................membermembermembermembermember_nomember_no……...5678......5678......Chen......Chen...Multimedia: How Merge Joins Are ProcessedConsiderations When Merge Joins Are UsedRequires That Joined Columns Are SortedEvaluates Sorted ValuesUses an existing index treeLeverages sort operationsPerforms its own sort operationPerformance ConsiderationsUsually defaultUSE creditSELECT m.lastname, p.payment_amt FROM member AS m INNER JOIN payment AS p ON m.member_no = p.member_no WHERE p.payment_amt < 7000 AND m.firstname < 'Jak'USE creditSELECT m.lastname, p.payment_amt FROM member AS m INNER JOIN payment AS p ON m.member_no = p.member_no WHERE p.payment_amt < 7000 AND m.firstname < 'Jak'Recommended PracticesDefine an Index on a Highly Selective ColumnDefine an Index on a Highly Selective ColumnEnsure That Useful Indexes Exist for All Columns Referencedin the OR OperatorEnsure That Useful Indexes Exist for All Columns Referencedin the OR OperatorMinimize the Use of Hash JoinsMinimize the Use of Hash JoinsReviewQueries That Usethe AND Operatorthe OR OperatorJoin