Printable View
1)In a transaction file we have large volume of data. Is it advisable to split that table data year wise then copying it into diiferent tables?
For example Loans2006, loans2007 likewise.
2)If we split the table, based on that table there are number of reports. How to chage tablename in procedure dynamically based on the year taken as parameter?
What do you mean by split the table. do you want to create partition.
If you want to pass tablename dynamically then have to use EXECUTE IMMEDIATE.
Creating a new table for each year, tablename ends with year for example loans2006, loans2007 etc.
Why not create index on date field and pass date range to the procedure. It iwill be easier to maintain the database.