RE: Partition

[Jacques Kilchoer]

I agree with Dennis Williams. To know what is the right answer for you,
first you should determine why you want the table partitioned in the first
place. Is it so that you can easily "archive" old data by using the "alter
table  drop partition" command? Is it so that your queries will benefit
from partition pruning? Or is it only to split the table across several
tablespaces or have different storage parameters for each partition? (In the
last case you can use hash partitions.)

Have you tried "alter table ... split partition ..." see the SQL Reference
manual) to divide your "high" partition into smaller subsets??

What do you do with the table once it is exported? Is it for a backup of the
data or for another reason?

To avoid snapshot too old on the export, you can try to export one partition
at a time (if you are using range or list partitioning). For example if
partition 1 is specified with "values less than (100)":
exp scott/tiger tables=partner query=\"where partner_id \< 100\"

To change a partitioned table into a subpartitioned table, I think the only
way is to do a create table ... as select ... or a create table ... / insert
... select ...

> -Original Message-
> From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
> 
> Jacques, the version is 9.0.1.1.1.
> Dennis, Thanks for the confidence I totally agree with you 
> about designing
> and everything else.
> Could there be a temporary solution to get successful exports until we
> embark on this project.
> we are in a 24x 7 environment.
> 
> -Original Message-
> DENNIS WILLIAMS
> 
>I am fairly new to partitions myself and must relearn the 
> commands each
> time I need to work with the partitions. So I will speak more to the
> philosophy of a DBA that doesn't like to end up in a jam.
>I get a bad feeling about hashing to get around an 
> immediate problem. I
> also question the value of an export this large. Have you timed the
> re-import? It might take so long as to not be of practical value.
>My approach in this situation would be to first design how 
> I want my
> system to be. Lay out the partitions I would like to have, 
> etc. Then create
> a plan to get to the situation I want. Conduct tests to see 
> what changes can
> be made in a reasonable time. First create a new partition 
> that will take
> the newly arriving data so your problem doesn't continue to 
> get worse. Then
> aim at creating new partitions one at a time that will split 
> the existing
> too-large partition into smaller partitions in a manageable effort per
> partition. You may end up creating one each weekend for awhile.
>My theory is being DBA of a really large database is 
> similar to being
> captain of a supertanker. You can't turn a large ship or a 
> large database
> quickly. You must anticipate problems a long distance off.
> 
> -Original Message-
> From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
> 
> I  created a range partition table with the last high_value 
> being maxvalue.
> The partition key is partner_id (an incremental sequence).
> Now pmax (the last partition) has become very big and gives 
> "snapshot too
> old.." while exporting.
> 
> Ultimate Plan
> =
> I realize that the ultimate solution will be the split the 
> partition pmax,
> into enough segments so that
> the new pmax has no data. Then at this point I will drop the 
> pmax partition.
> Therefore when new partners come I will add
> partitions accordingly.
> 
> Maybe
> =
> But I was thinking If I can make pmax into a hashed 
> subpartition, it will
> also distribute the data reasonably.
> I dont have to have the segments separated by the partner_id 
> because the
> data are not evenly distributed to the partners.
>   partner_id  , count(*)
>   1 4646545542
>   2 11164546466
>   ... and so on..
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition

[DENNIS WILLIAMS]

Ishwar
   One priority might be to get to 9.2. There are reasons Oracle has given
9.0.1 a short decommission date.
   One idea would be to export with a SQL statement. This would reduce the
size of your exports. Also, if you are mostly just adding new data (few
updates), you may be mostly just interested in exporting the new data.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, June 20, 2003 4:45 PM
To: Multiple recipients of list ORACLE-L


Jacques, the version is 9.0.1.1.1.
Dennis, Thanks for the confidence I totally agree with you about designing
and everything else.
Could there be a temporary solution to get successful exports until we
embark on this project.
we are in a 24x 7 environment.


Rgds
Ishwar.

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 4:30 PM
To: Multiple recipients of list ORACLE-L


Ishwar
   I am fairly new to partitions myself and must relearn the commands each
time I need to work with the partitions. So I will speak more to the
philosophy of a DBA that doesn't like to end up in a jam.
   I get a bad feeling about hashing to get around an immediate problem. I
also question the value of an export this large. Have you timed the
re-import? It might take so long as to not be of practical value.
   My approach in this situation would be to first design how I want my
system to be. Lay out the partitions I would like to have, etc. Then create
a plan to get to the situation I want. Conduct tests to see what changes can
be made in a reasonable time. First create a new partition that will take
the newly arriving data so your problem doesn't continue to get worse. Then
aim at creating new partitions one at a time that will split the existing
too-large partition into smaller partitions in a manageable effort per
partition. You may end up creating one each weekend for awhile.
   My theory is being DBA of a really large database is similar to being
captain of a supertanker. You can't turn a large ship or a large database
quickly. You must anticipate problems a long distance off.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis:

I  created a range partition table with the last high_value being maxvalue.
The partition key is partner_id (an incremental sequence).
Now pmax (the last partition) has become very big and gives "snapshot too
old.." while exporting.

Ultimate Plan
=
I realize that the ultimate solution will be the split the partition pmax,
into enough segments so that
the new pmax has no data. Then at this point I will drop the pmax partition.
Therefore when new partners come I will add
partitions accordingly.

Maybe
=
But I was thinking If I can make pmax into a hashed subpartition, it will
also distribute the data reasonably.
I dont have to have the segments separated by the partner_id because the
data are not evenly distributed to the partners.
  partner_id  , count(*)
  1 4646545542
  2 11164546466
  ... and so on..



Thanks very much for your help.

Ishwar

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be 

RE: Partition

[Ishwar Tewari]

Jacques, the version is 9.0.1.1.1.
Dennis, Thanks for the confidence I totally agree with you about designing
and everything else.
Could there be a temporary solution to get successful exports until we
embark on this project.
we are in a 24x 7 environment.


Rgds
Ishwar.

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 4:30 PM
To: Multiple recipients of list ORACLE-L


Ishwar
   I am fairly new to partitions myself and must relearn the commands each
time I need to work with the partitions. So I will speak more to the
philosophy of a DBA that doesn't like to end up in a jam.
   I get a bad feeling about hashing to get around an immediate problem. I
also question the value of an export this large. Have you timed the
re-import? It might take so long as to not be of practical value.
   My approach in this situation would be to first design how I want my
system to be. Lay out the partitions I would like to have, etc. Then create
a plan to get to the situation I want. Conduct tests to see what changes can
be made in a reasonable time. First create a new partition that will take
the newly arriving data so your problem doesn't continue to get worse. Then
aim at creating new partitions one at a time that will split the existing
too-large partition into smaller partitions in a manageable effort per
partition. You may end up creating one each weekend for awhile.
   My theory is being DBA of a really large database is similar to being
captain of a supertanker. You can't turn a large ship or a large database
quickly. You must anticipate problems a long distance off.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis:

I  created a range partition table with the last high_value being maxvalue.
The partition key is partner_id (an incremental sequence).
Now pmax (the last partition) has become very big and gives "snapshot too
old.." while exporting.

Ultimate Plan
=
I realize that the ultimate solution will be the split the partition pmax,
into enough segments so that
the new pmax has no data. Then at this point I will drop the pmax partition.
Therefore when new partners come I will add
partitions accordingly.

Maybe
=
But I was thinking If I can make pmax into a hashed subpartition, it will
also distribute the data reasonably.
I dont have to have the segments separated by the partner_id because the
data are not evenly distributed to the partners.
  partner_id  , count(*)
  1 4646545542
  2 11164546466
  ... and so on..



Thanks very much for your help.

Ishwar

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXA

RE: Partition

[DENNIS WILLIAMS]

Ishwar
   I am fairly new to partitions myself and must relearn the commands each
time I need to work with the partitions. So I will speak more to the
philosophy of a DBA that doesn't like to end up in a jam. 
   I get a bad feeling about hashing to get around an immediate problem. I
also question the value of an export this large. Have you timed the
re-import? It might take so long as to not be of practical value.
   My approach in this situation would be to first design how I want my
system to be. Lay out the partitions I would like to have, etc. Then create
a plan to get to the situation I want. Conduct tests to see what changes can
be made in a reasonable time. First create a new partition that will take
the newly arriving data so your problem doesn't continue to get worse. Then
aim at creating new partitions one at a time that will split the existing
too-large partition into smaller partitions in a manageable effort per
partition. You may end up creating one each weekend for awhile.
   My theory is being DBA of a really large database is similar to being
captain of a supertanker. You can't turn a large ship or a large database
quickly. You must anticipate problems a long distance off.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, June 20, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis:

I  created a range partition table with the last high_value being maxvalue.
The partition key is partner_id (an incremental sequence).
Now pmax (the last partition) has become very big and gives "snapshot too
old.." while exporting.

Ultimate Plan
=
I realize that the ultimate solution will be the split the partition pmax,
into enough segments so that
the new pmax has no data. Then at this point I will drop the pmax partition.
Therefore when new partners come I will add
partitions accordingly.

Maybe
=
But I was thinking If I can make pmax into a hashed subpartition, it will
also distribute the data reasonably.
I dont have to have the segments separated by the partner_id because the
data are not evenly distributed to the partners.
  partner_id  , count(*)
  1 4646545542
  2 11164546466
  ... and so on..



Thanks very much for your help.

Ishwar

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5

RE: Partition

[Jacques Kilchoer]

When you say 9i, do you mean 9.0.1 or 9.2.0? In 9.2.0 You can have list subpartitions.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2153058

> -Original Message-
> From: Ishwar Tewari [mailto:[EMAIL PROTECTED]
> 
> I  created a range partition table with the last high_value 
> being maxvalue.
> The partition key is partner_id (an incremental sequence).
> Now pmax (the last partition) has become very big and gives 
> "snapshot too
> old.." while exporting.
> 
> Ultimate Plan
> =
> I realize that the ultimate solution will be the split the 
> partition pmax,
> into enough segments so that
> the new pmax has no data. Then at this point I will drop the 
> pmax partition.
> Therefore when new partners come I will add
> partitions accordingly.
> 
> Maybe
> =
> But I was thinking If I can make pmax into a hashed 
> subpartition, it will
> also distribute the data reasonably.
> I dont have to have the segments separated by the partner_id 
> because the
> data are not evenly distributed to the partners.
>   partner_id  , count(*)
>   1 4646545542
>   2 11164546466
>   ... and so on..
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition

[Ishwar Tewari]

The version is 9i.


-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition

[Ishwar Tewari]

Hey Dennis:

I  created a range partition table with the last high_value being maxvalue.
The partition key is partner_id (an incremental sequence).
Now pmax (the last partition) has become very big and gives "snapshot too
old.." while exporting.

Ultimate Plan
=
I realize that the ultimate solution will be the split the partition pmax,
into enough segments so that
the new pmax has no data. Then at this point I will drop the pmax partition.
Therefore when new partners come I will add
partitions accordingly.

Maybe
=
But I was thinking If I can make pmax into a hashed subpartition, it will
also distribute the data reasonably.
I dont have to have the segments separated by the partner_id because the
data are not evenly distributed to the partners.
  partner_id  , count(*)
  1 4646545542
  2 11164546466
  ... and so on..



Thanks very much for your help.

Ishwar

-Original Message-
DENNIS WILLIAMS
Sent: Friday, June 20, 2003 3:10 PM
To: Multiple recipients of list ORACLE-L


Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition

[DENNIS WILLIAMS]

Ishwar - What Oracle version. Just off the top of my head, I think that 8i
only allows the subpartition to be hashed. What is your goal?

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, June 20, 2003 12:00 PM
To: Multiple recipients of list ORACLE-L


Hey,

Can anyone say if there is a way to breakup an existing partition into
subpartitons. The partition contains data.


Thx
Ishwar.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ishwar Tewari
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition recovery question

[Johnston, Tim]

Hi Dennis...

  Sorry for the late reply but I'm just reading this now...  I've faced
similar issues in my environment...  In the future, I think the following
will work for you:

Create a standalone table of the same definition as the partitioned table
Exchange the partition that belongs to the missing datafile with this new
table
Drop the table (which is now a stand alone table residing in the missing
datafile)
Drop the partition (which is located where you placed the temp table)

Sorry it wasn't sooner...

Tim
 

-Original Message-
Sent: Friday, February 21, 2003 5:35 PM
To: Multiple recipients of list ORACLE-L


Thanks Darrell. I ended up moving the data, dropping and recreating the
table, and moving the data back. 
   At least I received more ideas from this list than from Oracle Support
(zero). Since I didn't have an active error, it got rated a priority 4. Oh
well, I'm just glad to have that over with, hopefully the load this weekend
will be problem-free. IT'S MILLER TIME!!

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, February 21, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis,

You brought up an interesting situation so I had to go test it.  I'll
provide my test case in a second, but the summary is that I would copy or
export this table.  You should still be able to read from and write to this
table, including the partition which belongs in the deleted datafile.  If,
since datafile dropped, your database has been shutdown, you should still be
able to read from and write to this table.  In the after shutdown scenario,
I saw that newly inserted records we're going into the next highest range
partition.  Basically, this confirms that you don't seem to have a problem
with that table.  HOWEVER, I'm not sure I'd be comfortable with that and
since you can read from it, I would either export it or copy it using create
table as select ...  and drop this original table.  Even though while
testing I couldn't find a circumstance where I lose access to this table, it
still may be in an unstable condition.

Here's what I did...
Create 3 tablespaces (testdata1, testdata2, testdata3), each locally managed
uniform size 1M.
Then create table with 3 range partitions, 1 partition in each tablespace.
The table was partitioned by date, basically p1 max date of 1-01-2001, p2
max date of 1-01-2002, and p3 max date of 1-01-2003.  Then I put data in
parts 1 and 3.
I then went to the OS and removed the file for tablespace testdata2, which
contained empty partition part2.
At this point I could read/write to table including date range covered in
partition p2.
Now, I alter database datafile '/u02/oradata/DBID/testdata2.dbf' offline
drop.
Shutdown and restart database.
Now, I can still read/write to table including date range which was covered
in p2, but that data is now going into partition p3.
((ie...  after restart, then analyze compute,
SQL> select partition_name, num_rows from user_tab_partitions;

PARTITION_NAME   NUM_ROWS
-- --
P1  13752
P3371
))

Now, drop tablespace testdata2  (which used to contain partition p2).
Now, can still read from and write to table ok and no reference of partition
p2 in user_tab_parts.  Also, using dbastudio, I do a show object ddl, and it
also shows no reference to partition p2.  It seems to be legitimately and
safely gone.
But, personnally, I wouldn't trust this for long term production use.  I
would go ahead and copy or export the table.

Thanks,
Darrell




>>> [EMAIL PROTECTED] 02/21/03 08:19AM >>>
I lost a data file that contained the tablespace for an empty partition. I
dropped the datafile from Oracle, and the table itself seems okay, but I'm
wondering what I can do with that partition. Can I simply merge that
partition with another partition? Any ideas especially if you've encountered
a similar situation would be welcome.
 
Oracle 8.1.6
Alpha Tru64
Range partitions
 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatci

RE: Partition recovery question

[Darrell Landrum]

No problem, thanks for the feedback. 
I learned as well!

>>> [EMAIL PROTECTED] 02/21/03 04:34PM >>>
Thanks Darrell. I ended up moving the data, dropping and recreating the
table, and moving the data back. 
   At least I received more ideas from this list than from Oracle Support
(zero). Since I didn't have an active error, it got rated a priority 4. Oh
well, I'm just glad to have that over with, hopefully the load this weekend
will be problem-free. IT'S MILLER TIME!!

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, February 21, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis,

You brought up an interesting situation so I had to go test it.  I'll
provide my test case in a second, but the summary is that I would copy or
export this table.  You should still be able to read from and write to this
table, including the partition which belongs in the deleted datafile.  If,
since datafile dropped, your database has been shutdown, you should still be
able to read from and write to this table.  In the after shutdown scenario,
I saw that newly inserted records we're going into the next highest range
partition.  Basically, this confirms that you don't seem to have a problem
with that table.  HOWEVER, I'm not sure I'd be comfortable with that and
since you can read from it, I would either export it or copy it using create
table as select ...  and drop this original table.  Even though while
testing I couldn't find a circumstance where I lose access to this table, it
still may be in an unstable condition.

Here's what I did...
Create 3 tablespaces (testdata1, testdata2, testdata3), each locally managed
uniform size 1M.
Then create table with 3 range partitions, 1 partition in each tablespace.
The table was partitioned by date, basically p1 max date of 1-01-2001, p2
max date of 1-01-2002, and p3 max date of 1-01-2003.  Then I put data in
parts 1 and 3.
I then went to the OS and removed the file for tablespace testdata2, which
contained empty partition part2.
At this point I could read/write to table including date range covered in
partition p2.
Now, I alter database datafile '/u02/oradata/DBID/testdata2.dbf' offline
drop.
Shutdown and restart database.
Now, I can still read/write to table including date range which was covered
in p2, but that data is now going into partition p3.
((ie...  after restart, then analyze compute,
SQL> select partition_name, num_rows from user_tab_partitions;

PARTITION_NAME   NUM_ROWS
-- --
P1  13752
P3371
))

Now, drop tablespace testdata2  (which used to contain partition p2).
Now, can still read from and write to table ok and no reference of partition
p2 in user_tab_parts.  Also, using dbastudio, I do a show object ddl, and it
also shows no reference to partition p2.  It seems to be legitimately and
safely gone.
But, personnally, I wouldn't trust this for long term production use.  I
would go ahead and copy or export the table.

Thanks,
Darrell




>>> [EMAIL PROTECTED] 02/21/03 08:19AM >>>
I lost a data file that contained the tablespace for an empty partition. I
dropped the datafile from Oracle, and the table itself seems okay, but I'm
wondering what I can do with that partition. Can I simply merge that
partition with another partition? Any ideas especially if you've encountered
a similar situation would be welcome.
 
Oracle 8.1.6
Alpha Tru64
Range partitions
 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.

Re: Partition recovery question

[Darrell Landrum]

Alter tablespace ... offline didn't work in testing I did in 8.1.6, as it wants to 
checkpoint and update the datafiles in that tablespace when taking it to offline 
status.
First alter database datafile '/missingdatafilename_goes_here' offline drop, then 
offline the tablespace.

>>> [EMAIL PROTECTED] 02/21/03 04:15PM >>>
Dennis,

How about this strategy?

(1) Find out the tablespace the partition is in.
(2) ALTER TABLESPACE ... OFFLINE;
(3) ALTER TABLE ... DROP PARTITION ...

HTH.

Arup


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 21, 2003 1:55 PM


> Thanks Arup
>I have tried some experiments on our test system. It is that old
problem
> of Oracle having so much integrity that you can't do something simple like
> that. When you try to just drop the partition, Oracle says "woops I can't
> read the file that partition is stored on".
>Looks as if my only choice is to move the data to a temporary table,
> rebuild the table and partitions, and move the data back. I'm guessing
that
> will be faster than export/import. Fortunately it isn't an extremely large
> table.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
>
>
> -Original Message-
> Sent: Friday, February 21, 2003 9:50 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Why not just drop the partition?
>
> Arup
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, February 21, 2003 9:19 AM
>
>
> > I lost a data file that contained the tablespace for an empty partition.
I
> > dropped the datafile from Oracle, and the table itself seems okay, but
I'm
> > wondering what I can do with that partition. Can I simply merge that
> > partition with another partition? Any ideas especially if you've
> encountered
> > a similar situation would be welcome.
> >
> > Oracle 8.1.6
> > Alpha Tru64
> > Range partitions
> >
> >
> >
> >
> > Dennis Williams
> > DBA, 40%OCP, 100% DBA
> > Lifetouch, Inc.
> > [EMAIL PROTECTED] 
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net 
> > --
> > Author: DENNIS WILLIAMS
> >   INET: [EMAIL PROTECTED] 
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net 
> --
> Author: Arup Nanda
>   INET: [EMAIL PROTECTED] 
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net 
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED] 
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fat

RE: Partition recovery question

[DENNIS WILLIAMS]

Thanks Darrell. I ended up moving the data, dropping and recreating the
table, and moving the data back. 
   At least I received more ideas from this list than from Oracle Support
(zero). Since I didn't have an active error, it got rated a priority 4. Oh
well, I'm just glad to have that over with, hopefully the load this weekend
will be problem-free. IT'S MILLER TIME!!

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, February 21, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Hey Dennis,

You brought up an interesting situation so I had to go test it.  I'll
provide my test case in a second, but the summary is that I would copy or
export this table.  You should still be able to read from and write to this
table, including the partition which belongs in the deleted datafile.  If,
since datafile dropped, your database has been shutdown, you should still be
able to read from and write to this table.  In the after shutdown scenario,
I saw that newly inserted records we're going into the next highest range
partition.  Basically, this confirms that you don't seem to have a problem
with that table.  HOWEVER, I'm not sure I'd be comfortable with that and
since you can read from it, I would either export it or copy it using create
table as select ...  and drop this original table.  Even though while
testing I couldn't find a circumstance where I lose access to this table, it
still may be in an unstable condition.

Here's what I did...
Create 3 tablespaces (testdata1, testdata2, testdata3), each locally managed
uniform size 1M.
Then create table with 3 range partitions, 1 partition in each tablespace.
The table was partitioned by date, basically p1 max date of 1-01-2001, p2
max date of 1-01-2002, and p3 max date of 1-01-2003.  Then I put data in
parts 1 and 3.
I then went to the OS and removed the file for tablespace testdata2, which
contained empty partition part2.
At this point I could read/write to table including date range covered in
partition p2.
Now, I alter database datafile '/u02/oradata/DBID/testdata2.dbf' offline
drop.
Shutdown and restart database.
Now, I can still read/write to table including date range which was covered
in p2, but that data is now going into partition p3.
((ie...  after restart, then analyze compute,
SQL> select partition_name, num_rows from user_tab_partitions;

PARTITION_NAME   NUM_ROWS
-- --
P1  13752
P3371
))

Now, drop tablespace testdata2  (which used to contain partition p2).
Now, can still read from and write to table ok and no reference of partition
p2 in user_tab_parts.  Also, using dbastudio, I do a show object ddl, and it
also shows no reference to partition p2.  It seems to be legitimately and
safely gone.
But, personnally, I wouldn't trust this for long term production use.  I
would go ahead and copy or export the table.

Thanks,
Darrell




>>> [EMAIL PROTECTED] 02/21/03 08:19AM >>>
I lost a data file that contained the tablespace for an empty partition. I
dropped the datafile from Oracle, and the table itself seems okay, but I'm
wondering what I can do with that partition. Can I simply merge that
partition with another partition? Any ideas especially if you've encountered
a similar situation would be welcome.
 
Oracle 8.1.6
Alpha Tru64
Range partitions
 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-53

Re: Partition recovery question

[Arup Nanda]

Dennis,

How about this strategy?

(1) Find out the tablespace the partition is in.
(2) ALTER TABLESPACE ... OFFLINE;
(3) ALTER TABLE ... DROP PARTITION ...

HTH.

Arup


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 21, 2003 1:55 PM


> Thanks Arup
>I have tried some experiments on our test system. It is that old
problem
> of Oracle having so much integrity that you can't do something simple like
> that. When you try to just drop the partition, Oracle says "woops I can't
> read the file that partition is stored on".
>Looks as if my only choice is to move the data to a temporary table,
> rebuild the table and partitions, and move the data back. I'm guessing
that
> will be faster than export/import. Fortunately it isn't an extremely large
> table.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
>
> -Original Message-
> Sent: Friday, February 21, 2003 9:50 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Dennis,
>
> Why not just drop the partition?
>
> Arup
>
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, February 21, 2003 9:19 AM
>
>
> > I lost a data file that contained the tablespace for an empty partition.
I
> > dropped the datafile from Oracle, and the table itself seems okay, but
I'm
> > wondering what I can do with that partition. Can I simply merge that
> > partition with another partition? Any ideas especially if you've
> encountered
> > a similar situation would be welcome.
> >
> > Oracle 8.1.6
> > Alpha Tru64
> > Range partitions
> >
> >
> >
> >
> > Dennis Williams
> > DBA, 40%OCP, 100% DBA
> > Lifetouch, Inc.
> > [EMAIL PROTECTED]
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: DENNIS WILLIAMS
> >   INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Arup Nanda
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition recovery question

[Lyndon Tiu]

I like that 40% OCP, 100% DBA.

-- 
Lyndon Tiu


Quoting DENNIS WILLIAMS <[EMAIL PROTECTED]>:

> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Lyndon Tiu
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition recovery question

[Darrell Landrum]

Hey Dennis,

You brought up an interesting situation so I had to go test it.  I'll provide my test 
case in a second, but the summary is that I would copy or export this table.  You 
should still be able to read from and write to this table, including the partition 
which belongs in the deleted datafile.  If, since datafile dropped, your database has 
been shutdown, you should still be able to read from and write to this table.  In the 
after shutdown scenario, I saw that newly inserted records we're going into the next 
highest range partition.  Basically, this confirms that you don't seem to have a 
problem with that table.  HOWEVER, I'm not sure I'd be comfortable with that and since 
you can read from it, I would either export it or copy it using create table as select 
...  and drop this original table.  Even though while testing I couldn't find a 
circumstance where I lose access to this table, it still may be in an unstable 
condition.

Here's what I did...
Create 3 tablespaces (testdata1, testdata2, testdata3), each locally managed uniform 
size 1M.
Then create table with 3 range partitions, 1 partition in each tablespace.  The table 
was partitioned by date, basically p1 max date of 1-01-2001, p2 max date of 1-01-2002, 
and p3 max date of 1-01-2003.  Then I put data in parts 1 and 3.
I then went to the OS and removed the file for tablespace testdata2, which contained 
empty partition part2.
At this point I could read/write to table including date range covered in partition p2.
Now, I alter database datafile '/u02/oradata/DBID/testdata2.dbf' offline drop.
Shutdown and restart database.
Now, I can still read/write to table including date range which was covered in p2, but 
that data is now going into partition p3.
((ie...  after restart, then analyze compute,
SQL> select partition_name, num_rows from user_tab_partitions;

PARTITION_NAME   NUM_ROWS
-- --
P1  13752
P3371
))

Now, drop tablespace testdata2  (which used to contain partition p2).
Now, can still read from and write to table ok and no reference of partition p2 in 
user_tab_parts.  Also, using dbastudio, I do a show object ddl, and it also shows no 
reference to partition p2.  It seems to be legitimately and safely gone.
But, personnally, I wouldn't trust this for long term production use.  I would go 
ahead and copy or export the table.

Thanks,
Darrell




>>> [EMAIL PROTECTED] 02/21/03 08:19AM >>>
I lost a data file that contained the tablespace for an empty partition. I
dropped the datafile from Oracle, and the table itself seems okay, but I'm
wondering what I can do with that partition. Can I simply merge that
partition with another partition? Any ideas especially if you've encountered
a similar situation would be welcome.
 
Oracle 8.1.6
Alpha Tru64
Range partitions
 



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition recovery question

[DENNIS WILLIAMS]

Thanks Arup
   I have tried some experiments on our test system. It is that old problem
of Oracle having so much integrity that you can't do something simple like
that. When you try to just drop the partition, Oracle says "woops I can't
read the file that partition is stored on". 
   Looks as if my only choice is to move the data to a temporary table,
rebuild the table and partitions, and move the data back. I'm guessing that
will be faster than export/import. Fortunately it isn't an extremely large
table. 
  
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, February 21, 2003 9:50 AM
To: Multiple recipients of list ORACLE-L


Dennis,

Why not just drop the partition?

Arup


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 21, 2003 9:19 AM


> I lost a data file that contained the tablespace for an empty partition. I
> dropped the datafile from Oracle, and the table itself seems okay, but I'm
> wondering what I can do with that partition. Can I simply merge that
> partition with another partition? Any ideas especially if you've
encountered
> a similar situation would be welcome.
>
> Oracle 8.1.6
> Alpha Tru64
> Range partitions
>
>
>
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition recovery question

[Arup Nanda]

Dennis,

Why not just drop the partition?

Arup


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 21, 2003 9:19 AM


> I lost a data file that contained the tablespace for an empty partition. I
> dropped the datafile from Oracle, and the table itself seems okay, but I'm
> wondering what I can do with that partition. Can I simply merge that
> partition with another partition? Any ideas especially if you've
encountered
> a similar situation would be welcome.
>
> Oracle 8.1.6
> Alpha Tru64
> Range partitions
>
>
>
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition and primary key

[M Rafiq]

Philippe

If you are using parallel clause in index creation/rebuilding you need more 
space in target tablespace as well as temp tablespace.

Regards
Rafiq








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Fri, 07 Feb 2003 08:09:25 -0800


You are running out of space in the SCOPE_MD_IDX tablespace.

i.e. your index won't fit.

It appears that you are also using up all the TEMP space.  Most
unusual, at least to me.  I've never seen both the tablespace and
the temp tablespace run out of space at the same time.

Jared

On Friday 07 February 2003 00:38, NGUYEN Philippe (Cetelem) wrote:
> Here a sample of my alert.log, SCOPE_MD_IDX is my index tablespace :
>
> ORA-1652: unable to extend temp segment by 320 in tablespace
> SCOPE_MD_IDX
> ORA-1652: unable to extend temp segment by 320 in tablespace
> TEMP
>
>
> 1. I've then created a primary key with DISABLE option
> 2. Build the indexes ---> succeeded
> 3. when I turned the constraint on --> OR-1652
>
> TIA
> Philippe


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: M Rafiq
 INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition and primary key

[Jared Still]

You are running out of space in the SCOPE_MD_IDX tablespace.

i.e. your index won't fit.  

It appears that you are also using up all the TEMP space.  Most
unusual, at least to me.  I've never seen both the tablespace and
the temp tablespace run out of space at the same time.

Jared

On Friday 07 February 2003 00:38, NGUYEN Philippe (Cetelem) wrote:
> Here a sample of my alert.log, SCOPE_MD_IDX is my index tablespace :
>
> ORA-1652: unable to extend temp segment by 320 in tablespace
> SCOPE_MD_IDX
> ORA-1652: unable to extend temp segment by 320 in tablespace   
> TEMP
>
>
> 1. I've then created a primary key with DISABLE option
> 2. Build the indexes ---> succeeded
> 3. when I turned the constraint on --> OR-1652
>
> TIA
> Philippe


Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition and primary key

[NGUYEN Philippe (Cetelem)]

Title: RE: Partition and primary key





Here a sample of my alert.log, SCOPE_MD_IDX is my index tablespace :


ORA-1652: unable to extend temp segment by 320 in tablespace    SCOPE_MD_IDX 
ORA-1652: unable to extend temp segment by 320 in tablespace    TEMP 


1. I've then created a primary key with DISABLE option
2. Build the indexes ---> succeeded
3. when I turned the constraint on --> OR-1652


TIA
Philippe

RE: Partition and primary key

[Jared . Still]

Well, you're getting close.

The full message looks like this:

ORA-01652,  "unable to extend temp segment by %s in tablespace %s"

The interesting part of the message appears where the '%s'  are.

Jared







"NGUYEN Philippe (Cetelem)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 02/06/2003 05:43 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
    cc: 
Subject:RE: Partition and primary key


Jared, 
UNABLE TO EXTENT TEMP SEGMENT BY ... 
-Message d'origine- 
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 
Date: mercredi 5 février 2003 21:50 
À: Multiple recipients of list ORACLE-L 
Objet: Re: Partition and primary key 

What is the exact error message, including text? 
It could be failing on the temp segment initially created in 
the index tablespace, and not necessarily running out 
of sort space. 
Jared 





"NGUYEN Philippe (Cetelem)" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
 02/05/2003 06:52 AM 
 Please respond to ORACLE-L 
  
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]> 
cc: 
Subject:Partition and primary key 

Hi List, 
We are using Oracle 8.1.7 (64 bits) with Solaris 8 
I have a big table with over 300 millions rows (10 Go), 
We have partinionned this table by range of date. 
When we try to create a constraint primary key (with dedicated 
tablespace), we fail each time because the database was unable to extend 
temp segment. 
1/ Instead of enlarging the temp tablespace undefinatly , is it possible 
to create this constraint for each partition so that it would reduce the 
amount of temp segment required ? 
2/ If not, creating an unique will also be efficient for join  ? 
Thanks in advance! 
Philippe 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services 
- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition and primary key

[NGUYEN Philippe (Cetelem)]

Title: RE: Partition and primary key





Jared,
UNABLE TO EXTENT TEMP SEGMENT BY ...


-Message d'origine-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Date: mercredi 5 février 2003 21:50
À: Multiple recipients of list ORACLE-L
Objet: Re: Partition and primary key



What is the exact error message, including text?


It could be failing on the temp segment initially created in
the index tablespace, and not necessarily running out
of sort space.


Jared







"NGUYEN Philippe (Cetelem)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 02/05/2003 06:52 AM
 Please respond to ORACLE-L


 
    To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
    cc: 
    Subject:    Partition and primary key



Hi List, 
We are using Oracle 8.1.7 (64 bits) with Solaris 8 
I have a big table with over 300 millions rows (10 Go), 
We have partinionned this table by range of date. 
When we try to create a constraint primary key (with dedicated 
tablespace), we fail each time because the database was unable to extend 
temp segment. 
1/ Instead of enlarging the temp tablespace undefinatly , is it possible 
to create this constraint for each partition so that it would reduce the 
amount of temp segment required ?
2/ If not, creating an unique will also be efficient for join  ? 
Thanks in advance! 
Philippe 
  



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California    -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition and primary key

[Jonathan Lewis]

Practical Oracle 8i   
Addison Wesley; ISBN: 0201715848


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates: 
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 06 February 2003 11:00


thank U Johnattan, which book are you talk about ? (isbn)



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition and primary key

[NGUYEN Philippe (Cetelem)]

Title: RE: Partition and primary key





thank U Johnattan, which book are you talk about ? (isbn)


-Message d'origine-
De: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
Date: mercredi 5 février 2003 19:20
À: Multiple recipients of list ORACLE-L
Objet: Re: Partition and primary key




First create the index UNUSABLE - which will
create each index as a single extent at virtually
no cost.


Then for rebuild each partition.


Than add the PK using the index.


(At least, that's what it says in my book ;)
I think you'll find a similar piece of advice in
the PL/SQL packages manual under the
PCXUTIL package.



Regards


Jonathan Lewis
http://www.jlcomp.demon.co.uk


Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )


UK___March 19th
USA_(FL)_May 2nd



Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )


USA_(CA, TX)_August



The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 05 February 2003 17:57



>Hi List,
>We are using Oracle 8.1.7 (64 bits) with Solaris 8
>
>I have a big table with over 300 millions rows (10 Go),
>We have partinionned this table by range of date.
>When we try to create a constraint primary key (with dedicated
tablespace),
>we fail each time because the database was unable to extend temp
segment.
>1/ Instead of enlarging the temp tablespace undefinatly , is it
possible to
>create this constraint for each partition so that it would reduce the
amount
>of temp segment required ?
>2/ If not, creating an unique will also be efficient for join  ?
>
>Thanks in advance!
>Philippe
>
>
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California    -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition and primary key

[Janardhana Babu Donga]

Title: Partition and primary key



You need 
to have 15 to 20gig temp space to do this, assuming you are 
equipartitioning the primary key index.

  -Original Message-From: NGUYEN Philippe (Cetelem) 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 05, 
  2003 6:53 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Partition and primary key
  Hi List, We are using Oracle 8.1.7 (64 
  bits) with Solaris 8 
  I have a big table with over 300 millions rows (10 Go), 
  We have partinionned this table by range of date. 
  When we try to create a constraint primary key (with 
  dedicated tablespace), we fail each time because the database was unable to 
  extend temp segment. 
  1/ Instead of enlarging the temp tablespace undefinatly , is 
  it possible to create this constraint for each partition so that it would 
  reduce the amount of temp segment required ?
  2/ If not, creating an unique will also be efficient for 
  join  ? 
  Thanks in advance! Philippe 

   

Re: Partition and primary key

[Jared . Still]

What is the exact error message, including text?

It could be failing on the temp segment initially created in
the index tablespace, and not necessarily running out
of sort space.

Jared






"NGUYEN Philippe (Cetelem)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 02/05/2003 06:52 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:Partition and primary key


Hi List, 
We are using Oracle 8.1.7 (64 bits) with Solaris 8 
I have a big table with over 300 millions rows (10 Go), 
We have partinionned this table by range of date. 
When we try to create a constraint primary key (with dedicated 
tablespace), we fail each time because the database was unable to extend 
temp segment. 
1/ Instead of enlarging the temp tablespace undefinatly , is it possible 
to create this constraint for each partition so that it would reduce the 
amount of temp segment required ?
2/ If not, creating an unique will also be efficient for join  ? 
Thanks in advance! 
Philippe 
  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition and primary key

[Jonathan Lewis]

First create the index UNUSABLE - which will
create each index as a single extent at virtually
no cost.

Then for rebuild each partition.

Than add the PK using the index.

(At least, that's what it says in my book ;)
I think you'll find a similar piece of advice in
the PL/SQL packages manual under the
PCXUTIL package.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

UK___March 19th
USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 05 February 2003 17:57


>Hi List,
>We are using Oracle 8.1.7 (64 bits) with Solaris 8
>
>I have a big table with over 300 millions rows (10 Go),
>We have partinionned this table by range of date.
>When we try to create a constraint primary key (with dedicated
tablespace),
>we fail each time because the database was unable to extend temp
segment.
>1/ Instead of enlarging the temp tablespace undefinatly , is it
possible to
>create this constraint for each partition so that it would reduce the
amount
>of temp segment required ?
>2/ If not, creating an unique will also be efficient for join  ?
>
>Thanks in advance!
>Philippe
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Info

[Govind.Arumugam]

You may use the following query will identify the 'highest' partition by date range.

select partition_name, partition_position , high_value, tablespace_name
from dba_tab_partitions
where table_name = ''
and table_owner = ''
and partition_position = ( select max(partition_position)
from dba_tab_partitions
where table_name = ''
and table_owner = '' )

You can store the high_value in another table and start manipulating the way 
you want such as:

select max(add_months( to_date( substr(high_value,3,19), 'S-MM-DD HH24:MI:SS' ), 1 
))
into new_high_value
from temp_table

I don't know how we can directly manipulate HIGH_VALUE column in dba_tab_partitions.  
But I have tried the above in a PL/SQL program and it worked.

Hope this helps.

-Original Message-
Sent: Monday, December 02, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L


DBA_TAB_PARTITIONS

-Original Message-
Sent: Monday, December 02, 2002 3:49 PM
To: Multiple recipients of list ORACLE-L


Hello everybody.
I have a number of tables with range by date partitions.  I need to
write a script to monitor the latest partitions and send me an e-mail if
it's close to a current date.

I am just looking for tables or views that will help me find out the
date of the last partition.

Sergei.




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sergei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Info : thank you

[Sergei]

Got it

-Original Message-
Neyman
Sent: Monday, December 02, 2002 1:45 PM
To: Multiple recipients of list ORACLE-L

Sergei

Check HIGH_VALUE column in DBA_TAB_PARTITIONS and compare it to current
date.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, December 02, 2002 3:48 PM


> Hello everybody.
> I have a number of tables with range by date partitions.  I need to
> write a script to monitor the latest partitions and send me an e-mail
if
> it's close to a current date.
>
> I am just looking for tables or views that will help me find out the
> date of the last partition.
>
> Sergei.
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sergei
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sergei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Info

[Khedr, Waleed]

DBA_TAB_PARTITIONS

-Original Message-
Sent: Monday, December 02, 2002 3:49 PM
To: Multiple recipients of list ORACLE-L


Hello everybody.
I have a number of tables with range by date partitions.  I need to
write a script to monitor the latest partitions and send me an e-mail if
it's close to a current date.

I am just looking for tables or views that will help me find out the
date of the last partition.

Sergei.




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sergei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition Info

[Igor Neyman]

Sergei

Check HIGH_VALUE column in DBA_TAB_PARTITIONS and compare it to current
date.

Igor Neyman, OCP DBA
[EMAIL PROTECTED]



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, December 02, 2002 3:48 PM


> Hello everybody.
> I have a number of tables with range by date partitions.  I need to
> write a script to monitor the latest partitions and send me an e-mail if
> it's close to a current date.
>
> I am just looking for tables or views that will help me find out the
> date of the last partition.
>
> Sergei.
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Sergei
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Question

[Veronica Levin]

I agree too, 
You have to know what is best for you,
I just partitioned several tables by date value... left the "historic"
records in one partition (let's say date < Jan 2002) and the rest of the
records in another partition.but that was because it was the best for
us, some users generate historic reports, and other users just update the
transactions of the day and generate reports of that day... 
Hope this helps,

Saludos,
Veronica Levin Enriquez
Compañía Cervecera de Nicaragua


-Mensaje original-
De: Mark Richard [mailto:mrichard@;transurban.com.au]
Enviado el: Tuesday, November 05, 2002 4:09 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Partition Question


I agree...

What are you trying to accomplish with partitioning?  Partitioning by year
/ month / day / whatever can make it easy to truncate / archive old data.
The only trick is to create new partitions before they are required.

Another goal of partitioning may be query execution.  You might partition a
table by a certain column what is frequently stored in a where clause.
This might restrict the query to a partition rather than the entire table
and (depending on the query) could give a performance gain.

If you are lucky partitioning will achieve both, if you are unlucky
partitioning will just introduce a maintenance hassle.  Think about why you
want to partition the table and what you expect to gain by doing it.
Whatever you do, don't partition simply because you can.

Regards,
 Mark.



 

Don Jerman

<[EMAIL PROTECTED]   To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>   
ate.nc.us>cc:

Sent by:  Subject: Re: Partition
Question   
[EMAIL PROTECTED]

m

 

 

05/11/2002

05:54

Please respond

to ORACLE-L

 

 





It depends on your reason for partitioning -- if you mean to drop a
partition in
the future (to roll off the 1999 data or whatever) then the ID range is
potentially a valid approach, as long as ID is serial.  If you just want to
put
chunks on different disk volumes, you could use the type or even a hash
partitioning scheme.  It's down to what you're trying to accomplish, and
what is
good for one partition key is probably bad or neutral for the other.


Hamid Alavi wrote:

> Hi List,
>
> I have a question regarding partitioning: If I want to partition a table
> which strategy is better, like do i have to use a value which from first
day
> of using this table all those partion is using or just using first
partion,
> then second etc.
> E.G:
> If I do partion tableA based on ID range 1000, so for few month the
only
> first partion of this table will be used then second partion, but if I
> partion it on Type (1,2,3,4,5) any record can be any of these type and
from
> first day all of the partions will be used.
> Just want to check with you guys which way is better for performance?
> THanks for HELP>
>
> Hamid Alavi
> Office 818 737-0526
> Cell818 416-5095
>
> === Confidentiality Statement ===
> The information contained in this message and any attachments is
> intended only for the use of the individual or entity to which it is
> addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL
> and exempt from disclosure under applicable law.  If you have received
> this message in error, you are prohibited from copying, distributing, or
> using the information.  Please contact the sender immediately by return
> e-mail and delete the original message from your system.
> = End Confidentiality Statement =
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Hamid Alavi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
(See attached file: djerman.vcf)


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>&g

Re: Partition Question

[Mark Richard]

I agree...

What are you trying to accomplish with partitioning?  Partitioning by year
/ month / day / whatever can make it easy to truncate / archive old data.
The only trick is to create new partitions before they are required.

Another goal of partitioning may be query execution.  You might partition a
table by a certain column what is frequently stored in a where clause.
This might restrict the query to a partition rather than the entire table
and (depending on the query) could give a performance gain.

If you are lucky partitioning will achieve both, if you are unlucky
partitioning will just introduce a maintenance hassle.  Think about why you
want to partition the table and what you expect to gain by doing it.
Whatever you do, don't partition simply because you can.

Regards,
 Mark.



   
 
Don Jerman 
 
<[EMAIL PROTECTED]   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
ate.nc.us>cc:  
 
Sent by:      Subject: Re: Partition Question  
 
[EMAIL PROTECTED]
 
m  
 
   
 
   
 
05/11/2002 
 
05:54  
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




It depends on your reason for partitioning -- if you mean to drop a
partition in
the future (to roll off the 1999 data or whatever) then the ID range is
potentially a valid approach, as long as ID is serial.  If you just want to
put
chunks on different disk volumes, you could use the type or even a hash
partitioning scheme.  It's down to what you're trying to accomplish, and
what is
good for one partition key is probably bad or neutral for the other.


Hamid Alavi wrote:

> Hi List,
>
> I have a question regarding partitioning: If I want to partition a table
> which strategy is better, like do i have to use a value which from first
day
> of using this table all those partion is using or just using first
partion,
> then second etc.
> E.G:
> If I do partion tableA based on ID range 1000, so for few month the
only
> first partion of this table will be used then second partion, but if I
> partion it on Type (1,2,3,4,5) any record can be any of these type and
from
> first day all of the partions will be used.
> Just want to check with you guys which way is better for performance?
> THanks for HELP>
>
> Hamid Alavi
> Office 818 737-0526
> Cell818 416-5095
>
> === Confidentiality Statement ===
> The information contained in this message and any attachments is
> intended only for the use of the individual or entity to which it is
> addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL
> and exempt from disclosure under applicable law.  If you have received
> this message in error, you are prohibited from copying, distributing, or
> using the information.  Please contact the sender immediately by return
> e-mail and delete the original message from your system.
> = End Confidentiality Statement =
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Hamid Alavi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT

Re: Partition Question

[Don Jerman]

It depends on your reason for partitioning -- if you mean to drop a partition in
the future (to roll off the 1999 data or whatever) then the ID range is
potentially a valid approach, as long as ID is serial.  If you just want to put
chunks on different disk volumes, you could use the type or even a hash
partitioning scheme.  It's down to what you're trying to accomplish, and what is
good for one partition key is probably bad or neutral for the other.


Hamid Alavi wrote:

> Hi List,
>
> I have a question regarding partitioning: If I want to partition a table
> which strategy is better, like do i have to use a value which from first day
> of using this table all those partion is using or just using first partion,
> then second etc.
> E.G:
> If I do partion tableA based on ID range 1000, so for few month the only
> first partion of this table will be used then second partion, but if I
> partion it on Type (1,2,3,4,5) any record can be any of these type and from
> first day all of the partions will be used.
> Just want to check with you guys which way is better for performance?
> THanks for HELP>
>
> Hamid Alavi
> Office 818 737-0526
> Cell818 416-5095
>
> === Confidentiality Statement ===
> The information contained in this message and any attachments is
> intended only for the use of the individual or entity to which it is
> addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL
> and exempt from disclosure under applicable law.  If you have received
> this message in error, you are prohibited from copying, distributing, or
> using the information.  Please contact the sender immediately by return
> e-mail and delete the original message from your system.
> = End Confidentiality Statement =
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Hamid Alavi
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

begin:vcard 
n:Jerman;Don
tel;work:919.508.1886
x-mozilla-html:TRUE
org:Database Management Service,Information Technology
version:2.1
email;internet:[EMAIL PROTECTED]
title:Database Administrator
adr;quoted-printable:;;Database Management Service,Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA
x-mozilla-cpt:;-9536
fn:Don Jerman
end:vcard

Re: Partition and Index Usage

[Mark Richard]

Larry,

Perhaps this is out of the question, but...

What about the possibility of creating a batch_month column and using that
to partition the table.  Leave the bitmap index on batch_date for those who
need it and let the other users go into the table using batch_month
= 'blah'.  Unfortunately I realise that this isn't a pure back-end fix, but
perhaps it is worth considering.  Depending on what you are querying
perhaps an aggregated table (materialized view even?) is the next step to
reduce the volume to something a little easier for Oracle - but that's
really getting into the end-user / application part of town.

Regards,
 Mark.

PS:  Sorry I couldn't provide a simple solution but I'm not sure that there
is one.



   

"Larry Elkins" 

   
.net>cc:   

Sent by: Subject: Partition and Index Usage

[EMAIL PROTECTED] 

om 

   

   

13/10/02 02:03 

Please respond 

to ORACLE-L

   

   





Listers,

I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's the
scenario.

8.1.7.4

Partitioned table, by month, on a date column called batch_date. 30 some
odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:

 WHERE CUST_ID = 12345 and
   Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')

I end up in some cases with a BITMAP MERGE operation, using the BMI indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and, the
batch date value is inclusive of *all* rows in that partition (batch_date
has no time component, ok, technically it's midnight). So, using the BMI on
batch date to merge with the BMI on cust id is wasted effort -- there will
be no rows in that partition outside of the date range specified, and all
rows in the partition are *in* that range -- the index on batch date does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of the BMI on batch date, and use just the BMI on
cust id and see substantial improvement. Obviously I would prefer to get
the
stats squared away as opposed to using a hint, especially since hinting
isn't feasible with the dynamic queries issued by the various ad-hoc tools
used.

Anyway, it just seems strange to me that the CBO, on occasion, not always,
will choose to include the usage of the index on batch date when it matches
the partition boundaries and will do nothing as far as filtering rows. Oh
yeah, since the upper boundary of the partition is defined as less than
TO_DATE('02012002','MMDD'), and the criteria would leave wiggle room in
there for dates on "01312002" that have a time component, I can change the
criteria to be "BATCH_DATE >= TO_DATE('01012002','MMDD') and BATCH_DATE
< TO_DATE('02012002','MMDD'). This would account for a time component
(though time component is midnight). But I still get the same BMI merge
with
batch date on the handful of sample queries exhibiting this behavior.

Oh well, off to dig into the stats and play with 10053 traces. Just curious
if someone has run into something similar. And yes, I could simply drop the
index altogether, but that wouldn't help the folks querying on just a
single
day.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-

RE: Partition and Index Usage

[Khedr, Waleed]

I think that there is another dimension to the problem that you may not be
aware of and that is even when you think that you succeeded to eliminate the
use of the index on the batch_date using a hint (no_index), Oracle still has
to go the table using the rowid (after conversion from bitmap) to filter on
the batch_date predicate. Probably you did not notice that since you have to
go there to pick up some other columns that are not included in the index.

But if you try: select count(cust_id) >>> using your hints >>you will find
it's still going to the table although nothing is needed from there.
The reason is it needs to filter on the batch_date.

Something else I need to correct (from my previous reply) and that is the
optimizer sometimes does not have to check on the predicates on the
partitioning key if it's concluded from partitioning and partitions
eliminations. But I see this happening only when it does table/table
partition segment full scan.

And here comes the question: why?

The only answer I can come with: When it's a full table/table partition scan
The optimizer is able to do the right thing since there is a direct relation
between the table, its partitions and the partition key. But in case of
local indexes, it's indirect relationship. The index is just a local index
which makes harder for the optimizer to optimize!

I'm not saying it is hard to do, I'm saying that the functionality may not
be there yet (8.1.7).
I do not have access to a 9i database now to compare.

Waleed



-Original Message-
Sent: Saturday, October 12, 2002 10:23 PM
To: Multiple recipients of list ORACLE-L


>  Hi Larry,
>
> First I would suggest doing daily partitioning and dropping the
> index on the
> batch_date.

That's been kicked around. It's not a bad idea -- it would make sure the
index, since it wouldn't exist, doesn't get in the way. FWIW, it's
partitioned on a monthly basis to fall more in line with bulk maintenance
operations that are sometimes performed. External feed discovers a problem
and resends a month? A simple exchange partition novalidate after the data
is loaded in staging and verified. The pruning is another benefit, but not
the only reason it's partitioned by month -- 99% of the queries are for
month, 2 months, quarter, year, etc. Going to a more granular level will
just result in more partitions being examined. Would we get better
performance? Maybe, maybe not. Would have to test, and will not be able to
build a full blown test for another month or so until an additional 4.2 TB
disk space comes on-line. And yeah, where *are* the aggregates ;-)

>
> Regarding your sql:  partitions eliminations never substitutes
> the necessity
> to validate any predicates on the partitioning key in the where clause.

And that was really the whole reason for posing this question -- why does
the CBO even consider that index, in this particular case where a month
range is specified, the same as the partition. It *should* know that the
batch date index can be no more or no less than what the partition
comprises. But the CBO obviously isn't thinking that way and is evaluating
the index selectivity just like it does all the other BMI's. And in some
cases decides to go ahead and include it, doing the bitmap merge with
multiple other BMI's on which criteria exists. Just thought it was odd that
it doesn't take it into consideration, seeing if the boundaries of the
criteria on the batch date are the same as the boundaries for the partition.
Probably a good reason for it -- it just escapes me what it might be. Maybe
to consider an index join between multiple BMI's avoiding hitting the table
at all, but it's not doing that.

>
> If it's not feasible to partition by day, I would drop the BMI on the
> batch_date and include the batch_date in the cust_id BMI (local index).

This is actually done in a couple of cases on some other tables, but more
for the reason of being able to resolve some specific queries against those
tables entirely in the index.

Anyway, good thoughts and suggestions, I appreciate it. Each of them could
address this specific issue.

I'm still going to go ahead and dig into the 10053 and stats. Try to get it
to include the batch date index when helpful, and avoid it when it's not. Or
I could always set the distinct keys to 1 or drop the index ;-). FWIW, this
isn't a huge problem -- the performance when it includes the batch date even
when a month is specified is still quite good, we just know it could be even
better. We use Usage Tracker from Ambeo, plus my ongoing monitoring, and the
query execution time numbers overall for the system are *extremely* good. We
just have that occasional ad-hoc query that could be a bit better (sometimes
a lot better, but it's not the batch date index that is causing problems on
those that really need some help ;-)). But we have identified the solutions
for those.

But yes, your suggestions are certainly things to keep in mind when
encountering the non-selective usage of 

RE: Partition and Index Usage

[Larry Elkins]

>  Hi Larry,
>
> First I would suggest doing daily partitioning and dropping the
> index on the
> batch_date.

That's been kicked around. It's not a bad idea -- it would make sure the
index, since it wouldn't exist, doesn't get in the way. FWIW, it's
partitioned on a monthly basis to fall more in line with bulk maintenance
operations that are sometimes performed. External feed discovers a problem
and resends a month? A simple exchange partition novalidate after the data
is loaded in staging and verified. The pruning is another benefit, but not
the only reason it's partitioned by month -- 99% of the queries are for
month, 2 months, quarter, year, etc. Going to a more granular level will
just result in more partitions being examined. Would we get better
performance? Maybe, maybe not. Would have to test, and will not be able to
build a full blown test for another month or so until an additional 4.2 TB
disk space comes on-line. And yeah, where *are* the aggregates ;-)

>
> Regarding your sql:  partitions eliminations never substitutes
> the necessity
> to validate any predicates on the partitioning key in the where clause.

And that was really the whole reason for posing this question -- why does
the CBO even consider that index, in this particular case where a month
range is specified, the same as the partition. It *should* know that the
batch date index can be no more or no less than what the partition
comprises. But the CBO obviously isn't thinking that way and is evaluating
the index selectivity just like it does all the other BMI's. And in some
cases decides to go ahead and include it, doing the bitmap merge with
multiple other BMI's on which criteria exists. Just thought it was odd that
it doesn't take it into consideration, seeing if the boundaries of the
criteria on the batch date are the same as the boundaries for the partition.
Probably a good reason for it -- it just escapes me what it might be. Maybe
to consider an index join between multiple BMI's avoiding hitting the table
at all, but it's not doing that.

>
> If it's not feasible to partition by day, I would drop the BMI on the
> batch_date and include the batch_date in the cust_id BMI (local index).

This is actually done in a couple of cases on some other tables, but more
for the reason of being able to resolve some specific queries against those
tables entirely in the index.

Anyway, good thoughts and suggestions, I appreciate it. Each of them could
address this specific issue.

I'm still going to go ahead and dig into the 10053 and stats. Try to get it
to include the batch date index when helpful, and avoid it when it’s not. Or
I could always set the distinct keys to 1 or drop the index ;-). FWIW, this
isn't a huge problem -- the performance when it includes the batch date even
when a month is specified is still quite good, we just know it could be even
better. We use Usage Tracker from Ambeo, plus my ongoing monitoring, and the
query execution time numbers overall for the system are *extremely* good. We
just have that occasional ad-hoc query that could be a bit better (sometimes
a lot better, but it’s not the batch date index that is causing problems on
those that really need some help ;-)). But we have identified the solutions
for those.

But yes, your suggestions are certainly things to keep in mind when
encountering the non-selective usage of the index on batch date. Just not
sure if it’s something we have the time to pursue right now. And if you
follow the classic example of when to composite partition, this table is
screaming for it -- range on batch date, hash on another column (no meaning
generated numeric value that is always specified).

>
> Regards,
>
> Waleed
>
>
> -Original Message-
> To: Multiple recipients of list ORACLE-L
> Sent: 10/12/02 12:03 PM
>
> Listers,
>
> I'll be digging into this a bit more, playing around with a 10053 trace,
> reviewing the stats, and trying to tie back why this occurs, but here's
> the
> scenario.
>
> 8.1.7.4
>
> Partitioned table, by month, on a date column called batch_date. 30 some
> odd
> million rows per partition, 750+ million rows altogether. Multiple local
> BMI's defined. A query of the form:
>
>  WHERE CUST_ID = 12345 and
>Batch_Date between TO_DATE('01012002','MMDD') and
> TO_DATE('01312002','MMDD')
>
> I end up in some cases with a BITMAP MERGE operation, using the BMI
> indexes
> on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
> criteria already results in partition pruning for just that month, and,
> the
> batch date value is inclusive of *all* rows in that partition
> (batch_date
> has no time component, ok, technically it's midnight). So, using the BMI
> on
> batch date to merge with the BMI on cust id is wasted effort -- there
> will
> be no rows in that partition outside of the date range specified, and
> all
> rows in the partition are *in* that range -- the index on batch date
> does
> not, and cannot, exclude any rows in the partiti

RE: Partition and Index Usage

[Khedr, Waleed]

 Hi Larry,

First I would suggest doing daily partitioning and dropping the index on the
batch_date.

Regarding your sql:  partitions eliminations never substitutes the necessity
to validate any predicates on the partitioning key in the where clause.

If it's not feasible to partition by day, I would drop the BMI on the
batch_date and include the batch_date in the cust_id BMI (local index).

Regards,

Waleed


-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 10/12/02 12:03 PM

Listers,

I'll be digging into this a bit more, playing around with a 10053 trace,
reviewing the stats, and trying to tie back why this occurs, but here's
the
scenario.

8.1.7.4

Partitioned table, by month, on a date column called batch_date. 30 some
odd
million rows per partition, 750+ million rows altogether. Multiple local
BMI's defined. A query of the form:

 WHERE CUST_ID = 12345 and
   Batch_Date between TO_DATE('01012002','MMDD') and
TO_DATE('01312002','MMDD')

I end up in some cases with a BITMAP MERGE operation, using the BMI
indexes
on both CUST_ID *and* BATCH_DATE. Now here's the rub, the batch date
criteria already results in partition pruning for just that month, and,
the
batch date value is inclusive of *all* rows in that partition
(batch_date
has no time component, ok, technically it's midnight). So, using the BMI
on
batch date to merge with the BMI on cust id is wasted effort -- there
will
be no rows in that partition outside of the date range specified, and
all
rows in the partition are *in* that range -- the index on batch date
does
not, and cannot, exclude any rows in the partition. I can use a NO_INDEX
hint to suppress the use of the BMI on batch date, and use just the BMI
on
cust id and see substantial improvement. Obviously I would prefer to get
the
stats squared away as opposed to using a hint, especially since hinting
isn't feasible with the dynamic queries issued by the various ad-hoc
tools
used.

Anyway, it just seems strange to me that the CBO, on occasion, not
always,
will choose to include the usage of the index on batch date when it
matches
the partition boundaries and will do nothing as far as filtering rows.
Oh
yeah, since the upper boundary of the partition is defined as less than
TO_DATE('02012002','MMDD'), and the criteria would leave wiggle room
in
there for dates on "01312002" that have a time component, I can change
the
criteria to be "BATCH_DATE >= TO_DATE('01012002','MMDD') and
BATCH_DATE
< TO_DATE('02012002','MMDD'). This would account for a time
component
(though time component is midnight). But I still get the same BMI merge
with
batch date on the handful of sample queries exhibiting this behavior.

Oh well, off to dig into the stats and play with 10053 traces. Just
curious
if someone has run into something similar. And yes, I could simply drop
the
index altogether, but that wouldn't help the folks querying on just a
single
day.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partition tables

[Cherie_Machler]

Jack,

Thanks for these great hints.   I have not seen lots of these before and
they have given me lots of ideas.

Here's a question that you've induced:   How do you determine what a good
value is for INITRANS?
What are the downsides of setting it too high?

Thanks,

Cherie Machler
Oracle DBA
Gelco Information Network


   
  
Jack Silvey
  
 
ahoo.com>cc:   
  
Sent by: Subject: RE: partition tables 
  
[EMAIL PROTECTED] 
  
om 
  
   
  
   
  
05/31/02 10:08 
  
AM 
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




Big (or should we just call you P?),

I have become somewhat experienced at manipulating
large partition tables since I have had to do it so
often (can you say poor initial design?). Export /
import is not the fastest way to go.

Here are some tips from the trenches:

1) You mention that each partition should have only
100,000 rows, but it might be beneficial for you to
focus more on the partition key. If you choose the
right partition key, queries will be able to do
partition pruning, where they can look at the data
dictionary and see that they only want to look at
certain partitions and not others. This is the big win
for partitioned tables - remove as much data from the
initial lookup as possible by skipping partitions.

2) Investigate "create table as select" with the
nologging option, in combination with the partition
exchange option. Let's assume that you want a
partition table with 10 partitions. You can create an
empty partition table with 10 partitions, CTAS 10 new
tables from your original table, and exchange the
partitions. After this, you will have a partitition
table full of data. Ain't it cool.

3) If you are sure of your data integrity, use the
"without validation" clause of the partition exchange.
Otherwise, Oracle will look at each and every row in
each and every partition when it is swapped in -
really slows things down.

4) Another way of creating a partition table from a
standalone is to create the empty partition table and
do a "insert /*+ nologging append parallel(a,12) */
into tablea a select /*+ parallel (b,12) /* from
tableb b;" and this will spawn off 12 parallel
processes for the select, 12 parallel processes for
the insert, use almost no rollback (appends the data)
and use almost no logging. This screams like a
banshee, very fast. Remember, *each* pq process will
write to its own extent, size your extents
accordingly.

5) Create bitmap partitioned indexes on your low
cardinality join columns (look at number of distinct
values / number of rows) - make sure and set your
sort_area_size wayy high (and set your
sort_area_retained_size to the same value - bug in
oracle with the two not being equal throwing a -600
error) but remember - *each* pq process gets its own
sort_area_size - don't run the box out of ram.

6) Don't create the indexes before you load - this
will fragment them and slow down your insert.

7) Remember to set your parallelism on your table back
to a reasonable level if you CTAS with pq - otherwise,
a high parallelism level on the table will tend to
make Oracle favor full table scans and hash or sort
joins over nested loops and index lookups. Same goes
with indexes - more PQ favors full index scans.

8) You can analyze all your partitions separate from
each other, and in tandem if you wish.

9) alter your index partitions "unusable" before you
load and then rebuild those partitions with the
"compute statistics" clause - this is faster and
optimi

RE: RE: partition tables

[Michael P Sale]

I can confirm that this is true and a good idea. Testing is also done in
this fasion. Besides that, an Oracle salesperson would gladly have you
pay more without having to do anything on their part or yours.  ;>)

Regards,

Michael Sale
Author: Oracle9i for Windows(R) 2000 Tips & Techniques
http://www.amazon.com/exec/obidos/ASIN/0072194626


-Original Message-
Carmichael
Sent: Friday, May 31, 2002 8:24 AM
To: Multiple recipients of list ORACLE-L


> One item I've learned about Oracle from our recent audit is that 
> if you have not licensed an option, but installed it they don't
really > get bent out of shape so long as your not using it.


I've had Oracle Support tell me to install EVERYTHING and just use what
I'm licensed for. The logic behind this is that when the developers test
the release, they test it compiled with everything in it. It's too
time-consuming to test every possible variation on options. So if you
install everything then you are at least working with an executable that
has been tested.

Rachel


--- [EMAIL PROTECTED] wrote:
> BigP,
> 
> You'll have to rebuild the table from scratch as a partitioned 
> table.  Yes you can expect a performance gain, based on the fact that 
> you partition it
> appropriately  Also, if you haven't already licensed the partitioning
> option
> from Oracle, or installed it, you will have to.
> 
> One item I've learned about Oracle from our recent audit is that 
> if you have not licensed an option, but installed it they don't really

> get bent out of shape
> so long as your not using it.
> 
> Dick Goulet
> 
> Reply Separator
> Author: "Khedr; Waleed" <[EMAIL PROTECTED]>
> Date:   5/30/2002 6:08 PM
> 
>
http://docs.oracle.com/cd_a87860/doc/server.817/a76965/c09parti.htm#4369
72
>

> 
> 
> -Original Message-
> Sent: Thursday, May 30, 2002 8:59 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi All ,
> We are thinking of converting one of huge table in to partition table 
> . What is best way to achieve this ? Is there any alter table clause
> that can
> do this or I will have to export ..recreate table with partition
> option and
> then import . Also how can I mentiod that partition should have only
> 10
> rows . For example after each 10 rows add another partition ?
> If I have 1000 rows in the table , should I expect some
> performance gain
> out of this 
>  
> Thanks ,
> BigP
>  
>  
> 
> 
>  
>  
> 
> 
>  
> 
> 
> 
href="http://docs.oracle.com/cd_a87860/doc/server.817/a76965/c09parti.ht
m#436972
>
">http://docs.oracle.com/cd_a87860/doc/server.817/a76965/c09parti.htm#43
6972
> 
> 
>  size=2>-Original Message-From: BigP 
>   [mailto:[EMAIL PROTECTED]]Sent: Thursday, May
> 30, 2002 
>   8:59 PMTo: Multiple recipients of list
> ORACLE-LSubject: 
>   partition tables
>   Hi All ,
>   We are thinking of converting one
> of huge 
>   table in to partition table .  What is best way to achieve
> this ? Is 
>   there any alter table clause that can do this or I will have to
> export 
>   ..recreate table with partition option and then import . Also how
> can I 
>   mentiod that partition should have only 10 rows . For example
> after each 
>   10 rows add another partition ?
>   If I have 1000 rows in the table ,
> should I 
>   expect some performance gain out of this 
>    
>   Thanks ,
>   BigP
>    
>size=2> 
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
> the message BODY, include a line containing: UNSUB ORACLE-L (or the 
> name of mailing list you want to be removed from).  You may also send 
> the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).

-- 
Please 

RE: partition tables

[Ron Rogers]

BigP,
  You stated that you would like to limit the number of rows in a
partition. The partitioning option uses a "range" function on a column
to determine what partition to place to data into. If you do not have a
column that is used in your where clause, you are going to have a
difficult time determining what data is placed in what partition. The
biggest performance is gained when Oracle can eliminate the partitions
that do not match the where clause and then applies the search criteria
to a small subset of data, ie: a partition. As an example, if all of
your data had a date field that was used in the where clause you could
partition the data by range on that column by year or year,month and
have the data divided into a years worth of data or a months worth of
data. When oracle is requested to search for data it would eliminate all
of the partitions that do not match the date in the where clause, thus
eliminating the majority of the partitions if not all but one
partition.
 I would suggest as others have that you read and understand the
workings of partitioning and the possible benefits gained.
Ron
ROR mª¿ªm

>>> [EMAIL PROTECTED] 05/31/02 10:33AM >>>
BigP - I agree with Dick that you will need to create your new
partitioned
table and copy the rows from your current table into it. Given your
questions, before you charge into partitioning, carefully study the
ways
partitioning can increase your performance. It isn't just some magic
pixie
dust that simply makes everything faster. Carefully study how the table
is
accessed. For example, I applied partitioning to two instances. For
one, the
performance gain was tremendous. Queries that had taken more than 2
minutes
to complete dropped to under 10 seconds. You could hear the users
cheering.
On another instance, there was no detectable performance gain and I
ended up
undoing the partitioning. Fortunately Oracle is pretty lenient in terms
of
letting you try the feature to make sure it will deliver performance
worth
the licensing fee.
Dennis Williams 
DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, May 30, 2002 7:59 PM
To: Multiple recipients of list ORACLE-L


Hi All ,
We are thinking of converting one of huge table in to partition table
.
What is best way to achieve this ? Is there any alter table clause that
can
do this or I will have to export ..recreate table with partition option
and
then import . Also how can I mentiod that partition should have only
10
rows . For example after each 10 rows add another partition ?
If I have 1000 rows in the table , should I expect some performance
gain
out of this 
 
Thanks ,
BigP
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partition tables

[Jack Silvey]

Big (or should we just call you P?),

I have become somewhat experienced at manipulating
large partition tables since I have had to do it so
often (can you say poor initial design?). Export /
import is not the fastest way to go.

Here are some tips from the trenches:

1) You mention that each partition should have only
100,000 rows, but it might be beneficial for you to
focus more on the partition key. If you choose the
right partition key, queries will be able to do
partition pruning, where they can look at the data
dictionary and see that they only want to look at
certain partitions and not others. This is the big win
for partitioned tables - remove as much data from the
initial lookup as possible by skipping partitions.

2) Investigate "create table as select" with the
nologging option, in combination with the partition
exchange option. Let's assume that you want a
partition table with 10 partitions. You can create an
empty partition table with 10 partitions, CTAS 10 new
tables from your original table, and exchange the
partitions. After this, you will have a partitition
table full of data. Ain't it cool. 

3) If you are sure of your data integrity, use the
"without validation" clause of the partition exchange.
Otherwise, Oracle will look at each and every row in
each and every partition when it is swapped in -
really slows things down.

4) Another way of creating a partition table from a
standalone is to create the empty partition table and
do a "insert /*+ nologging append parallel(a,12) */
into tablea a select /*+ parallel (b,12) /* from
tableb b;" and this will spawn off 12 parallel
processes for the select, 12 parallel processes for
the insert, use almost no rollback (appends the data)
and use almost no logging. This screams like a
banshee, very fast. Remember, *each* pq process will
write to its own extent, size your extents
accordingly.

5) Create bitmap partitioned indexes on your low
cardinality join columns (look at number of distinct
values / number of rows) - make sure and set your
sort_area_size wayy high (and set your
sort_area_retained_size to the same value - bug in
oracle with the two not being equal throwing a -600
error) but remember - *each* pq process gets its own
sort_area_size - don't run the box out of ram.

6) Don't create the indexes before you load - this
will fragment them and slow down your insert.

7) Remember to set your parallelism on your table back
to a reasonable level if you CTAS with pq - otherwise,
a high parallelism level on the table will tend to
make Oracle favor full table scans and hash or sort
joins over nested loops and index lookups. Same goes
with indexes - more PQ favors full index scans.

8) You can analyze all your partitions separate from
each other, and in tandem if you wish.

9) alter your index partitions "unusable" before you
load and then rebuild those partitions with the
"compute statistics" clause - this is faster and
optimizes your indexes. Bitmap indexes do not like to
be up while loading. Be advised, if someone tries to
query this table and they don't have
"skip_unusable_indexes=true" set in their session,
they will get an error. One way to set this parameter
in every session is to include it in a logon trigger
using "execute immediate 'alter session set
skip_unusable_indexes=true'" - HOWEVER, this will
change their execution plan to favor FTS since the
index is *not available*. Use caution. 

10) Create and rebuild your indexes in parallel.

11) Use a MAXVALUE partition - this will allow you to
load all data and catch that data that falls outside
the other partition ranges. If you don't have a
maxvalue partition, and you try to insert a row that
does not match the other partitions, you will get
"inserted value beyond highest legal partition key"
and your insert will fail and might stop your load.
You can always split the MAXVALUE partition later.

12) Put all your table partitions in one tablespace
and all your index partitions in another single
tablespace (each suitably striped, of course, and
respecting recovery plans.) This will allow you to
automate partition management (addition of new
partitions and dropping of old) if you need to and
manage your tablespace space more effectively.

13) Use the "monitoring" option on your partitions -
not all partitions change enough to be analyzed each
time necessarily, and this will tell you which ones
need it.

14) *do not* create your table with pctfree = 0 and
*do* create with healthy initrans value (we use 8). If
you have initrans of 2 and pctfree of 0, and you try
to insert/update the table with 3 or more parallel
processes, the ITL table (the thing that the processes
register with when they use the table)  cannot grow
(no space with pctfree 0) and the extra processes will
either wait or fail with a deadlock error. To change
pctfree you will have to rebuild the table. 

hth,

jack silvey







> -Original Message-
> Sent: Thursday, May 30, 2002 8:59 PM
> To: Multiple recipients of li

Re: partition tables

[Steven Lembark]

-- paquette stephane <[EMAIL PROTECTED]>

> You can use insert select , export/import, create as
> select to move data from a non-partitionned to a
> partitionned table.
>
> Partitionning helps in the management of large tables
> more than in speeding the queries.
> Will you delete data from that table one day ?
> Choose the partition key carefully.
>
> A partition with only 100 000 rows is pretty small.
> Since you have 10 000 000 rows in your table, you will
> have 100 partitions of 100 000 rows, it's way too many
> small partitions.

Depends on the use. If they have many queries for which
indexes don't help then locally managed part's w/ table
scans in parallel server might help. It also depends on
their unit of rolloff. In a near-realtime system being
able to offline/truncate a small partition every 3 minutes
can be a big help. I've dealt with databases that had
houly partitions for 7 days (though with more rows than
this, the count of partitions helped).

A lot of it comes down to how the primary key breaks
down and how granular the rolloff needs to be.

--
Steven Lembark   2930 W. Palmer
Workhorse Computing   Chicago, IL 60647
+1 800 762 1582
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Lembark
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partition tables

[DENNIS WILLIAMS]

BigP - I agree with Dick that you will need to create your new partitioned
table and copy the rows from your current table into it. Given your
questions, before you charge into partitioning, carefully study the ways
partitioning can increase your performance. It isn't just some magic pixie
dust that simply makes everything faster. Carefully study how the table is
accessed. For example, I applied partitioning to two instances. For one, the
performance gain was tremendous. Queries that had taken more than 2 minutes
to complete dropped to under 10 seconds. You could hear the users cheering.
On another instance, there was no detectable performance gain and I ended up
undoing the partitioning. Fortunately Oracle is pretty lenient in terms of
letting you try the feature to make sure it will deliver performance worth
the licensing fee.
Dennis Williams 
DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, May 30, 2002 7:59 PM
To: Multiple recipients of list ORACLE-L


Hi All ,
We are thinking of converting one of huge table in to partition table .
What is best way to achieve this ? Is there any alter table clause that can
do this or I will have to export ..recreate table with partition option and
then import . Also how can I mentiod that partition should have only 10
rows . For example after each 10 rows add another partition ?
If I have 1000 rows in the table , should I expect some performance gain
out of this 
 
Thanks ,
BigP
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: partition tables

[paquette stephane]

You can use insert select , export/import, create as
select to move data from a non-partitionned to a
partitionned table.

Partitionning helps in the management of large tables
more than in speeding the queries. 
Will you delete data from that table one day ?
Choose the partition key carefully. 

A partition with only 100 000 rows is pretty small.
Since you have 10 000 000 rows in your table, you will
have 100 partitions of 100 000 rows, it's way too many
small partitions.


 --- BigP <[EMAIL PROTECTED]> a écrit : > Hi
All ,
> We are thinking of converting one of huge table in
> to partition table .  What is best way to achieve
> this ? Is there any alter table clause that can do
> this or I will have to export ..recreate table with
> partition option and then import . Also how can I
> mentiod that partition should have only 10 rows
> . For example after each 10 rows add another
> partition ?
> If I have 1000 rows in the table , should I
> expect some performance gain out of this 
> 
> Thanks ,
> BigP
> 
> 
>  

=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]

___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partition tables

[Khedr, Waleed]

http://docs.oracle.com/cd_a87860/doc/server.817/a76965/c09parti.htm#436972

  -Original Message-From: BigP 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, May 30, 2002 
  8:59 PMTo: Multiple recipients of list ORACLE-LSubject: 
  partition tables
  Hi All ,
  We are thinking of converting one of huge 
  table in to partition table .  What is best way to achieve this ? Is 
  there any alter table clause that can do this or I will have to export 
  ..recreate table with partition option and then import . Also how can I 
  mentiod that partition should have only 10 rows . For example after each 
  10 rows add another partition ?
  If I have 1000 rows in the table , should I 
  expect some performance gain out of this 
   
  Thanks ,
  BigP
   
   

RE: Partition Exchange

[Jack Silvey]

Hello Holly,

Make sure and pay attention to the clause that allows
you to skip validation. Don't remember it right off
the top, but it will save you beaucoup time if you are
sure about your data.

Jack


--- "Deshpande, Kirti" <[EMAIL PROTECTED]>
wrote:
> It means partitioning a non-partitioned table using
> "exchange partition"
> mechanism, and creating non-partitioned table from
> partitions of a
> partitioned table. You can find detail info in the
> Database Administrator's
> Guide.
> 
> Regards,
> 
> - Kirti
> 
> -Original Message-
> Sent: Tuesday, May 21, 2002 9:18 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Dear all,
> What's the meaning of "Partition Exchange"?
> 
> tia,
> 
> Holly
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Deshpande, Kirti
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Exchange

[Deshpande, Kirti]

It means partitioning a non-partitioned table using "exchange partition"
mechanism, and creating non-partitioned table from partitions of a
partitioned table. You can find detail info in the Database Administrator's
Guide.

Regards,

- Kirti

-Original Message-
Sent: Tuesday, May 21, 2002 9:18 PM
To: Multiple recipients of list ORACLE-L


Dear all,
What's the meaning of "Partition Exchange"?

tia,

Holly

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition Exchange

[paquette stephane]

The alter table exchange partition lets you transfer
data from the partition of a partitioned table to a
non partitioned table. It changes the adress in the
data dictionnary, no data is moved, that's why it is
fast.

For example, I'm using it in a system to exchange old
data with new data. The new data is loaded in staging
tables (non-partitioned). When the data is cleansed
and validated, the staging tables are exchanged with
the target tables (partitionned).
The target tables are partitioned with only one
partition to be able to used the alter table exchange
partition statement.

More in the docs...

 --- [EMAIL PROTECTED] a écrit : > Dear all,
> What's the meaning of "Partition Exchange"?
> 
> tia,
> 
> Holly
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: 
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing). 

=
Stéphane Paquette
DBA Oracle, consultant entrepôt de données
Oracle DBA, datawarehouse consultant
[EMAIL PROTECTED]

___
Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français !
Yahoo! Mail : http://fr.mail.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition tables: Indexes

[Johnson Poovathummoottil]

There is SKIP_UNUSABLE_INDEXES option in SQLldr


--- "Rusnak, George A." <[EMAIL PROTECTED]>
wrote:
> Group,
> I have a partition that has < 1 million records that
> is joined to 4 other
> tables. In order to get an acceptable response time
> I added two additional
> indexes. The response time is now acceptable but the
> load time is
> unacceptable. I truncate the partition prior to the
> load. Is there any way
> to drop/set unusable the local index on the
> partition being loaded, load the
> data and then rebuild the local index? 
> If so PLEASE give me an example !! I am just about
> burned out on reading the
> docs !!!
> My table structure:
> CREATE TABLE scan_contract
>   (CONTRACT_BEGIN_DATE  DATE, 
>NSN  VARCHAR2(13) ,
>CONTRACT VARCHAR2(14) NOT NULL,
>CONTRACT_END_DATEDATE,
>FUTURE_EFF_DATE  DATE,
>FUTURE_SELL_PRICENUMBER(11,4),
>SELL_PRICE   NUMBER(8,2),
>UPDATE_DATE  DATE,
>DODAAC   VARCHAR2(6),
>VENDOR_NUMBERVARCHAR2(4),
>ITEM_UPC VARCHAR2(14),
>REGION_FFS   VARCHAR2(3)
>   )
>   partition by range (REGION_FFS)
> SUBPARTITION BY HASH(DODAAC)
> SUBPARTITIONS 10
>(partition CONTRACT_1 Values less than
> ('RGC')
>   tablespace contract_ffs_1,
> partition CONTRACT_2 Values less than
> ('RGD')
>   tablespace contract_ffs_2,
> partition CONTRACT_3 Values less than
> ('RGE')
>   tablespace contract_ffs_3,
> partition CONTRACT_4 Values less than
> ('RGF')
>   tablespace contract_ffs_4,
> partition CONTRACT_5 Values less than
> ('RGG')
>   tablespace contract_ffs_5,
> partition CONTRACT_6 Values less than
> ('RGH')
>   tablespace contract_ffs_6
>)
>   PCTFREE 10 
>   PCTUSED 40 
>   INITRANS 1 
>   MAXTRANS 255 
>   TABLESPACE scan_down_ts
>   NOLOGGING
>   STORAGE(INITIAL 5M  
>   NEXT 2M  
>   MINEXTENTS 1
>   MAXEXTENTS 99
>   PCTINCREASE 0);
> Create index scan_contract_idx1
> ON scan_contract(REGION_FFS, 
>  NSN,
>  dodaac) STORAGE (initial 10K)
>  LOCAL
>  (partition CONTRACT_1 TABLESPACE contract_ffs_1,
>   partition CONTRACT_2 TABLESPACE contract_ffs_2,
>   partition CONTRACT_3 TABLESPACE contract_ffs_3,
>   partition CONTRACT_4 TABLESPACE contract_ffs_4,
>   partition CONTRACT_5 TABLESPACE contract_ffs_5,
>   partition CONTRACT_6 TABLESPACE contract_ffs_6);
> Create index scan_contract_item_upc_idx
> ON scan_contract(ITEM_UPC) STORAGE (initial 25K)
>  LOCAL
>  (partition CONTRACT_1 TABLESPACE contract_ffs_1,
>   partition CONTRACT_2 TABLESPACE contract_ffs_2,
>   partition CONTRACT_3 TABLESPACE contract_ffs_3,
>   partition CONTRACT_4 TABLESPACE contract_ffs_4,
>   partition CONTRACT_5 TABLESPACE contract_ffs_5,
>   partition CONTRACT_6 TABLESPACE contract_ffs_6);
> Create index scan_contract_contract_idx
> ON scan_contract(CONTRACT) STORAGE (initial 25K)
> LOCAL
>  (partition CONTRACT_1 TABLESPACE contract_ffs_1,
>   partition CONTRACT_2 TABLESPACE contract_ffs_2,
>   partition CONTRACT_3 TABLESPACE contract_ffs_3,
>   partition CONTRACT_4 TABLESPACE contract_ffs_4,
>   partition CONTRACT_5 TABLESPACE contract_ffs_5,
>   partition CONTRACT_6 TABLESPACE contract_ffs_6);
> 
> TIA
> 
> Al Rusnak
> 804-734-8453
> [EMAIL PROTECTED]
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Rusnak, George A.
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Do You Yahoo!?
Listen to your Yahoo! Mail messages from any phone.
http://phone.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johnson Poovathummoottil
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: partition

[Wong, Bing]

That's right.
 
But I am curious, why you partitioned it for 
just 2 values?  How big is the table in terms of number of rows?  

 
Bing
 
 
 

  -Original Message-From: JOE TESTA 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001 
  8:10 AMTo: Multiple recipients of list ORACLE-LSubject: 
  Re: partition
  by default you can't change the rows part key.  
   
  do a alter table  enable row movement;
   
  then the row can move between partitions.
   
  joe
  >>> [EMAIL PROTECTED] 09/24/01 10:50AM 
  >>>
  Hello,
   
  I have a partitioned 
  table by range on a column like STATUS , where STATUS can be either say YES or 
  NO .
  i do my partitionning on 
  this STATUS column.
   
  When i do insert in this 
  table, data are correctly inserted in the right partition BUT if I want to 
  update the STATUS field, it returns me an error I found 
  ambiguous:
  "ORA-14402: updating 
  partition key column would cause a partition change"
   
  The "would" is confusing 
  , does this mean it won't do it anyhow or with some special keyword would move 
  it to the other partition.
   
  Thanks,
  Maya

RE: partition

[Cale, Rick T (Richard)]

In 
8.0.x it will NOT do the update. I think in 8i it will but not 
sure.
 
Rick

  -Original Message-From: Maya Kenner 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001 
  10:50 AMTo: Multiple recipients of list ORACLE-LSubject: 
  partition
  Hello,
   
  I have a 
  partitioned table by range on a column like STATUS , where STATUS can be 
  either say YES or NO .
  i do my 
  partitionning on this STATUS column.
   
  When i do insert 
  in this table, data are correctly inserted in the right partition BUT if I 
  want to update the STATUS field, it returns me an error I found 
  ambiguous:
  "ORA-14402: 
  updating partition key column would cause a partition 
  change"
   
  The "would" is 
  confusing , does this mean it won't do it anyhow or with some special keyword 
  would move it to the other partition.
   
  Thanks,
  Maya

RE: partition

[Libal, Ivo]

Alter 
table  enable row movement;
 
 
Regards
Ivo
 

  -Original Message-From: Maya Kenner 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, September 24, 2001 
  04:50 PMTo: Multiple recipients of list ORACLE-LSubject: 
  partition
  Hello,
   
  I have a 
  partitioned table by range on a column like STATUS , where STATUS can be 
  either say YES or NO .
  i do my 
  partitionning on this STATUS column.
   
  When i do insert 
  in this table, data are correctly inserted in the right partition BUT if I 
  want to update the STATUS field, it returns me an error I found 
  ambiguous:
  "ORA-14402: 
  updating partition key column would cause a partition 
  change"
   
  The "would" is 
  confusing , does this mean it won't do it anyhow or with some special keyword 
  would move it to the other partition.
   
  Thanks,
  Maya

Re: partition

[JOE TESTA]

by default you can't change the rows part key.  
 
do a alter table  enable row movement;
 
then the row can move between partitions.
 
joe
>>> [EMAIL PROTECTED] 09/24/01 10:50AM 
>>>
Hello,
 
I have a partitioned 
table by range on a column like STATUS , where STATUS can be either say YES or 
NO .
i do my 
partitionning on this STATUS column.
 
When i do insert in 
this table, data are correctly inserted in the right partition BUT if I want to 
update the STATUS field, it returns me an error I found 
ambiguous:
"ORA-14402: updating 
partition key column would cause a partition change"
 
The "would" is 
confusing , does this mean it won't do it anyhow or with some special keyword 
would move it to the other partition.
 
Thanks,
Maya

RE: Partition attached to Synonym

[Dave Morgan]

Hi All,
Once again sinking into the depths of Oracle code.

>   BUG INFO
>  
>
>   /  
>
>  Base 
>
>  Fixed In Ver: 9.0.2
>
>  Abstract: CANNOT DROP PARTITION IF ADDED VIA SYNONYM - ORA-2149


Still waiting for instructions on how to cleanup my data dictionary.

Oracle support can reproduce it and feel that's enough. 

Sigh 

Dave
-- 
Dave Morgan
DBA, Cybersurf
Office: 403 777 2000 ext 284
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Dave Morgan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: PARTITION attache to SYNONYM

[Jonathan Lewis]

Remind me,
Is it a locally managed tablespace ?
If so, get on to Oracle about the following idea.
a) Export the data from the extent
b) Use dbms_space_admin to make the non-existent
segment appear/disappear

If not, is there even an entry in UET$ for the
extent ?

Is this a standard partiitoned table, or a
partitioned IOT ? If standard, there MUST
be a segment, because dba_tab_partitions
CANNOT report a partition without joining
to the matching seg$ row.


Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 08 August 2001 20:00


|Jonathan and others,
|
|And some more information. I love my job, I love my job, ...
|
|
|I have 15 million rows taking up 840 MB in a tablespace
|that has no segments or extents (In a partition that sort of exists,
|or sort of not exists)
|
|The data is updatable and readable.
|
|
|Did I mention that I love my job.
|
|TIA
|Dave
|
|Dave Morgan wrote:
|>
|> Hi Jonathan,
|> Oracle 8.1.7.0 on Solaris 2.8 (5.8 or just 8).
|>
|> It is a private synonym owned by the table owner.
|>
|> Strange indeed, there is no record of the partition
|> in dba_segments. All the other partitions show up,
|> (with the correct owner) and the app is still loading
|> August data. Where is what I am wondering now?
|>
|> Sigh 
|>
|> All I really need to know is what happens if
|> I drop the underlying table, the synonym and
|> recreate the synonym to point to a new table.
|>
|> Thanks for your assistance.
|>
|> Dave
|
|--
|Dave Morgan
|DBA, Cybersurf
|Office: 403 777 2000 ext 284
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Dave Morgan
|  INET: [EMAIL PROTECTED]
|
|Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
|San Diego, California-- Public Internet access / Mailing
Lists
|
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from).  You may
|also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: PARTITION attache to SYNONYM

[Jonathan Lewis]

Clearly this hasn't happened ;)

But if the item shows up in dba_tab_partitions
then there is a data segment linked to the
partition.

Get the SQL from the view dba_tab_partitions,
you will see that the first section of the 3 unions
is for tabpart$, and it joins tabpart$ to seg$
on file#' and block#.

Clip out this bit of the sql, and select out
the file# and block# for the funny partition.

Then use those values to query the 
file and block against dba_segments to
find out what data segment is actually 
being referenced.



Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 08 August 2001 18:22


|Hi Jonathan,
| Oracle 8.1.7.0 on Solaris 2.8 (5.8 or just 8).
|
| It is a private synonym owned by the table owner.
|
| Strange indeed, there is no record of the partition
| in dba_segments. All the other partitions show up,
| (with the correct owner) and the app is still loading 
| August data. Where is what I am wondering now?
|
| Sigh 
|
| All I really need to know is what happens if
| I drop the underlying table, the synonym and
| recreate the synonym to point to a new table.
|
| Thanks for your assistance.
|
|Dave
|
|


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: PARTITION attache to SYNONYM

[Dave Morgan]

Jonathan and others, 

And some more information. I love my job, I love my job, ...


I have 15 million rows taking up 840 MB in a tablespace
that has no segments or extents (In a partition that sort of exists,
or sort of not exists)

The data is updatable and readable.


Did I mention that I love my job.

TIA
Dave

Dave Morgan wrote:
> 
> Hi Jonathan,
> Oracle 8.1.7.0 on Solaris 2.8 (5.8 or just 8).
> 
> It is a private synonym owned by the table owner.
> 
> Strange indeed, there is no record of the partition
> in dba_segments. All the other partitions show up,
> (with the correct owner) and the app is still loading
> August data. Where is what I am wondering now?
> 
> Sigh 
> 
> All I really need to know is what happens if
> I drop the underlying table, the synonym and
> recreate the synonym to point to a new table.
> 
> Thanks for your assistance.
> 
> Dave

-- 
Dave Morgan
DBA, Cybersurf
Office: 403 777 2000 ext 284
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Dave Morgan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: PARTITION attache to SYNONYM

[Dave Morgan]

Hi Jonathan,
Oracle 8.1.7.0 on Solaris 2.8 (5.8 or just 8).

It is a private synonym owned by the table owner.

Strange indeed, there is no record of the partition
in dba_segments. All the other partitions show up,
(with the correct owner) and the app is still loading 
August data. Where is what I am wondering now?

Sigh 

All I really need to know is what happens if
I drop the underlying table, the synonym and
recreate the synonym to point to a new table.

Thanks for your assistance.

Dave




>  From: "Jonathan Lewis" <[EMAIL PROTECTED]>
>  Date: Tue, 7 Aug 2001 20:15:30 +0100
>  Subject: Re: PARTITION attache to SYNONYM
> 
> Which version of Oracle ?
> I got a 600 error when trying to create a partition
> when using a public synonym instead of the
> table_name on 8.1.7.0
> 
> Have a look in dba_segments for segment_name  = 'your table name'
> and segment_name = 'your synonym', check especially the OWNER
> in case something very strange has happened.
> 
> 
> Jonathan Lewis
> 
> Seminars on getting the best out of Oracle
> Last few places available for Sept 10th/11th
> See http://www.jlcomp.demon.co.uk/seminar.html
> 
> 
> 
> 
> -Original Message-
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: 07 August 2001 19:57
> 
> 
> |Hi All,
> | Wondering if anyone has seen anything like this. I have
> |filed a TAR.
> |
> | When adding next month's partition to a table a synonym was
> | used accidently. However, the partition was created and shows
> | up  in dba_tab_partitions. However, I cannot modify of drop the
> | partition.
> |
> | ALTER TABLE synonym_name drop PARTITION partition_name;
> | returns no such table
> |
> | ALTER TABLE table_name drop PARTITION partition_name;
> | returns no such partition.
> |
> | I have built another structure to hold the data but does anyone
> | know the consequences if I dorp the ysnonym.
> |
> |TIA
> |Dave
> |
> |
> |--
> |Dave Morgan
> |DBA, Cybersurf
> |Office: 403 777 2000 ext 284

-- 
Dave Morgan
DBA, Cybersurf
Office: 403 777 2000 ext 284
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Dave Morgan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: PARTITION attache to SYNONYM

[Jonathan Lewis]

Which version of Oracle ?
I got a 600 error when trying to create a partition
when using a public synonym instead of the
table_name on 8.1.7.0

Have a look in dba_segments for segment_name  = 'your table name'
and segment_name = 'your synonym', check especially the OWNER
in case something very strange has happened.


Jonathan Lewis

Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html




-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 07 August 2001 19:57


|Hi All,
| Wondering if anyone has seen anything like this. I have
|filed a TAR.
|
| When adding next month's partition to a table a synonym was
| used accidently. However, the partition was created and shows
| up  in dba_tab_partitions. However, I cannot modify of drop the
| partition.
|
| ALTER TABLE synonym_name drop PARTITION partition_name;
| returns no such table
|
| ALTER TABLE table_name drop PARTITION partition_name;
| returns no such partition.
|
| I have built another structure to hold the data but does anyone
| know the consequences if I dorp the ysnonym.
|
|TIA
|Dave
|
|
|--
|Dave Morgan
|DBA, Cybersurf
|Office: 403 777 2000 ext 284
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Dave Morgan
|  INET: [EMAIL PROTECTED]
|
|Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
|San Diego, California-- Public Internet access / Mailing
Lists
|
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from).  You may
|also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[dave . leach]

Many thanks for the replies on this.

I was indeed using autotrace instead of doing an explain plan with utlxpls.
I have also found the detail I was looking for in the documentation (can't
beleive I didn't seee it before, it must have been a long day!!).

Cheers,

Dave Leach

-Original Message-
Sent: 31 May 2001 18:22
To: Multiple recipients of list ORACLE-L


Oracle could be doing partition elimination but the tools that you are using
to see the execution plan is not showing you the details you're looking for.

I usually do:

Truncate table plan_table;
explain plan for ;
select * from plan_table;


There should be two columns that indicate the partition-start_number and the
partition_stop_number for this full table scan.

If it's not working let's know.

Regards,

Waleed

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition Elimination

[Jared Still]

The docs actually do a good job of showing what the explain plan
will look like for a partition elimination.

Look at the 'Explan Plan' chapter of 'Designing and Tuning for Performance'

Are you using ?/rdbms/admin/utlxplp.sql to examine your explain plan?

Jared


On Thursday 31 May 2001 09:00, [EMAIL PROTECTED] wrote:
> Hi All,
>
> Can anyone help me with this.
>
> I have range partitioned a table (no indexes) and then computed statistics.
> I have now queried the table using the partition key as the only criteria
> in the where clause.  Why does Oracle still do a full table scan, why is it
> not clever enough to only scan the partition(s) effected by the where
> condition?.
>
> The Oracle documentation gives a good insight into partitioning but does
> not go into detail about when partition elimination will be performed and
> what the explain plan would look like when this occurs.  If anyone can
> point me to a section of the documentation that covers this I would be
> grateful.
>
> Many Thanks,
>
> Dave Leach
>
>
> **
> The above information is confidential to the addressee and may be
> privileged.  Unauthorised access and use is prohibited.
>
> Internet communications are not secure and therefore this Company does not
> accept legal responsibility for the contents of this message.
>
> If you are not the intended recipient, any disclosure, copying,
> distribution or any action taken or omitted to be taken in reliance on it,
> is prohibited and may be unlawful.
>
> Hogg Robinson PLC
> Registered Office: Abbey House, 282 Farnborough Road,
> Farnborough,
> Hampshire GU14 7NJ
> Registered in England and Wales No 3249700
>
> **
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[Toepke, Kevin M]

At parse time, the optimizer determines that partition elimination can be
done -- which partitions to use is determined after the values are bound to
the query.

-Original Message-
Sent: Thursday, May 31, 2001 3:17 PM
To: Multiple recipients of list ORACLE-L


Can oracle do partition elimination when ysing bind variables?

Alex Hillman

-Original Message-
Sent: Thursday, May 31, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


Dave:

Oracle does do partition elimination in this case...check out he
partition_start and partition_stop columns of you plan_table.

Kevin

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hillman, Alex
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[Hillman, Alex]

Can oracle do partition elimination when ysing bind variables?

Alex Hillman

-Original Message-
Sent: Thursday, May 31, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


Dave:

Oracle does do partition elimination in this case...check out he
partition_start and partition_stop columns of you plan_table.

Kevin

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hillman, Alex
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[Khedr, Waleed]

Yes it does.

The scan will be range of partitions and the execution plan will show
partition stop = key and partition start = key.

Regards,

Waleed

-Original Message-
Sent: Thursday, May 31, 2001 3:17 PM
To: Multiple recipients of list ORACLE-L


Can oracle do partition elimination when ysing bind variables?

Alex Hillman

-Original Message-
Sent: Thursday, May 31, 2001 1:16 PM
To: Multiple recipients of list ORACLE-L


Dave:

Oracle does do partition elimination in this case...check out he
partition_start and partition_stop columns of you plan_table.

Kevin

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hillman, Alex
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition Elimination

[Jay Hostetter]

Check optimizer_mode in v$parameter for your session.  I don't think it will eliminate 
partitions if it is RULE.

Here is an explain plan from a simple select on a partitioned table.  An index exists:

SELECT STATEMENT Optimizer=CHOOSE (Cost=1.69535189333285 Card=32 Bytes=3488)
  PARTITION RANGE (SINGLE)
TABLE ACCESS (BY LOCAL INDEX ROWID) OF DMS_AMA_RECORDS (Cost=1.69535189333285 
Card=32 Bytes=3488)
  BITMAP CONVERSION (TO ROWIDS)
BITMAP INDEX (SINGLE VALUE) OF DMS_AMA_RECORDS_UI1


Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA  USA

>>> [EMAIL PROTECTED] 05/31/01 12:00PM >>>
Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: 
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[Khedr, Waleed]

Oracle could be doing partition elimination but the tools that you are using
to see the execution plan is not showing you the details you're looking for.

I usually do:

Truncate table plan_table;
explain plan for ;
select * from plan_table;


There should be two columns that indicate the partition-start_number and the
partition_stop_number for this full table scan.

If it's not working let's know.

Regards,

Waleed

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Partition Elimination

[Toepke, Kevin M]

Dave:

Oracle does do partition elimination in this case...check out he
partition_start and partition_stop columns of you plan_table.

Kevin

-Original Message-
Sent: Thursday, May 31, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L


Hi All,

Can anyone help me with this.

I have range partitioned a table (no indexes) and then computed statistics.
I have now queried the table using the partition key as the only criteria in
the where clause.  Why does Oracle still do a full table scan, why is it not
clever enough to only scan the partition(s) effected by the where
condition?.

The Oracle documentation gives a good insight into partitioning but does not
go into detail about when partition elimination will be performed and what
the explain plan would look like when this occurs.  If anyone can point me
to a section of the documentation that covers this I would be grateful.  

Many Thanks,

Dave Leach


** 
The above information is confidential to the addressee and may be
privileged.  Unauthorised access and use is prohibited. 

Internet communications are not secure and therefore this Company does not
accept legal responsibility for the contents of this message. 

If you are not the intended recipient, any disclosure, copying, distribution
or any action taken or omitted to be taken in reliance on it, is prohibited
and may be unlawful. 

Hogg Robinson PLC 
Registered Office: Abbey House, 282 Farnborough Road, 
Farnborough, 
Hampshire GU14 7NJ 
Registered in England and Wales No 3249700 

** 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Partition by month (NOT INCLUDING YEAR)

[Binley Lim]

First of all, always post the code *and* the error message. 

In range partitioning, the partitioning column must be of the same datatype as the 
partitioning value -- ie date. If you want to partition by month (number) then you 
need an additional column holding the month number only, and refer to that number in 
the values less than clause.

But this is probably a better candidate for hash partitioning.


>>> [EMAIL PROTECTED] 04/12/01 04:40AM >>>
List,
  I am attempting to partition a table based on the month, not the month and 
the year. I want all January records to go into the January partition (even 
if the records are from January 1998,1999,2000, or 2001). Any January record 
from any year all goes into one partition, any February record from any year 
goes into the February partition, etc...)

I tried the following code, but no luck. Ideas??
Thanks! -Fred S.

CREATE TABLE MEETING_ROOM_USAGE (
SEQ_MEETING_ROOM_USAGE NUMBER(18) NOT NULL,
POLL_ID NUMBER(10) NOT NULL,
PROPERTY_ID NUMBER(8) NOT NULL,
IP_ADDRESS VARCHAR2(15) NULL,
MAC_ADDRESS VARCHAR2(17) NULL,
ACCESS_CODE VARCHAR2(20) NULL,
CHARGE NUMBER(7,2) NULL,
DURATION NUMBER(9) NULL,
USAGE_DATE DATE NULL,
ROOM_NUMBER VARCHAR2(20) NULL,
COLLECTION_DATE DATE NULL,
CUSTOMER VARCHAR2(50) NULL,
USER_NAME VARCHAR2(50) NULL,
CONSTRAINT PK_MEETING_ROOM_USAGE
PRIMARY KEY (SEQ_MEETING_ROOM_USAGE))
PARTITION BY RANGE (USAGE_DATE)
(PARTITION JANUARY values less than (TO_CHAR('02','MM'))
TABLESPACE MONTH1_USAGE_TS,
PARTITION FEBRUARY values less than (TO_CHAR('03','MM'))
TABLESPACE MONTH2_USAGE_TS,
PARTITION MARCH values less than (TO_CHAR('04','MM'))
TABLESPACE MONTH3_USAGE_TS,
PARTITION APRIL values less than (TO_CHAR('05','MM'))
TABLESPACE MONTH4_USAGE_TS,
PARTITION MAY values less than (TO_CHAR('06','MM'))
TABLESPACE MONTH5_USAGE_TS,
PARTITION JUNE values less than (TO_CHAR('07','MM'))
TABLESPACE MONTH6_USAGE_TS,
PARTITION JULY values less than (TO_CHAR('08','MM'))
TABLESPACE MONTH7_USAGE_TS,
PARTITION AUGUST values less than (TO_CHAR('09','MM'))
TABLESPACE MONTH8_USAGE_TS,
PARTITION SEPTEMBER values less than (TO_CHAR('10','MM'))
TABLESPACE MONTH9_USAGE_TS,
PARTITION OCTOBER values less than (TO_CHAR('11','MM'))
TABLESPACE MONTH10_USAGE_TS,
PARTITION NOVEMBER values less than (TO_CHAR('12','MM'))
TABLESPACE MONTH11_USAGE_TS,
PARTITION DECEMBER values less than (maxvalue))
/


_
Get your FREE download of MSN Explorer at http://explorer.msn.com 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Fred Smith
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Binley Lim
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).