garbagedump

sql | dev | automatic partitions for the lazy kind - automatic list partitioning

weathered's photo
weathered
·

3 min read

nah, using sample schema is way easier actually, lets go to HR

lets say i want to make a stripped down table identical to the EMPLOYEES table, paritioned, by DEPARTMENT_ID column. so if i am using the HR.EMPLOYEES table, 

SELECT  COUNT(DISTINCT DEPARTMENT_ID)
  FROM  HR.EMPLOYEES;

/*
COUNT(DISTINCTDEPARTMENT_ID)
----------------------------
                          11
*/

i would need to create 11+1 (NULL) partitions (i like my NULLs separated), in the bad old way:

CREATE TABLE SYS.EMPLOYEES (
    EMPLOYEE_ID      NUMBER (6),
    FIRST_NAME       VARCHAR2 (20 BYTE),
    LAST_NAME        VARCHAR2 (25 BYTE),
    DEPARTMENT_ID    NUMBER (4)
)
PARTITION BY LIST(DEPARTMENT_ID)
(
    PARTITION DEPT_10 VALUES(10),
    PARTITION DEPT_20 VALUES(20),
    PARTITION DEPT_30 VALUES(30),
    PARTITION DEPT_40 VALUES(40),
    PARTITION DEPT_50 VALUES(50),
    PARTITION DEPT_60 VALUES(60),
    PARTITION DEPT_70 VALUES(70),
    PARTITION DEPT_80 VALUES(80),
    PARTITION DEPT_90 VALUES(90),
    PARTITION DEPT_100 VALUES(100),
    PARTITION DEPT_110 VALUES(110),
    PARTITION DEPT_NULL VALUES(NULL)
);

but this limits me to only values i defined in the table structure, we'll be getting errors if a new value shows up. DEFAULT???

    .
    .
    PARTITION DEPT_DEFAULT VALUES(DEFAULT)
);

i would need to create 11 (DISTINCT) + 1 (NULL) + 1 (DEFAULT) partitions, in the bad old way:

DROP TABLE SYS.EMPLOYEES;

CREATE TABLE SYS.EMPLOYEES (
    EMPLOYEE_ID      NUMBER (6),
    FIRST_NAME       VARCHAR2 (20 BYTE),
    LAST_NAME        VARCHAR2 (25 BYTE),
    DEPARTMENT_ID    NUMBER (4)
)
PARTITION BY LIST(DEPARTMENT_ID)
(
    PARTITION DEPT_10 VALUES(10),
    PARTITION DEPT_20 VALUES(20),
    PARTITION DEPT_30 VALUES(30),
    PARTITION DEPT_40 VALUES(40),
    PARTITION DEPT_50 VALUES(50),
    PARTITION DEPT_60 VALUES(60),
    PARTITION DEPT_70 VALUES(70),
    PARTITION DEPT_80 VALUES(80),
    PARTITION DEPT_90 VALUES(90),
    PARTITION DEPT_100 VALUES(100),
    PARTITION DEPT_110 VALUES(110),
    PARTITION DEPT_NULL VALUES(NULL),
    PARTITION DEPT_DEFAULT VALUES(DEFAULT)
);

INSERT INTO  SYS.EMPLOYEES
    SELECT  EMPLOYEE_ID,
            FIRST_NAME,
            LAST_NAME,
            DEPARTMENT_ID
      FROM  HR.EMPLOYEES;

    COMMIT;

SELECT  COUNT(*)
  FROM  USER_TAB_PARTITIONS
 WHERE  TABLE_NAME = 'EMPLOYEES';
/*
  COUNT(*)
----------
        13
*/

what if there are 100 distincts? or 1000?

i dont get paid enough for this, neither do i get to sleep peacefully or get vacations and stuff

now if i create the table with only the values i have now along with DEFAULT, what if a large chunk of new values show up, DEFAULT will be huge then, and the purpose is ruined. i want them individually created, but, in an easier way. if your db is running 12.2 or higher, do give a kiss on DBA foreheads and thank your management (even if you dont want to, understandable, just pretend), use AUTOMATIC and dump the DEFAULT. if a new value shows up, oracle will create a partition for it, automatically

DROP TABLE SYS.EMPLOYEES;

CREATE TABLE SYS.EMPLOYEES (
    EMPLOYEE_ID      NUMBER (6),
    FIRST_NAME       VARCHAR2 (20 BYTE),
    LAST_NAME        VARCHAR2 (25 BYTE),
    DEPARTMENT_ID    NUMBER (4)
)
PARTITION BY LIST(DEPARTMENT_ID) AUTOMATIC -- << this guy
(
    PARTITION DEPT_10 VALUES(10) --<<poof, the huge list is gone
);

INSERT INTO  SYS.EMPLOYEES
    SELECT  EMPLOYEE_ID,
            FIRST_NAME,
            LAST_NAME,
            DEPARTMENT_ID
      FROM  HR.EMPLOYEES;

    COMMIT;

SELECT  COUNT(*)
  FROM  USER_TAB_PARTITIONS
 WHERE  TABLE_NAME = 'EMPLOYEES';
/*
  COUNT(*)
----------
        12
*/

there is always someone much lazier than you, looking for shortcuts

SQLOracle