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

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*