Module 14 Analyzing Queries Overview Queries That Use the AND Operator the OR Operator Join Operations Queries 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 singlecolumn indexes and creating a composite index Queries 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 Processed Selectivity 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 density Percentage of Rows Returned Selectivity How Joins Are Processed USE USE credit credit SELECT SELECT m member no m member no c charge no c charge no c charge amt c charge amt c statement no c statement no FROM member AS m INNER JOIN charge FROM member AS m INNER JOIN charge AS AS cc ON ON m member no m member no c member no c member no WHERE c member no WHERE c member no 5678 5678 Unique nonclustered index member member member no member no 5678 5678 Chen Chen Result member no member no charge no charge no 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 5678 30257 30257 17673 17673 15259 15259 16351 16351 32778 32778 48897 48897 60611 60611 66794 66794 74396 74396 76840 76840 86173 86173 87902 87902 99607 99607 13 row s affected Nonclustered index charge charge charge no charge no member no member no 15259 15259 16351 16351 17673 17673 5678 5678 5678 5678 5678 5678 How Nested Loop Joins Are Processed USE USE credit credit SELECT SELECT m member no m member no c charge no c charge no c charge amt c charge amt s statement no s statement no FROM FROM member member AS AS mm INNER INNER JOIN JOIN charge charge AS AS cc ON ON m member no m member no c member no c member no INNER JOIN statement INNER JOIN statement AS AS ss ON ON c member no c member no s member no s member no WHERE WHERE m member no m member no 5678 5678 member member member no member no 5678 5678 Chen Chen statement statement statement no statement no member no member no 5678 5678 15678 15678 11 Retrieves qualifying rows from both tables and joins them 5678 5678 5678 5678 charge charge charge no charge no 15259 15259 16351 16351 17673 17673 member no member no 5678 5678 5678 5678 5678 5678 22 Joins the results with the qualifying rows of the charge table Multimedia How Merge Joins Are Processed Considerations 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 default USE USE credit credit SELECT SELECT m lastname m lastname p payment amt p payment amt FROM member AS m INNER FROM member AS m INNER JOIN JOIN payment payment AS AS pp ON ON m member no m member no p member no p member no WHERE WHERE p payment amt p payment amt 7000 7000 AND AND m firstname m firstname Jak Jak Recommended Practices Define Define an an Index Index on on aa Highly Highly Selective Selective Column Column Ensure Ensure That That Useful Useful Indexes Indexes Exist Exist for for All All Columns Columns Referenced Referenced in in the the OR OR Operator Operator Minimize Minimize the the Use Use of of Hash Hash Joins Joins Review Queries That Use the AND Operator the OR Operator Join Operations