Home » RDBMS Server » Performance Tuning » Staging table without indexes, slow queries (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0)
Staging table without indexes, slow queries [message #615484] Wed, 04 June 2014 11:10 Go to next message
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 #615486 is a reply to message #615484] Wed, 04 June 2014 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I have to load the data to staging tables,
Why not just use EXTERNAL TABLE & avoid loading into staging tables?


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Staging table without indexes, slow queries [message #615490 is a reply to message #615484] Wed, 04 June 2014 12:21 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Staging table without indexes, slow queries [message #615599 is a reply to message #615595] Thu, 05 June 2014 15:05 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
yes, doing row by row processing more than once is slow by slow.

http://www.orafaq.com/forum/m/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Previous Topic: SQL performance tunning
Next Topic: Table with 40 million records problem
Goto Forum:
  


Current Time: Thu Mar 28 06:56:18 CDT 2024