create 방문테이블 ( 방문번호 number, 방문일자 varchar2(8), 고객ID varchar2(5),
배송일자 varchar2(8), 방문횟수 number ... )
partition by range(방문일자) (
partition by P2020_Q1 value less than ('20200101') -- 1월 기준
,partition by P2020_Q2 value less than ('20200201')-- 2월 기준
,partition by P2020_Q3 value less than ('20200301')
,partition by P2020_Q4 value less than ('20200401')
,partition by P2020_Q5 value less than ('20200501')
,partition by P2020_Q6 value less than ('20200601')
,partition by P2020_Q7 value less than ('20200701')
,partition by P2020_Q8 value less than ('20200801')
,partition by P2020_Q9 value less than ('20200901')
,partition by P2020_Q10 value less than ('20201001')
,partition by P2020_Q11 value less than ('20201101')
,partition by P2020_Q12 value less than (MAXVALUE)
-- 방문일자 >= '20201101'
)
-- 2020년 1월 부터 ~ 4월 30일까지 방문한 고객
select *
from 방문테이블
where 방문일자 >= '20200101'
and 방문일자 < '20200501'
1.파티션명 지정
select *
from table_name partition(P2020_Q1, P2020_Q2, P2020_Q3, P2020_Q4)
2. 파티션 조건 지정 (파티션명을 모를 경우)
select ~~
from table_name partition for ('7')
-- 파티션 키가 되는 값 지정, 숫자, 날짜등...
--파티션 DELETE문
DELETE FROM 테이블명 PARTITION (파티션명)
WHERE
조건;
mysql> EXPLAIN PARTITIONS
SELECT * FROM `test`.`tb_part_test`
WHERE 1=1
and part_key_col between '2019/01/01' and '2020/12/31'
ORDER BY sales_code;
+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb_part_test | p2019,p2020 | index | NULL | idx1_test | 302 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+--------------+-------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
출처:
https://dev4u.tistory.com/628 [데브포유:티스토리]