| Create partitioned table [message #626400] |
Mon, 27 October 2014 10:24  |
Malakay79
Messages: 41
Registered: September 2007
|
Member |
|
|
I have to create a partitioned table:
create table TAB
(
db_name VARCHAR2(30),
data DATE,
col1 NUMBER,
col2 NUMBER,
col3 NUMBER,
col4 NUMBER,
col5 NUMBER,
col6 NUMBER,
app VARCHAR2(30),
cod VARCHAR2(30)
)
PARTITION BY RANGE(YEAR(data))
SUBPARTITION BY HASH(MONTH(data))
SUBPARTITIONS 12 (
PARTITION m1 VALUES LESS THAN (2),
PARTITION m2 VALUES LESS THAN (3),
PARTITION m3 VALUES LESS THAN (4),
PARTITION m4 VALUES LESS THAN (5)
PARTITION m5 VALUES LESS THAN (6)
PARTITION m6 VALUES LESS THAN (7)
PARTITION m7 VALUES LESS THAN (8)
PARTITION m8 VALUES LESS THAN (9)
PARTITION m9 VALUES LESS THAN (10)
PARTITION m10 VALUES LESS THAN (11)
PARTITION m11 VALUES LESS THAN (12)
PARTITION Q12 VALUES LESS THAN (13) );
And I get the error:
ORA-00907 missing right parenthesis
I don't understand why?
|
|
|
|
|
|
| Re: Create partitioned table [message #626455 is a reply to message #626404] |
Tue, 28 October 2014 03:22  |
Malakay79
Messages: 41
Registered: September 2007
|
Member |
|
|
I create the table like this:
create table MON_IO_STATS_PART
(
db_name VARCHAR2(30),
data DATE,
letture_fisiche NUMBER,
applicativo VARCHAR2(30),
codice_sito VARCHAR2(30)
)
PARTITION BY RANGE(data)
(PARTITION y2013 VALUES LESS THAN (TO_DATE('31-12-2013', 'DD-MM-YYYY')), --> 2013 data
PARTITION y2014 VALUES LESS THAN (TO_DATE('31-12-2014', 'DD-MM-YYYY'))) --> 2014 data
Now I would like to have a subpartition for every month (october, november...)
I can I do this?
|
|
|
|
|
|
|
|
| Re: Create partitioned table [message #626460 is a reply to message #626459] |
Tue, 28 October 2014 03:58 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You could do something like this (to take an old example I happen to have):
create table calls(callid integer,sourcenode integer, starttime date,fromnumber varchar2(11),tonumber varchar2(11),duration number)
partition by range (starttime) interval (interval '1' hour)
subpartition by hash (sourcenode) subpartitions 4
(partition p1 values less than (to_date('01-01-2012','dd-mm-yyyy')))
But first, you need to work out why a particular partitioning strategy will help you. What problem do you want to solve? Or is this jut an exercise to learn syntax?
|
|
|
|
| Re: Create partitioned table [message #626461 is a reply to message #626459] |
Tue, 28 October 2014 03:59 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> create table MON_IO_STATS_PART
2 (
3 db_name VARCHAR2(30),
4 data DATE,
5 letture_fisiche NUMBER,
6 applicativo VARCHAR2(30),
7 codice_sito VARCHAR2(30),
8 data_month integer as (to_number(to_char(data,'MM'))) virtual
9 )
10 PARTITION BY RANGE(data) subpartition by list (data_month)
11 (PARTITION y2013 VALUES LESS THAN (TO_DATE('31-12-2013', 'DD-MM-YYYY')),
12 PARTITION y2014 VALUES LESS THAN (TO_DATE('31-12-2014', 'DD-MM-YYYY')))
13 /
Table created.
|
|
|
|
| Re: Create partitioned table [message #626463 is a reply to message #626461] |
Tue, 28 October 2014 04:31 |
Malakay79
Messages: 41
Registered: September 2007
|
Member |
|
|
I did it.
Now if a execute this select:
select * from mon_io_stats_part where data_month = 4
I get this execution plan:
SELECT STATEMENT, GOAL = ALL_ROWS 3445 102 31314
PARTITION RANGE ALL 3445 102 31314
PARTITION LIST SINGLE 3445 102 31314
TABLE ACCESS FULL MONITOR_DB MON_IO_STATS_PART 3445 102 31314
Why oracle do not usa a partition but did a table access full?
|
|
|
|
|
|
| Re: Create partitioned table [message #626465 is a reply to message #626463] |
Tue, 28 October 2014 04:35 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You need to use a better way of showing execution plans. Then you wikll see the partition pruning. For example,12c orclz> set autot on exp
12c orclz> select * from mon_io_stats_part where data_month = 4;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 947751435
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 163 | 14018 | 56 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 163 | 14018 | 56 (0)| 00:00:01 | 1 | 2 |
| 2 | PARTITION LIST SINGLE| | 163 | 14018 | 56 (0)| 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | MON_IO_STATS_PART | 163 | 14018 | 56 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DATA_MONTH"=4)
12c orclz>
|
|
|
|
| Re: Create partitioned table [message #626540 is a reply to message #626465] |
Tue, 28 October 2014 15:02 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
if you want the data for 2013 and 2014 you are skipping the last day of the year. It should be
(PARTITION y2013 VALUES LESS THAN (TO_DATE('01-01-2014', 'DD-MM-YYYY')),
PARTITION y2014 VALUES LESS THAN (TO_DATE('01-01-2015', 'DD-MM-YYYY'))) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
