This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jan 25, 2013 1:24 PM by rp0428 Go to original post RSS
  • 15. Re: dbms_redefinition - change table from non partitioned to partitioned
    rp0428 Guru
    Currently Being Moderated
    >
    This update is obviously a one time run, and so is the subsequent insert in my steps mentioned above, the DBA says because the table is huge, lets skip the insert by using any other approach
    >
    IMPOSSIBLE! It simply isn't possible to skip the insert. If your DBA thinks it is then then don't understand how partitioning is done.

    Here are the basic facts:

    1. A non-partitioned table has one segment that ALL data is in (we are ignoring possible LOB segments)

    2. Each partition of a partitioned table has its own segment.

    3. ALL data from the non-partitioned table's segment has to be INSERTED into a partition of the partitioned table.

    You can use DBMS_REDEFINITION and let it do the insert or you can do the insert yourself. But you CANNOT avoid doing the insert.

    What you can possibly avoid is doing the update of your 'pv' column of your existing table and just create the proper value when you do do the insert.
  • 16. Re: dbms_redefinition - change table from non partitioned to partitioned
    onedbguru Pro
    Currently Being Moderated
    If you are using 11gR2, you roll your own DBMS_REDEF by using a procedure that uses DBMS_PARALLEL_EXECUTE.

    create starting point (id col+ starting timestamp)
    copy data in parallel based on rowid (see PEX documentation examples)
    update any changes since starting
    rename tables... done.

    The REDEF is still your best option...
  • 17. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    You are correct


    -- copies data, this works
    BEGIN
    dbms_redefinition.start_redef_table( user, 'TEST_T1','TEST_T2' );
    END;


    DECLARE
    nerr number;
    begin
    dbms_redefinition.copy_table_dependents
    ( 'DMR', 'TEST_T1', 'TEST_T2',
    copy_indexes => dbms_redefinition.cons_orig_params,
    copy_triggers => TRUE,
    copy_constraints => TRUE,
    copy_privileges => TRUE,
    copy_statistics => TRUE,
    num_errors => :nerr );
    END;

    The copy_table_dependents is supposed to copy triggers, constraints, privileges, It does but with new names

    user_indexes shows the index name for test_2 as TMP$$_IDX_TESTY0 , test_1 has the index name IDX_TESTY0.

    How to take care that I get the same index names ? The only option I have is to drop and then recreate without using dbms_redefinition ?
  • 18. Re: dbms_redefinition - change table from non partitioned to partitioned
    user650888 Newbie
    Currently Being Moderated
    As for whether to create local indexes, it depends. Is your goal to improve query performance (I'm still not clear if this is the goal)? Do all your queries have predicates on PV? How are you partitioning the table? How many distinct PV values are there per partition? Is data evenly distributed across PV values?


    1. What do you mean by "predicate"

    2. There are 5 distinct values for PV for 63666500 records

    3. No the values for pv are not evenly distributed, pv has values (0,1,2,3,4), Value 3 is the one that has maximum number of records, followed by 2, 0 and 1 has only one record each, and
    I do not think 4 will even be there in the table

    Based on this, do you recommend local indexes ?
  • 19. Re: dbms_redefinition - change table from non partitioned to partitioned
    Justin Cave Oracle ACE
    Currently Being Moderated
    user650888 wrote:
    1. What do you mean by "predicate"
    A predicate is a condition in a query. Your predicates are the various filters you have in the WHERE clause of your query.
    2. There are 5 distinct values for PV for 63666500 records
    It seems odd that you'd be partitioning on a key that has only 5 values...
    3. No the values for pv are not evenly distributed, pv has values (0,1,2,3,4), Value 3 is the one that has maximum number of records, followed by 2, 0 and 1 has only one record each, and
    I do not think 4 will even be there in the table
    And it seems even odder that you would partition on a key that has only 5 values where the data distribution is highly skewed. If 4 isn't even in the table, then you're down to 4 partitions with data. 2 of your partitions have only 1 row of data so you're now down, effectively to 2 partitions. And if 3 has substantially more rows than 2, what possible benefit would you get from partitioning in the first place?

    What is your goal in partitioning the table?

    Justin
  • 20. Re: dbms_redefinition - change table from non partitioned to partitioned
    rp0428 Guru
    Currently Being Moderated
    >
    How to take care that I get the same index names ? The only option I have is to drop and then recreate without using dbms_redefinition ?
    >
    Rename them after you drop the original indexes.

    Why do you care what the index name is?
    >
    3. No the values for pv are not evenly distributed, pv has values (0,1,2,3,4), Value 3 is the one that has maximum number of records, followed by 2, 0 and 1 has only one record each, and
    I do not think 4 will even be there in the table

    Based on this, do you recommend local indexes ?
    >
    Based on that you have no justification for partioning the table at all. Partitioning will only be beneficial to make it easier to drop old data that is no longer needed or if your queries typically only need a subset of the data based on some criteria. If your PV values were fairly evenly distributed and your queries typically only selected one or two values then partitioning on that column would let Oracle automatically know to query only 20 or 40% of the data rather than a full table scan of all of the data.

    You should stop what you are planning now until you can demonstrate that there will be some benefit to partitioning.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points