database-partitioning
What is the algorithm used by the ORA_HASH function?
another system that appears to use ORA_HASH Well, if it “appears to use” then it makes sense to do a bit of reverse engineering and check what exactly is called and disassemble code of the function. If you, however, want to dive into Oracle internals then following may help. First of all, you have to … Read more
how to partition a table by datetime column?
Partitions by HASH is a very bad idea with datetime columns, because it cannot use partition pruning. From the MySQL docs: Pruning can be used only on integer columns of tables partitioned by HASH or KEY. For example, this query on table t4 cannot use pruning because dob is a DATE column: SELECT * FROM … Read more
Optimize Postgres query on timestamp range
CLUSTER If you intend to use CLUSTER, the displayed syntax is invalid. create CLUSTER ticket USING ticket_1_idx; Run once: CLUSTER ticket USING ticket_1_idx; This can help a lot with bigger result sets. Not so much for a single or few rows returned. Postgres remembers which index to use for subsequent calls. If your table isn’t … Read more