Multiple Table Extract Question

[Edgar_10]

Hi All,

I have a DB which has daily monthly tables(ie.20030101_data,20030102_data,20030103_data ..,20030131_data).These tables are not partitioned by the way. My question is, how can i query all daily tables of a specific month for a certain pattern (ie.select * from 20030101where NAME='JOHN') without having to write 31 sql statements. And if I wanted to export the returned rows, how can I export in a CSV format.

Your assistance is most appreciated!

Regards

[Jean-Luc Oudart]

If I understand all tables have same structure. Therefore you can create a view on all these tables (Union).

But if you have lots of tables this will be very costly !

JL

[Edgar_10]

Hi J,

You are correct, the tables have the exact same structure,just different names. Any idea how the syntax of the inner/outer/cartesian join should be?

Thanks!

[Jean-Luc Oudart · Accepted solution]

Would be something like :
create view my_view as
( select A,B,C from tab1
union all
select A,B,C from tab2
union all
select A,B,C from tab3
)
But as I said performance will degrade seriously with the number (and size) of tables you include in your view definition

Hopefully NAME is an index in all these tables.

I suppose this was the "old" way to simulate partioning.

Rgds,
Jean-Luc

[Edgar_10]

Hi JL,

Yeah, performance wise there will be degradation,but hopefully not too serious.Thanks for the input, appreciate it!

Thanks!

[Radim Jarosek]

Hi,

concerning export ...

Folowing query return data in CSV format:

select col1 ||','|| col2 ||','|| col3 from
table


If you have oracle, use UTL_FILE package to save output to txt-file.

Example : http://oracle.ittoolbox.com/code/d.asp?d=2012&a=s


HTH

Radim

[Indira Aramandla]

What version of oracle are you using. If the
version is oracle 9i, then you could use the full functionality of outer join / inner join.

If the version is other than 9i then I guess you use the UNION join. It does degrade performance, so you can write the scrpt and cron the job when there is low load on the system.

When you extract you can spool the output as delimited by comma or space into a text file. thne you can save the file as exel or any other type.

[vasundhara]

Hi,

You can even go for a PL/SQL procedure or shell script. U just need to pass the date as argument for the query.

I am giving u a small shell script. I think you have to modify this if your single digit day is represneted as 01, 02, 03 etc... If it is represented as 0,1,2... it will work fine.

$i=0
while [[ $i -lt 30 ]]
do
sqlplus -s $1 <select * from 200301$i_data;
quit
ABC
i=`expr $i+1`
done



Regards
VJ.

[H.Merijn Brand (procura]

Why not use a perl script with dynamic statements and DBD::CVS as backend?


my $dbh = DBI->connect (...);

foreach my $d (20030101 .. 20030131) {
my $sth = $dbh->prepare ("select * from ${d}_data");
$sth->execute;
while (my @row = $sth->fetchrow_array) {
# ... do something with your data
}
}

# and now use DBD::CSV (together with Text::CSV_XS for speed) to write the gathered data

Enjoy, have FUN! H.Merijn