| Index and Partitions [message #621473] |
Wed, 13 August 2014 10:52  |
 |
dariusd7
Messages: 24
Registered: August 2014
|
Junior Member |
|
|
I have to create an index on a partioned table.. the table is not partitioned as of yet. So my question is do I partition the table first, and then put an index on it? The assignment says the index/partition should be on the Place_date column.. so I am thinking I should do a Hash partition. Also it wants it to be split into 3 partition and store in two table (two partitions are to be store in the user table and 1 of the partitions in the Users_Auto table)
is this even close?
Alter table classmate.classified_AD
add partition by has (placed_date)
partition 3
store in (user_auto, users)
partition user_auto values less than (47,501),
partition users values less than (50,001),
partition users values less than (52,501);
I just realized I need to split the partition by date using the to_date function.. I think I know how it should be done. but just not sure about the proper way to write the query as shown above.
any suggestion?
|
|
|
|
|
|
| Re: Index and Partitions [message #621479 is a reply to message #621473] |
Wed, 13 August 2014 11:08  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Quote:is this even close? No, it isn't. You cannot convert a non-partitioned table to a partitioned table. You need to create a new table, which is partitioned.
If you want good marks for the assignment, you need to think about the type of partitioning. Usually, range partitioning is appropriate if you are likely to run queries that use the partitioning column with a range predicate. Hash partitioning is better for queries with an equality predicate. You need to think about DML, too: hash partitioning is usually much better for high concurrent DML; range partitioning usually not so good. Composite partition can give you the best (or the worst) of both.
You also have to think about the indexing strategy. That is a whole lot more complicated.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Index and Partitions [message #621506 is a reply to message #621504] |
Wed, 13 August 2014 20:15 |
|
dariusd7
Messages: 24
Registered: August 2014
|
Junior Member |
|
|
THANKS. This is what I ended up doing before you posted Blackswan.
CREATE TABLE CLASSMATE.CLASSIFIED_AD2
(
AD_NO NUMBER NOT NULL,
SECTION_NO NUMBER NOT NULL ,
AD_TEXT VARCHAR2(1000),
CUSTOMER_ID NUMBER,
INTAKE_EDITOR_ID NUMBER,
PRICE NUMBER(6,2),
PLACED_DATE DATE,
RUN_START_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE,
RUN_END_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE,
RUN_DAYS INTERVAL DAY(3) TO SECOND(0)
)
partition by RANGE (PLACED_DATE)
SUBPARTITION BY hASH (RUN_START_DATE)
SUBPARTITIONS 1 STORE IN(USERS, USER_AUTO)
(PARTITION CLASSFIED_AD2007 VALUES LESS THAN (MAXVALUE))
;
it worked so far.. the next thing I'm going to do is do an exchange from
the original table called classmate.classified_ad to the partition table
called classmate.classified_ad2. then drop the original table and then
do an alter table to rename the partitioned table to the original table name
and also do an alter index to rename the index on the partitioned table to the
name of the index on the original table. Once that is done I will do a split partition
so that I will have 3 partitions as required by the assignment... Hope that is right and it works. will keep you posted.. You guys are a big help....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
