Table partitionig

I have a Oracle table with >10M (referencing other child tables) of record without date, and i want partitioning it  by date.
There is a workaround ?


CREATE TABLE T1
(
 ID               NUMBER(19),
 VERSION   NUMBER(19),
  NAME       VARCHAR2(200 BYTE) NOT NULL,
  KEY           VARCHAR2(32 BYTE) NOT NULL
)
LVL 1
Raffaele d'ArgenzioSystem Administrator & Oracle DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't understand your question.

How do you propose to do date range partitioning without a date column in the table?

What benefit do you hope to achieve with partitioning if there is a column that you will NEVER use in a query?

Just because you have 10 million rows doesn't mean you need to partition.  I have tables with over 100 million rows and they aren't partitioned.  The way they are queried just doesn't lend itself to a good partitioning scheme.

Another consideration is Partitioning isn't free and it isn't cheap.  If you don't need it, then why license it?
0
johnsoneSenior Oracle DBACommented:
Partitioning for the sake of partitioning isn't useful.  What feature of partitioning are you planning on taking advantage of?  The most useful one would be partition pruning, but there isn't a way to do that if you aren't using the partition key in your query.

If you aren't taking advantage of partitioning in some way, you still have to access the same amount of data.  Depending on your query, it could actually be more.  Think of a partition with a local index.  If you have 10 partitions, on a keyed lookup, that is 10 trees that have to be traversed to find the record you want.  If it wasn't partitioned, it would only be one.  Tree depth should be about the same, so do you want to do 10 times the work to get the same information?

I have had tables with over 1 billion rows in them.  Not partitioned.  Sub second performance on queries.
0
Raffaele d'ArgenzioSystem Administrator & Oracle DBAAuthor Commented:
I have already active partitions on other tables.
The table is a master table that have 10 tables referenced ( https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1#reference_partitioning)
The scope is for mantain online only few day, dropping referenced partition

I want only add a date on table T1.
Is possible to add virtual column with value of SCN_TO_TIMESTAMP(ORA_ROWSCN) ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

awking00Commented:
Without knowing your data, it's hard to tell if your table could benefit by partitioning or not. Do you query the table based on the version or key values? If so, it may or not be beneficial to partition on those fields.
0
slightwv (䄆 Netminder) Commented:
I'm not sure if you can use ORA_ROWSCN in a virtual column.  You can try it and see but the docs say you cannot use it in a view so I'm not sure if that also applies to a virtual column.  I'm also not sure if SCN_TO_TIMESTAMP(ORA_ROWSCN) is deterministic so it might not let you use it.

It also is loosely based on last update time so it isn't an exact time of row change.  If you can live with possibly dropping rows that you didn't mean to, then sure, if it works, go for it.  You will also get rows moving around in the partitions as they are updated.

Can you explain a little more about the requirement to only keep a few days of data based on insert/update dates?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raffaele d'ArgenzioSystem Administrator & Oracle DBAAuthor Commented:
Thanks solved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.