All geek questions in one place

Separation by date and PK

I am developing a new laboratory database.

My primary data tables will have at least id (PK NUMBER)and created_on (DATE). In addition, for any two records, a record with a higher one idwill have a later date created_on.

I plan to split by created_onto improve performance on recently entered data. As the columns grow together, the table will also be split into id, implicitly. Oracle will not be aware of the proposed partitioning idto take advantage of partitioning table joins into id.

Two questions:

  • How to make both columns grow together?

  • How can I use this implicit partitioning for table joins?

+3
source share
5 answers

The real important question is: will you ever need a query over a range of identifiers? It is unlikely that you will need to build a query using ID BETWEEN :A AND :B. Consequently, Oracle will not benefit from a correlated partition scheme. For all questions you can use GUIDfor the primary key and you will get better scalability for INSERTS.

+2
source

, (, ..), . , , , , , ( ) . , . , PK rowid, , , .

[]

PK CREATED_ON, , YYYYMMDD, . :

  • ,

  • , , 0

  • , YYYYMMDD || { }, ,

, ID ,

+4

, . - , , , . 11g A, B, 10g . , , , , , , , "".

+1

"table_id" "created_on" , . , "table_id" "created_on", "PARTITION RANGE ALL" "PARTITION RANGE SINGLE". .

Edit:

:

ALTER TABLE my_table MODIFY created_on DEFAULT SYSDATE;

.

+1

?

  • , , id - , , ALTER SEQUENCE , , . , ETL, , / .

  • created_on, id. .

  • , , , 11g . date_partition

    CASE WHEN created_on BETWEEN ... AND ... THEN 'PARTITION_1' WHEN created_on BETWEEN ... AND ... THEN 'PARTITION_2' ... END

A similar virtual column is on id_partition, although you will need to query to get the minimum / maximum PK for each partition. It should be fast, because, being the primary key, there is an index on it.

Then you add a constraint such that id_partition = date_partition

0
source

Source: https://habr.com/ru/post/1715181/

More articles:


All Articles