Timeline for Key value alongside SQL table
Current License: CC BY-SA 4.0
18 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 17, 2022 at 14:56 | answer | added | David Browne - Microsoft | timeline score: 2 | |
| Aug 17, 2022 at 14:02 | history | edited | user1822 |
edited tags
|
|
| Aug 17, 2022 at 14:01 | comment | added | Charlieface | Partitioning is only useful if you want to bulk load, otherwise it just introduces complexity to no benefit, as you can just use a normal index | |
| Aug 17, 2022 at 13:56 | history | edited | Charlieface | CC BY-SA 4.0 |
deleted 10 characters in body
|
| Aug 16, 2022 at 15:20 | history | edited | user17990136 | CC BY-SA 4.0 |
added 12 characters in body
|
| Aug 16, 2022 at 15:16 | history | edited | user17990136 | CC BY-SA 4.0 |
edited body
|
| Aug 16, 2022 at 15:06 | history | edited | user17990136 | CC BY-SA 4.0 |
added 76 characters in body
|
| Aug 16, 2022 at 15:05 | comment | added | user17990136 |
@Akina Thanks for pointing out about the partitioning; I read about it and I think it is definitely going to work. But in case the valid column is not part of the original SQL table, do you see other options other than creating a key-value index alongside?
|
|
| Aug 16, 2022 at 15:04 | history | edited | user17990136 | CC BY-SA 4.0 |
added 374 characters in body
|
| Aug 16, 2022 at 12:46 | answer | added | J.D. | timeline score: 2 | |
| Aug 16, 2022 at 12:39 | history | edited | user17990136 | CC BY-SA 4.0 |
added 61 characters in body
|
| Aug 16, 2022 at 12:38 | history | edited | user17990136 | CC BY-SA 4.0 |
added 177 characters in body
|
| Aug 16, 2022 at 12:25 | history | edited | user17990136 | CC BY-SA 4.0 |
edited body
|
| Aug 16, 2022 at 12:22 | comment | added | user17990136 |
@Akina Sorry, I kind of oversimplify the question. In my intended question, the valid column doesn't actually exist in the original SQL table; it is computed at runtime and stored in another table (id, valid). But I could try to have valid column in the same table as well, which in that case, your suggestion regarding the partition is very interesting.
|
|
| Aug 16, 2022 at 11:50 | comment | added | Akina |
the number of valid records could be relatively very small (~100) compared to the whole size of the table (~10M+) - if so then this column must be index prefix. I.e. CREATE INDEX idx ON table (valid, col1, col2, col3). Moreover, you may partition your table by valid (i.e. CREATE PARTITION FUNCTION pfn (INT) AS RANGE LEFT FOR VALUES (0); GO; CREATE PARTITION SCHEME psc AS PARTITION pfn TO (invalid, valid);) which will made the rows selection O(1) because of the partition pruning.
|
|
| Aug 16, 2022 at 11:19 | history | edited | user17990136 | CC BY-SA 4.0 |
added 216 characters in body
|
| S Aug 16, 2022 at 11:14 | review | First questions | |||
| Aug 17, 2022 at 14:48 | |||||
| S Aug 16, 2022 at 11:14 | history | asked | user17990136 | CC BY-SA 4.0 |