Home » SQL & PL/SQL » SQL & PL/SQL » Advanced partitioning question (windows vista service pack 1)
Advanced partitioning question [message #354609] Mon, 20 October 2008 10:02 Go to next message
christian.boeur@cepa.be
Messages: 2
Registered: October 2008
Location: Belgium
Junior Member
Hi,

I am working on the partitioning of the history tables of one of our applications, and I encountered this situation.

lets say that I use this statement to create an index with 1 partition:

CREATE INDEX PAD.PAD_DRW_COW_FK ON PAD.PAD_WER_DRUKKEN_NEW
(CODE_WER)
NOLOGGING
GLOBAL PARTITION BY HASH (CODE_WER)
PARTITIONS 1 STORE IN(I_PHIST01);


when I execute this statement and later on I go to check the script code (for example using toad or something else) I notice that oracle has changed my code into this...

CREATE INDEX PAD.PAD_DRW_COW_FK ON PAD.PAD_WER_DRUKKEN_NEW
(CODE_WER)
NOLOGGING
TABLESPACE USERS
GLOBAL PARTITION BY HASH (CODE_WER)
PARTITIONS 1 STORE IN(I_PHIST01);


this is not a severe problem but I would like to know whe Oracle does that. I would have suspected that when using a partition clause with tablespace specification like I do, that no extra tablespace clause must be specified but appearantly it does.

I would realy like to know what the tablespace mentionned in the tablespace clause is used for, My guess is NOTHING... but I'm not sure.

because we no longer use the USERS tablespace, Ive changed my statement into this:

CREATE INDEX PAD.PAD_DRW_COW_FK ON PAD.PAD_WER_DRUKKEN_NEW
(CODE_WER)
NOLOGGING
TABLESPACE T_NOTBS
GLOBAL PARTITION BY HASH (CODE_WER)
PARTITIONS 1 STORE IN(I_PHIST01);


where T_NOTBS is read only tablespace. so when a single byte of data is inserted in the T_NOTBS tablespace, an ORA error must occur. therefore I must be sure that my assumption is correct.

[Updated on: Mon, 20 October 2008 10:03]

Report message to a moderator

Re: Advanced partitioning question [message #354616 is a reply to message #354609] Mon, 20 October 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When you create an index a default tablespace must be defined for new partition and it is the current user default tablespace.
Try "alter index ... add partition ..." without naming any tablespace and you will see.

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Edit: Change wrong word]

[Updated on: Tue, 21 October 2008 03:04]

Report message to a moderator

Re: Advanced partitioning question [message #354763 is a reply to message #354616] Tue, 21 October 2008 02:55 Go to previous message
christian.boeur@cepa.be
Messages: 2
Registered: October 2008
Location: Belgium
Junior Member
thanks for the response.
Previous Topic: multiple join query
Next Topic: date counting select
Goto Forum:
  


Current Time: Sat Dec 03 13:44:04 CST 2016

Total time taken to generate the page: 0.05318 seconds