tuning

pts.
Tags:
Billing and customer care
Billing Support Systems
Data analysis
Database
DB2
Oracle
I have a program which is run on an ad-hoc basis....the database is mainly a read only dB, i.e. not much inserts/updates/deletes. The process basically reads tables, then creates temporary oracle tables, creates indices on these tables and then does some inserts. The temporary oracle tables are then dropped when the process in run the next time round. The performance has been good up until now but with much larger volumes of data it's slowing up. Will bitmap indices help for querying the temp tables? Do bitmaps take longer to create over b-tree? PL/SQL functions set table column values when creating the table, should I remove them to table triggers? Any other helpful ideas? dB = 10g Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

One thing you could try would be to create the indexes after you insert the records. This will speed up the inserts. Depending on the number of records in the tables and the number of indexes on the tables, the index creation process will probably take longer.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BCofHP
    Bitmap indices are useful for low-cardinality situations. You know your data. If you've got a good distribution of key values, stick with standard indices. Triggers will probably slow you down since they get fired for each row. It's not a good situation for bulk loading. Why not create the data first and then create the indices? This is typically much, much faster.
    0 pointsBadges:
    report
  • Oadegoke
    I agree with BCofHP.
    0 pointsBadges:
    report
  • Bozzo999
    Instrument and measure your process to better determine what is taking the most time (e.g. inserts vs index creates vs queries). Then focus on portions of the process with the longer duration. You indicate you?re building indexes now. Are the queries using them appropriately ? Are you generating statistics on any of the tables ? Ken
    0 pointsBadges:
    report
  • Bozzo999
    Instrument and measure your process to better determine what is taking the most time (e.g. inserts vs index creates vs queries). Then focus on portions of the process with the longer duration. You indicate you?re building indexes now. Are the queries using them appropriately ? Are you generating statistics on any of the tables ? Ken
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following