that's what I get for not testing but just reading the manual :)
remind me not to answer questions when I don't have a database
handy.
sounds like Dan's going to have to add a column.
--- Kirtikumar Deshpande [EMAIL PROTECTED] wrote:
Rahcel, Dan:
I played with such things a long time
First time I've seen this post. And from the fine Data Warehousing
manual:
here's an example of range partitioning. Note the to_date in the
values clause. I don't see why you couldn't use
to_date(date_column,'MONTH')
Rachel
CREATE TABLE sales
(s_productid NUMBER,
s_saledate DATE,
Dan,
Good question, but unless I'm misinterpreting the results, the answer is
no...
SQL show release
release 902000100
SQL create table test
2 (a date, b number, c number)
3 partition by list (to_char(a, 'MON'))
4 (partition pJAN values ('JAN')),
5
The only way I see is using a system-maintained ( through a before-insert
and if necessary before-update trigger ) field that is set to
to_char(date_column,'mm') and then range partition on that.
At 03:24 PM 1/14/2004, you wrote:
Pardon if this is a duplicate, but the original has not shown up
PROTECTED] On Behalf Of David
WagonerSent: 03 December 2003 14:25To: Multiple
recipients of list ORACLE-LSubject: RE: partitioning option
licensing
As of 9iR2, partitioning is still licensed separately as a
$10K (retail) extra charge per processor. So, the total retail comes to
a painful
Title: RE: partitioning option licensing
As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge per processor. So, the total retail comes to a painful $50K per processor for 9i + Partitioning. Also, remember that support costs X% of the licensing per year
-Original Message-
Hi,
We're looking into migrating from SQL server to Oracle. Does
anyone know if
Partitioning option is still licensed separately?
Thanks.
pat
Having just met with an Oracle rep yesterday. Yes, it is still
licensed separately.
--
Please see the official
]
Subject: RE: partitioning option licensing
As of 9iR2, partitioning is still licensed separately as a $10K (retail)
extra charge per processor. So, the total retail comes to a painful $50K
per processor for 9i + Partitioning. Also, remember that support costs X%
of the licensing per year
-Original Message-
Sent: Thursday, 4 December 2003 01:25
To: Multiple recipients of list ORACLE-L
As of 9iR2, partitioning is still licensed separately as a $10K (retail) extra charge
per processor. So, the total retail comes to a painful $50K per processor for 9i +
Partitioning.
To paraphrase Pythia, an early leader on the DSS market,
my advice would be
Partition not create monolythic.
Partitioning is done for performance reasons, that much is clear.
To really answer your question in any other way then speaking
quixotically on laurel (SQL for short), I'd need to know
At the site of one of my customers, in a not too big (100GB) database
environment, using NAS over NFS on RS/6000 with AIX gave us far from
enough throughput. It turned out that much more mountpoints (20 i.s.o. 2)
were necessary to get a more-or-less satisfactory throughput.
At 10:54 6-11-03
Thanks all who replied. The purpose of this excercise is mainly #1 mentioned in Dennis
W.'s e-mail. Because of the size of this table, purging has been a challenge and we
want to keep only 2 years data in the table and periodically drop partitions to save
space.
Dennis
-Original
Dennis
What are you trying to achieve by partitioning? Generally I've seen two
common goals, 1) break a large table into more manageable pieces, 2)
performance tuning, so a query only has to scan a small partition. Sometimes
the two can be achieved simultaneously, sometimes they are at odds. If
If it has a date column, partition by that column into whatever makes sense (weeks,
months, etc.)
CREATE TABLE YOURTABLE
(
YOURDATE DATE NOT NULL,
YOURCOLUMN NUMBER
)
PARTITION BY RANGE (YOURDATE)
(
PARTITION YOURTABLE_JUN03 VALUES LESS THAN (TO_DATE('
A little addition to my post:
Of course partitioning downtime will not be that bad if you got spare space
to build partitioned table first, transfer data, then do switchover, then
transfer data changed meanwhile (using triggers, snapshots or even
logminer..).
Note that it is possible to add
Are you saying you want to have twelve partitions, one for each month
of the year (regardless of year)?
--- Meng, Dennis [EMAIL PROTECTED] wrote:
I have worked with partitioning before but have yet encountered the
following challenge -
The table we are trying to partition is a large table
Dennis,
Remember that you need to have a catch all partition to keep the
data that does not fall into the date ranges you specify for the
partitions. Someone will always enter a future date into a record if
they are allowed.
When It comes time to drop the old partitions and add a new year there
Hi!
You can use to_date function in partition by clause on date column.
But, you'll have downtime for this table anyway, if you want to split
existing table to partitions.
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, August 12,
Partitioning by range will do just fine!
partition by date_col (
partition jan2000 values less than to_date('02-01-2000', 'mm-dd-')
,partition feb2000 values less than to_date('03-01-2000', 'mm-dd-')
);
Kevin
-Original Message-
Sent: Tuesday, August 12, 2003
Title: RE: partitioning
Hi
Jacques,
How do
I exactly implement this.In the before insert trigger what after I generate the
value for the new partition column.How does the
records go into that partition.
Have
you tried this.How is the performance for an insert into a table of 10
Title: RE: partitioning
-Original Message-
From: Basavaraja, Ravindra [mailto:[EMAIL PROTECTED]]
How do I exactly implement this.In the before insert trigger
what after I generate the value for the new partition column.How does the
records go into that partition.
Have you tried
read about hash partitioning
-Original Message-
Sent: Wednesday, March 19, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L
Hi,
I am wondering if there is any way to achieve horizontal partitioning in
Oracle.
Assuming that I have about 8 partitions for a table.When there is
Title: RE: partitioning
You could accomplish this with a before insert trigger and a partitioning column that contains the value 0 through 7.
e.g.
create trigger
before insert
for each row
begin
select mod (sequence.nextval, 8) into :new.partition_column
from dual ;
end ;
/
Something
Ravindra
Disk striping with RAID will accomplish what you are seeking.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, March 19, 2003 1:00 PM
To: Multiple recipients of list ORACLE-L
Hi,
I am wondering if there is any
Title: RE: partitioning
thanks
-Original Message-From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]Sent: Wednesday, March 19, 2003
11:52 AMTo: '[EMAIL PROTECTED]'Cc:
'[EMAIL PROTECTED]'Subject: RE:
partitioning
You could accomplish this with a before insert trigger
Title: RE: Partitioning
It's true for 9.2, too. It doesn't make sense to
have different storage parameters for hash partitions.
- Original Message -
From:
Jacques Kilchoer
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, February 26, 2003 6:39
PM
Subject
what do you mean by storage size? if you mean the initial, next and so on -
yes you can.
if you are talking about how big it should be - i dont think you can do
it...
Babu
Yes you can. Check the storage option of the partition clause.
Something like:
create table (sales_yr varchar2(4),)
partition by range (sales_yr)
(partition p1 values less than ('1996')
tablespace blah_p1
storage (initial 100M next 100M pctincrease 0),
partition p2 values
Thank you very much
Conrad...
-Original Message-
Sent: Wednesday, February 26, 2003 5:31 PM
To: Conrad Meertins; Multiple recipients of list ORACLE-L
YES, here's an example:
create table ate_headers(module_id varchar2(13),
session_number varchar2(16),
Conrad
I've always stored each partition in a separate tablespace. Make each
tablespace LMT with uniform extents. But if you want, you can use the
syntax:
partition by range ( parm1, periodenddate )
(
partition sum_fy_01 values less than ('FY', to_date('01011999','mmdd'))
tablespace
Title: RE: Partitioning
I'll add that for HASH partitions or subpartitions you can only specify TABLESPACE, all other storage parameters are taken from table / partition defaults. At least in 8.1.7.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
YES
Typical canned answer is try to partition by a numeric. We partition by
a date when we can.
I'm torn between the order of one and two, but that is more personal
preference. I generally try to partition by the fields that make joins run
best and the ones that make maintainence better if I
Bill - Since nobody has replied yet, I'll toss in a couple of ideas. What is
your motivation for partitioning?
Performance? That is what I get from your posting. I think you have good
ideas. I'll provide one more that got us a good performance boost. Some
queries were often comparing this
Bill,
I'm impressed! Most of us follow something similar to the rankings you
mentioned - but a very few actually spell it out and put it for the data
architects. Now that you have done most of the work, I have a few comments.
First, you don't have to worry about legal issues for testing these
Actually, even without the date field, queries will still benefit from the
partition-wise join on the charge_id column. You would see something like this
(partition hash all) in the plan:
SELECT STATEMENT CHOOSE (Cost=178026)
PARTITION HASH ALL 1:4:1
HASH JOIN
PARTITION RANGE
[EMAIL PROTECTED] wrote:
Hello,
We are planning to move to Oracle 9.2 on as-yet-undecided platform
(probably red hat linux on ibm hardware).
We finally pursuaded management to purchase the partitioning
license, and I have some questions on partitioning:
Scenario:
Range-Partition
That was not a good buy. Partitioning comes with Oracle 9, partitioning
option is no longer sold separately.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]
Sent: Friday, November 22, 2002 8:44 AM
To: Multiple recipients of list ORACLE-L
Subject: partitioning
Mladen,
are you sure, partitioning is included with oracle 9?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 22, 2002 11:08 AM
That was not a good buy. Partitioning comes with Oracle
Partitioning is still a separately licensed product.
RF
Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!
Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of
of list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: partitioning questions
Date: Fri, 22 Nov 2002 08:08:55 -0800
That was not a good buy. Partitioning comes with Oracle 9, partitioning
option is no longer sold separately.
-Original Message-
From: [EMAIL PROTECTED]
[m
As of when? It's still listed as a costly option on the Oracle Store web page.
The perpetual license is $10,000.00 per CPU for the U.S. market.
Oracle 9i comes with lots of options many of which cost extra.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original
Is there a single column/value that you could do LIST partitioning on
instead of range or hash?
Rick
Rishi.Jain@VerizonWi
Rishi,
The algorithm for has partitioning in Oracle requires you to choose your
partition count as a power of 2 (i.e. 2, 4, 8, 16, 32 partitions, etc) any
other number will be unbalanced as you've seen.
Cheers,
Pete
--
Peter Moore
Systems DBA,
Mid-Range Centre of Expertise,
Global Service
[EMAIL PROTECTED] wrote:
We have a table with around 80 million rows. The table has been partitioned
by hash as there is no clear way of partitioning depending on range etc..
The data is very unevenly distributed in these partitions. Some of them even
have 3 times the number of rows as
You can do the same thing in Oracle as you did in Informix. Create range partitions
for 0-9 and use your mod 10 on the key. I believe you will have to add a column in
the table to hold the mod number and make that the partitioning column.
HTH,
John
[EMAIL PROTECTED] 11/19/02 11:38AM
We
Rishi,
In hash partitioning you have to select the number of
partitions equal to the power of 2. i.e. 2,4,8,16 so
on.
Your problem of skewed partition size is well
documented in hash partitioning if you don't choose
correct number of partitions.
Please refer to JLewis Book. He deals with this very
Title: Message
None
It is an option (Means you pay).
Yechiel AdarMehish
- Original Message -
From:
Cunningham, Gerald
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, May 22, 2002 6:38
PM
Subject: partitioning
Hi
all,
Does anybody know
with
Absolutely true! Even worse, it is an option on top of Enterprise Edition,
so you can't license Standard Edition and buy the partitioning option.
-Original Message-
Sent: Wednesday, May 22, 2002 12:06 PM
To: Multiple recipients of list ORACLE-L
None
It is an option (Means you pay).
Damn!
-Original Message-
Sent: Wednesday, May 22, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Absolutely true! Even worse, it is an option on top of Enterprise Edition,
so you can't license Standard Edition and buy the partitioning option.
-Original Message-
Sent:
Partition views, after all, were not that bad!
-Original Message-
Sent: Wednesday, May 22, 2002 5:21 PM
To: Multiple recipients of list ORACLE-L
Damn!
-Original Message-
Sent: Wednesday, May 22, 2002 1:19 PM
To: Multiple recipients of list ORACLE-L
Absolutely true! Even
instead of triggers
Waleed
-Original Message-
Sent: Wednesday, May 22, 2002 7:12 PM
To: Multiple recipients of list ORACLE-L
Quick memory test - were you able to create partition views such that you
could insert rows into view? In other words, could you create multiple
tables joined
Dennis,
Sure, you need to use INSTEAD OF triggers on views. I don't think Oracle8
supports INSTED OF triggers, but Oracle8i does. I was working on a project
some time ago where we had used partitioned views and instead of triggers to
implement functionality that's somewhat similar to
Kevin - This sounds similar to a partitioning issue that I was able to
resolve. My suggestion is to consider partitioning on a concatenated key,
INVOICE_STATE, UPDATE_DATE. You'll have to play with it, the partitions
don't work the way you think they do. As I recall, if you say less than
'AL',
Thanks to Dennis, I found that partitioning on a concatenated key solves my
dilema.
For those who are interested, here is my new partitioning clause:
PARTITION BY RANGE (invoice_state, update_date) (
PARTITION inv_active VALUES LESS THAN
('B', TO_DATE('01-jan-',
You are right. Partitioning can still make your job as a DBA easier.
You can purge data (assuming the purge key is the same as partition key)
by dropping partition, etc.
-Original Message-
Sent: Thursday, March 14, 2002 12:33 PM
To: Multiple recipients of list ORACLE-L
I am new to the
You're right about the performance - they (the ones before I got here) tried
to run the stats and use choose and the performance was terrible. Now it is
not an option to use CBO. One of my tasks is to set up an environment and
take it to CBO, but this is not the immediate priority.
-Original
Sathish Tatikonda wrote:
hi all,
I have read this document on partitioning which says that partitioning
cannot be implemented in clusters.
[snip]
This functionality is available for both indexes and tables, but cannot
be implemented in clusters.
Harvinder...
What is the access patterns of the queries that will be using these
tables... Knowing how the data will be accessed is am important factor in
determining how to set this stuff up... i.e. If your data is historical in
nature and the queries typically access data for via time
Eric,
In my prev. assignment I had to deal with partitions quite a bit.
Thankfully, on the tables I had to deal with, the parent and child records
were on partitions corresponding to the same month.
Your's is a tricky situation. I have an idea but I am not sure it is
feasible for you. But here
Charlie,
Thanks for the link - it did seem to suggest that since RAC is shown as an
option but partition is ticked as included.
However, the following (huge) link (to http://store.oracle.com and then
click on the Enterprise Edition link)
60 matches
Mail list logo
