Home » SQL & PL/SQL » SQL & PL/SQL » Index and Partitions (Oracle 11g )
Index and Partitions [message #621473] Wed, 13 August 2014 10:52 Go to next message
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 #621477 is a reply to message #621473] Wed, 13 August 2014 11:00 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
usually when a table is partitioned on DATE datatype range partitioning is used.

You can not "partition" a non-partitioned table.

You simply create a partitioned table to begin with.

>I need to split the partition by date using the to_date function

never use TO_DATE() function on any DATE datatype.
Re: Index and Partitions [message #621479 is a reply to message #621473] Wed, 13 August 2014 11:08 Go to previous messageGo to next message
John Watson
Messages: 4610
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 #621482 is a reply to message #621479] Wed, 13 August 2014 13:03 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
OK so how does the data in the existing table, get moved to the newly created partitioned table?
Re: Index and Partitions [message #621484 is a reply to message #621482] Wed, 13 August 2014 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
INSERT INTO NEW_TABLE SELECT * FROM OLD_TABLE;

OR

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN12636
Re: Index and Partitions [message #621499 is a reply to message #621484] Wed, 13 August 2014 19:33 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Ok I'm cruising along but I ran into a problem..according to the assignment I have to store the index
in two places User and user_auto.. now every where I look and read about indexes and
partitions.. the only time I see a store clause and when you create any kind of partition
except a Range.. when I tried to include the store in clause as part of a range partition I get
the missing or invalid option error. Also according to everything I read and as stated here since
the partition is on a date column the best partition is to use a range partition. So now i am stumped....
I need to use a range partition, and store the index in two places..but I am not allowed to use the
store in clause with the range partition... HEEEELLLLPPPP!!!!
Re: Index and Partitions [message #621500 is a reply to message #621499] Wed, 13 August 2014 19:44 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF54018

"The STORE IN clause is valid only for range-hash or list-hash composite-partitioned tables. It lets you specify one or more default tablespaces across which Oracle Database will distribute all index hash subpartitions for all partitions. You can override this storage by specifying different default tablespace storage for the subpartitions of an individual partition in the second STORE IN clause in the index_subpartition_clause."
Re: Index and Partitions [message #621501 is a reply to message #621500] Wed, 13 August 2014 19:48 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
can you write a simple example of a range partition with a sub-partition.. the example i see only use sub-partition with range hash, has, composite..etc..
Re: Index and Partitions [message #621502 is a reply to message #621501] Wed, 13 August 2014 19:50 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
everything I see shows subpartioning by hash.. This is really getting confusing now..
Re: Index and Partitions [message #621503 is a reply to message #621502] Wed, 13 August 2014 19:59 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
Only way I can see getting this done is to do a subpartition by hash, on the Run_Start_date column...
Re: Index and Partitions [message #621504 is a reply to message #621503] Wed, 13 August 2014 20:04 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
1 CREATE TABLE page_history
2 ( id NUMBER NOT NULL
3 , url VARCHAR2(300) NOT NULL
4 , view_date DATE NOT NULL
5 , client_ip VARCHAR2(23) NOT NULL
6 , from_url VARCHAR2(300)
7 , to_url VARCHAR2(300)
8 , timing_in_seconds NUMBER
9 ) PARTITION BY RANGE(view_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
10 SUBPARTITION BY HASH(client_ip)
11 SUBPARTITIONS 32
12 STORE IN (USERS)
13 (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2006','dd-MON-yyyy')))
14* PARALLEL 32 COMPRESS
SQL> /

Table created.
Re: Index and Partitions [message #621506 is a reply to message #621504] Wed, 13 August 2014 20:15 Go to previous message
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))
    ;
i
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....
Previous Topic: Schedule a Procedure with Input Parameters
Next Topic: SQL Update - Avoid Unique Constraint Error
Goto Forum:
  


Current Time: Wed Oct 01 00:41:09 CDT 2014

Total time taken to generate the page: 0.08202 seconds