| Staging table without indexes, slow queries [message #615484] |
Wed, 04 June 2014 11:10  |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Hello,
I have to load the data to staging tables, and then to permanent tables. Eventhough there are thousands/may be millions of rows, there is no index on those staging tables. We are doing data validations before loading into permanent tables. Can anyone suggest what could be the best way to speed up the performance while running the data validation queries on staging tables? Or while loading the data?
Thanks.
|
|
|
|
|
|
| Re: Staging table without indexes, slow queries [message #615490 is a reply to message #615484] |
Wed, 04 June 2014 12:21  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Yes, External tables are the current and latest century's method for any ETL, mostly. However, without knowing OP's answer it won't be helpful to comment on anything.
@OP,
1. Could you please tell us whether you are using sqlldr or ext table for loading into staging tables?
2. Ok, you are doing a right thing by not using indexes on staging tables, however, could you please tell us what data validation is done before moving the data into so called permanent tables?
[Updated on: Wed, 04 June 2014 12:32] Report message to a moderator |
|
|
|
| Re: Staging table without indexes, slow queries [message #615595 is a reply to message #615490] |
Thu, 05 June 2014 14:39 |
rkhatiwala
Messages: 178
Registered: April 2007
|
Senior Member |
|
|
Thanks for the reply.
Data is loaded into staging using sqlldr.
About data validation - When the data is loaded into staging tables, they are validated against permanent tables for the related values. e.g. if col A in staging table t1 has value = 111, then the related or matching value in the permanent table t2, col B has some value, or is it null? so basically it is looking if the record exist in permanent table, or not.
This is done for all segments, one at a time ( LOOPing). So in the beginning the distinct segments are queried, e.g there are 10 segments. For each segment if they have 100 records, then all the validations ( maybe 20 validations - SELECT statements, some are cursors too). so in loop it runs for 20000 times, if we assume that there is only SELECT and no cursors. wherever the cursors are used, it returns maximum of 3 or 4 rows.
Thanks again
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
