1

I want to know if i can use partitioning on an already existing table. I have a table which i want to divide on the basis of values in one column. I have some tickets(t_id) which are of different accounts(a_id). I want to divide the tables based on a_id. Is it possible to do so? because it takes a lot of time to run queries or procedures on that table. If not possible is there any other way i could reduce the time taken by procedures and queries?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
olivia
  • 119
  • 1
  • 2
  • 9
  • Directly, no. You could create a new partitioned table, move the data from the old table over, then drop the old table. You can do that online with the `dbms_redefinition` package. Partitioning is an extra cost option on top of the enterprise edition license and not a cheap one. Are you certain that you need that rather than, say, adding some indexes? Do all of your queries include `a_id` as a predicate? – Justin Cave Nov 18 '15 at 04:30
  • Yes all my queries include a_id. How can i partition on the basis of a column? I am new to partitioning and know nothing about it. – olivia Nov 18 '15 at 04:35

2 Answers2

2

You can partition an existing table using DBMS_REDEFINITION package. The below link explains how to partition an existing table.

https://oracle-base.com/articles/misc/partitioning-an-existing-table

The performance of procedures and queries depends on lots of factors. Partitioning the table alone might not help. To start, you can look at the explain plan of the query that is slow. It will show whether you need to gather stats or add indices etc.

Balaji Sukumaran
  • 185
  • 1
  • 12
  • Actually i have one more thing that i need to do. I need to use a trigger separately for one a_id. But when i use a trigger it acts on the entire table which i don't want. – olivia Nov 19 '15 at 05:04
  • olivia, could you raise it as a different question so that the entire community will have the opportunity to look at it. Please do post the trigger code snippet as well. Thanks ! – Balaji Sukumaran Nov 19 '15 at 05:19
0

You cannot directly partition an existing non-partitioned table. You will need to create an interim table/new table depending on the following methods to partition:

  • DBMS_REDEFINITION

    1. Create a Partitioned Interim Table
    2. Start the Redefinition Process
    3. Create Constraints and Indexes (Dependencies)
    4. Complete the Redefinition Process

  • EXCHANGE PARTITION

    1. Create a Partitioned Destination Table
    2. EXCHANGE PARTITION
    3. SPLIT PARTITION (If required to split single large partition into smaller partitions)
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124