{"action":"opened","issue":{"url":"https://api.github.com/repos/palantir/atlasdb/issues/998","repository_url":"https://api.github.com/repos/palantir/atlasdb","labels_url":"https://api.github.com/repos/palantir/atlasdb/issues/998/labels{/name}","comments_url":"https://api.github.com/repos/palantir/atlasdb/issues/998/comments","events_url":"https://api.github.com/repos/palantir/atlasdb/issues/998/events","html_url":"https://github.com/palantir/atlasdb/issues/998","id":179703318,"number":998,"title":"PK-Ordered Overflow Segments","user":{"login":"clockfort","id":57031,"avatar_url":"https://avatars.githubusercontent.com/u/57031?v=3","gravatar_id":"","url":"https://api.github.com/users/clockfort","html_url":"https://github.com/clockfort","followers_url":"https://api.github.com/users/clockfort/followers","following_url":"https://api.github.com/users/clockfort/following{/other_user}","gists_url":"https://api.github.com/users/clockfort/gists{/gist_id}","starred_url":"https://api.github.com/users/clockfort/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/clockfort/subscriptions","organizations_url":"https://api.github.com/users/clockfort/orgs","repos_url":"https://api.github.com/users/clockfort/repos","events_url":"https://api.github.com/users/clockfort/events{/privacy}","received_events_url":"https://api.github.com/users/clockfort/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2016-09-28T07:51:21Z","updated_at":"2016-09-28T07:51:21Z","closed_at":null,"body":"@hsaraogi who is currently making OSS Atlas Oracle work (thanks!) asked me a bunch of questions about how things work, how we use db sequences, etc.\r\nIn doing so she made me look at how we do things currently, and I don't really like it.\r\n\r\n[Light Background Oracle Documentation on IOT table type and Oracle-managed overflow segments](https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables012.htm) \r\n\r\n\r\n# Current System\r\n\r\nI would categorize this as a manually administrated overflow segment. (<2KB is stored inline, >2KB is stored in a separate more standard non-IOT table, we do all of this logic on insert/retrieve manually, this is not managed by oracle)\r\n\r\ncurrent cons: \r\n- requires some extra work for atlas-side db sequence ID caching in order to not be fairly latency sensitive (to not require blocking on db sequence ID generation before we insert data)\r\n- requires extra round trip (fetch all rows, get all the overflow IDs, fetch those overflow segments)\r\n- range scans that want to consume swaths of large-valued rows will cause an excessive number of disk seeks as it looks up out-of-order overflow IDs, as compared to either keeping the values inline (which is bad for general B-tree perf) or via the primary-key ordered overflow implementation you could get by using Oracle automated overflow PCTTHRESHOLD / INCLUDING clauses.\r\n\r\nhttps://github.com/palantir/atlasdb/blob/b06b8cd9dfd8537c2a450e66618e2c2b63162544/atlasdb-dbkvs/src/main/java/com/palantir/atlasdb/keyvalue/dbkvs/impl/oracle/OracleDdlTable.java#L65-L81\r\n```\r\n                \"CREATE TABLE \" + prefixedTableName() + \" (\"\r\n                + \"  row_name   RAW(\" + Cell.MAX_NAME_LENGTH + \") NOT NULL,\"\r\n                + \"  col_name   RAW(\" + Cell.MAX_NAME_LENGTH + \") NOT NULL,\"\r\n                + \"  ts         NUMBER(20) NOT NULL,\"\r\n                + \"  val        RAW(2000), \"\r\n                + (needsOverflow ? \"  overflow   NUMBER(38), \" : \"\")\r\n                + \"  CONSTRAINT pk_\" + prefixedTableName() + \" PRIMARY KEY (row_name, col_name, ts) \"\r\n                + \") organization index compress overflow\",\r\n                \"ORA-00955\");\r\n        if (needsOverflow && config.overflowMigrationState() != OverflowMigrationState.UNSTARTED) {\r\n            executeIgnoringError(\r\n                    \"CREATE TABLE \" + prefixedOverflowTableName() + \" (\"\r\n                    + \"  id  NUMBER(38) NOT NULL, \"\r\n                    + \"  val BLOB NOT NULL,\"\r\n                    + \"  CONSTRAINT pk_\" + prefixedOverflowTableName() + \" PRIMARY KEY (id)\"\r\n                    + \")\",\r\n                    \"ORA-00955\");\r\n```\r\n![current_overflow](https://cloud.githubusercontent.com/assets/57031/18904006/dd4f8016-8510-11e6-964d-4b72c218a357.png)\r\n\r\n\r\n# Proposed Solutions / Gen 3 Overflow Methods\r\n\r\n##  Oracle-managed overflow segments \r\n\r\n(i.e.  PCTTHRESHOLD)\r\npro: easy on Atlas devs\r\npro: can lose a round trip on fetching overflow IDs because oracle will just magic it in for you\r\ncon: less flexible\r\n\r\n\r\n## Ordered Manually Managed Overflow Segments\r\ni.e. Keep using manually managed overflow segments at insert time, but organize the Overflow table to instead of being primary-key'd  / ordered by an arbitrary sequence ID that is not related to the ordering of the table, to be instead organized with the same primary key as the main table.\r\n\r\npro: can lose a round trip on fetching overflow IDs by issuing two parallel range scans on both tables\r\npro: very flexible in the future, easy to change or tune at any time\r\ncon: we will keep around some code in our codebase that we already have, and include a small amount more\r\n\r\n![manually_managed2](https://cloud.githubusercontent.com/assets/57031/18904941/85ff7348-8515-11e6-9f39-a1fbd4992fbc.png)\r\n"}}
{"actor":{"display_login":"clockfort"}}