Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need calculation examples 'You assume the numbers' and explanation for each of them (DBMS ch19 Query Optimization) 19.4 Cost Functions for SELECT Operation -
I need calculation examples 'You assume the numbers' and explanation for each of them
19.4 Cost Functions for SELECT Operation - Notation used in cost formulas CSP Cost for method Si in block accesses rX. Number of records (tuples) in a relation X bX: Number of blocks occupied by relation X (also referred to as b ) bfr XX : Blocking factor (i.e., number of records per block) in relation X sl A : Selectivity of an attribute A for a given condition sA: Selection cardinality of the attribute being selected (=slAr) xA : Number of levels of the index for attribute A b11A: Number of first-level blocks of the index on attribute A NDV (A,X) : Number of distinct values of attribute A in relation X Cost Function for SELECT Operation (cont'd.) - S3a: Using a primary index to retrieve a single record Cs3a=x+1 - S3b: Using a hash key to retrieve a single record CS3b=1 - S4: Using an ordering index to retrieve multiple records CS4=x+2b Cost Function for SELECT Operation (cont'd.) - S1: Linear search (brute force approach) - Search all file blocks to retrieve all records Csia=b - For equality condition on a key attribute, on average one-half the records are searched Cs1b=2b - S2: Binary search CS2=log2b+[bfrs]1 - Reduces to log2b if equality condition is on a key attribute Cost Functions for SELECT Operation (cont'd.) - S5: Using a clustering index to retrieve multiple records CS5=x+[bfrs] - S6: Using a secondary ( B+ tree) index CS6a=x+1+s (worst case) CS6b=x+2bh1+2r (for range queries) (DBMS ch19 Query Optimization)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started