cd
cat <<-'EOF' > dat2016.txt
1,1,2016-01-01
2,2,2016-01-02
EOF
cat <<-'EOF' > dat2017.txt
11,11,2017-11-01
12,12,2017-11-02
EOF
cat <<-'EOF' > dat2018.txt
21,21,2018-08-01
22,22,2018-08-02
EOF
ls -l dat*.txt
grep "" dat*.txt
CREATE TABLE hybrid_partition_table
( prod_id NUMBER NOT NULL,
cust_id NUMBER NOT NULL,
time_id DATE NOT NULL
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ORA_DIR
ACCESS PARAMETERS(
FIELDS TERMINATED BY ','
(prod_id,cust_id,time_id DATE 'yyyy-mm-dd')
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (time_id)
(PARTITION sales_2014 VALUES LESS THAN (TO_DATE('2015-01-01','yyyy-mm-dd')),
PARTITION sales_2015 VALUES LESS THAN (TO_DATE('2016-01-01','yyyy-mm-dd')),
PARTITION sales_2016 VALUES LESS THAN (TO_DATE('2017-01-01','yyyy-mm-dd')) EXTERNAL
LOCATION ('dat2016.txt'),
PARTITION sales_2017 VALUES LESS THAN (TO_DATE('2018-01-01','yyyy-mm-dd')) EXTERNAL
DEFAULT DIRECTORY ORA_DIR LOCATION ('dat2017.txt'),
PARTITION sales_2018 VALUES LESS THAN (TO_DATE('2019-01-01','yyyy-mm-dd')) EXTERNAL
DEFAULT DIRECTORY ORA_DIR LOCATION ('dat2018.txt')
);
insert into hybrid_partition_table values(31,31,'2014-01-01');
insert into hybrid_partition_table values(41,41,'2015-01-01');
commit;
select * from hybrid_partition_table;
explain plan for select * from hybrid_partition_table;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'ALL'));
