DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Pushing a limited number of rows from one table to another

  1. #1
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Ok,

    I've got a table called CUSTOMER_DIM with about 4.5 million rows. I need to send a copy of this table to ORACLE with only 5000 rows. The table needs to have about the same distribution of data on a column called SOURCE_SYSTEM that mimics the distribution in the larger table

    Basically I need a table where

    CUSTOMER_DIM.SOURCE_SYSTEM =
    'tms_frc' (21 rows)
    'tms_clr' (72 rows)
    'fas' (157 rows)
    'ccss' (344 rows)
    'tms_fas' (348 rows)
    'corp' (704 rows)
    'uty' (1171 rows)
    'tms_ren' (2183 rows)

    I was thinking I could write a simple cursor over each
    of the distinct values and set a counter and then do a
    while loop until counter = number of rows needed for
    each distinct value until the table was populated.

    The problem is that there are about 300 column values
    in the table and the cursor is messy trying to define a
    variable for each column type.

    I know there is a ROWTYPE that you can specify for an
    entire row. I'm hoping I can use that somehow but haven't
    done a lot of this type of thing. Can anyone suggest a way to do this?

    Is there a simple and clean way to loop through the CUSTOMER_DIM table and select, in the first case, 21 rows where source_system = 'tms_frc' and insert them into a table called CUSTOMER_DIM_SMALL? Then I would just change the where clause for each subsequent value.

    Thanks,

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  2. #2
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Forget it *laugh* I figured it out.

    Thanks :)

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    Can you do this :


    Create table dummy as select * from huge_table where rownum <= 5000
    Is this simple ?

    Maybe I misunderstand .. please correct me

  4. #4
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    That wouldn't work because I need a representative sample based on the distribution of my data centered around the column source_system. Basically I'm having an issue where one of my very selective bitmapped indexes is not being chosen and Oracle instead forces a full table scan. Oracle thinks they've hit a bug and wanted a representative sample of the data to play with. It looks like there may be a problem with the CBO.

    I ended up writing a cursor that did it. I just haven't had to write much in the way of PL/SQL in a bit. It was fun getting back into it again. Now I realize I need to get back into it and brush up.

    Thanks for the reply though!

    Joe
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

  5. #5
    Join Date
    Jun 2000
    Location
    Nagpur, Maharashtra, India
    Posts
    10
    HI,
    I could think of one way of meeting your requirement without getting into any PL/SQL coding.
    Just create a view of your desired table as:
    create view my_small_tab_view as
    select * from big_table
    where tab_col='val1'
    and rownum < num1+1
    union
    select * from big_table
    where tab_col='val2'
    and rownum < num2+1
    union
    select * from big_table
    where tab_col='val3'
    and rownum < num3+1;
    etc....
    Once the view is created, issue a simple select command to create table as:
    create table my_small_tab as
    select * from my_small_tab_view;

    ciao



    [Edited by Gopinath on 12-04-2000 at 08:14 AM]

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Or, look at
    [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=641423836249[/url]
    to find out how the "SAMPLE" function can get you a random sample of data.
    Jeff Hunter

  7. #7
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    Thx, Jeff & Joe, this is very cool, er, I mean useful. D.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width