A clustering index is an ordered file with two fields; the first field is of the same type as the clustering field of the data file, and the second field is a disk block pointer. There is one entry in the clustering index for each distinct value of the clustering field, and it contains the value and a pointer to the first block in the data file that has a record with that value for its clustering field.
Could any one explain to me how the clustering index have one entry for each distinct value of the clustering field While these below link to an illustrations image show a pointer to the same value 1 in the same block
I have read it many times but I cannot understand it. One entry for each distinct value while the are 2 pointers each of which pointing to the clustering field 1 in the same block file. I tried to reverse the arrows but the same issue 2 pointers 1, and 2 to the same clustering field in the same clustering file as you can see while other pointers point to a distinct values in each clustering block.
The records with Dept_number==1 start in block 0
The records with Dept_number==2 start in block 0
The records with Dept_number==3 start in block 1
The records with Dept_number==4 start in block 2
The records with Dept_number==5 start in block 3
The records with Dept_number==6 start in block 4
The records with Dept_number==8 start in block 5
but it returns nothing when the passed value is null and I do have null in the table!
here is my stored procedure:
JOIN assets$status ON `assets$status`.status_id = assets$audit.audit_status
LEFTJOIN assets$conditions ON `assets$conditions`.condition_id = assets$audit.audit_condition
WHERE item_id = param_item_id
AND location = param_loaction
AND floor = param_floor
AND room = param_room
AND department = param_department
AND employee = param_employee
ORDERBY audit_date DESC, created_date DESC;
Read and understand this visual explanation of sql joins[^] then you will have an idea of what is happening.
You may not have a null value in the table but you certainly have a left join which will return nulls if there is no corresponding row on the right side of the join.
“That which can be asserted without evidence, can be dismissed without evidence.”