-
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
-
Forget it *laugh* I figured it out.
Thanks :)
Joe
-
Can you do this :
Create table dummy as select * from huge_table where rownum <= 5000
Is this simple ?
Maybe I misunderstand .. please correct me
-
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
-
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]
-
Or, look at
http://osi.oracle.com/wa/ask/owa/ask...d=641423836249
to find out how the "SAMPLE" function can get you a random sample of data.
-
Thx, Jeff & Joe, this is very cool, er, I mean useful. D.
