3 votes 3 votes Databases indexing databases ace-test-series + – Na462 asked Jan 26, 2019 • edited Mar 3, 2019 by I_am_winner Na462 1.9k views answer comment Share Follow See all 21 Comments See all 21 21 Comments reply Manas Mishra commented Jan 26, 2019 reply Follow Share 99 ? 0 votes 0 votes balchandar reddy san commented Jan 26, 2019 reply Follow Share shouldn't it be 137? 0 votes 0 votes Na462 commented Jan 26, 2019 reply Follow Share Its 99, normally we take <search key,block pointer> but here its dense index so we need to point to individual record so <search key,record pointer> 0 votes 0 votes balchandar reddy san commented Jan 26, 2019 reply Follow Share Even then it will be 100 right, assuming that the blocks are unspanned. 0 votes 0 votes Somoshree Datta 5 commented Jan 26, 2019 reply Follow Share Assuming unspanned strategy, it would be 99, not 100. 0 votes 0 votes balchandar reddy san commented Jan 26, 2019 reply Follow Share solution? 0 votes 0 votes Magma commented Jan 26, 2019 reply Follow Share srestha $\frac{1024}{10} = 102 .4$ = 102 [due to unspanned] $\frac{10000}{102} = 98.03$ = 99 blocks needed 2 votes 2 votes srestha commented Jan 26, 2019 reply Follow Share No of record per block $\frac{1024}{100}=10$ No. of such block present $\frac{10000}{10}=1000$ Now blocking factor$\frac{1024}{6+2}=128$ So, in secondary indexing $\frac{10000}{128}=78$ @Magma chk once ans is 78 1 votes 1 votes srestha commented Jan 26, 2019 reply Follow Share in secondary indexing and first level $\frac{10000}{128}=78$ if asking for in secondary indexing and second level $\frac{78}{128}=1$ 0 votes 0 votes Magma commented Jan 26, 2019 reply Follow Share mam we construct secondary index for given data...not primary index that's Y we didn't include 6B (block index ) only we have to include ( record pointer + key) = 10B 0 votes 0 votes srestha commented Jan 26, 2019 reply Follow Share where have I taken primary index? I just took blocking factor 0 votes 0 votes srestha commented Jan 26, 2019 reply Follow Share blocking factor takes block pointer+key It is not Btree right? 0 votes 0 votes Magma commented Jan 26, 2019 reply Follow Share mam it's given that file is not order by key field....that's Y we apply blocking factor of secondary index right ?? 0 votes 0 votes srestha commented Jan 26, 2019 reply Follow Share blocking factor cannot be change for primary or secondary index 0 votes 0 votes Na462 commented Jan 27, 2019 reply Follow Share @srestha why you are using blocking factor ?? Here we are doing indexing on records directly i.e. in the index file we'll be having entry as <Search Key,Record pointer> not block pointer had it been sparse indexing there we first convert records into blocks by placing them within and calculate the blocking factor 0 votes 0 votes srestha commented Jan 27, 2019 reply Follow Share is it sparse or dense? not mentioned na? 0 votes 0 votes Na462 commented Jan 27, 2019 reply Follow Share Exactly but you see we are doing indexing on SSN and the records aren't ordered by SSN, that means we cant do sparse indexing on it because the order might be different and eventually we'll endup searching entirely, so its must to do dense indexing. And we know that secondary index must be dense only. 0 votes 0 votes srestha commented Jan 27, 2019 reply Follow Share @Na462 but how they got 10? it should be divided by 6+2=8 0 votes 0 votes Na462 commented Jan 28, 2019 reply Follow Share @srestha See Mam : Suppose i have a records as shown but the records when stored in file are stored using the FIRST attribute (The numbers here :) ) Now if i want to do indexing on Name attribute i cant do sparse why ? because as u can see above records are stored using numbers (orderly as both are ordered index) so if i search for 142 i can simply create a sparse index on blocks <Search Key , Block Pointer > and i can do a binary search to go to the record entry <= 142 from there i can do linear scan easily. But here we are doing indexing on Name attribute imagine i want to search for 'Bart' can i do similar binary search on Sparse index record file (if i do sparse indexing on Name ) to go to 'Bart' , NO ? Why because even if i scan lexicographically, you can see that name follows very different order. Hence its must to do indexing on every record. That's why its secondary index as its giving the order of records different from one stored in the files. So Dense indexing : I will keep an index corresponding to every Record : * In one index record i will keep <Search Key , Record Pointer> as You can see above so size of one entry = 8 + 2 = 10 ( Record pointer + Search Key here its SSN) * Now how many such entries i can have in a block : 1024/10 = 102, means in one block i can store 102 record index. We need to store for 10,000 records = 10000/102 = 98.02 = 99 blocks * Now if i again do indexing it will be Sparse , NOTE : Always the first level is sparse or dense and after then every indexing is sparse Why ? Because now we have blocks to deal with because we have already converted every thing to blocks :) Had it been Sparse: As you can see in figure they converted first in blocks, ALWAYS REMEMBER THAT SPARSE INDEX IS ONE PER BLOCK. Hence Number of blocks to store entire records :- How many records i can store in one block = 1024/100 = 10 I have total 10,000 records so 10000/10 = 1000 blocks Now in sparse index file as you can see i store <Search key , Block pointer > (Here block pointer pointing to block and also contains offset within the block to record > , So size of one entry = 6 + 2 = 8 (Block pointer , Search Key) How many index i can have = 1024/8 = 128 Hence number of blocks on first level = 1000/128 = 78. Hope you get now MAM 2 votes 2 votes Papan 2020 commented Oct 28, 2019 reply Follow Share Nice explanation sir. 0 votes 0 votes Pat_007 commented Aug 25, 2020 reply Follow Share Block size = 1024 B Size of index = 8 + 2 = 10 B. Number of indices in a block = 1024/10 = 102. Since in secondary indexing there will be a index for each data record in the database file.It’s dense indexing. Therefore number of indices in first level = number of data records = 10000. So, number of blocks in first level = total number of indices/ number of indices in a single block. number of block in first level = 10000/102 = 98.039. Since 98 block is sufficient so we will take 99 blocks. 1 votes 1 votes Please log in or register to add a comment.
0 votes 0 votes Can Indexing be spanned or unspanned ? By default do we take unspanned? If it's not mentioned in the question? srini_05 answered Aug 9, 2023 srini_05 comment Share Follow See all 0 reply Please log in or register to add a comment.