SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle performance with large tables

    Hi guys!

    I'm a junior php developer with little experience in large relational databases, read a lot theory since university but little practice (just mysql little installs at home), I'm wondering if you could gimme a hand...

    Now I'm dealing with an oracle db whose tables usually have thousands to few million rows. I'd like to speed up some queries involving in some cases 4 level inner joins, performing poorly imho (40 secs!), and after a second read on Rudy's Simply SQL I reach the conclusion Indexes and Views could improve those results, since most of them are too complicated (at this moment) for me to think on rewriting them. Notice the db is rarely updated/inserted, 3-4 times a year, all we do is selecting.

    So I'd like to know if you guys think these indexes could be a nice first step or should I try to set up an oracle free server at home to test and measure all this stuff. Before asking the dba to give me permissions to create views and indexes I'd like to make myself sure of what I need and want to do since there's a lot of people working on the server.

    Any help would be appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    indexes will definitely improve performance of queries, if they don't already exist

    you'll have to optimize each query separately, though, because an index that improves one query might have no effect on another

    the good news is that you don't have to worry about impacting updates

    note that views won't do a thing for performance, they're just a "re-statement" of a query, and they can make queries that use the views simpler and easier to understand
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since database updates are so infrequent, have you considered dumping all the data into a huge de-normalised table that's indexed to the hilt?

    Every time the DB is modified, re-run the query to build up the table again and do all of your selects against that.

    Cheers,
    D.

  4. #4
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you a lot joining for this discussion, please note it's more about my own training than anything else

    Rudy: I have read up somewhere that creating views are kind of precalculated since they're stored to be frequently used: "Materialized views are an Oracle Silver Bullet when pre-joining tables together for super-fast response time. " - found at dba-oracle.com.

    I checked your book and found none of this but on the where clause performance tips, about indexing fk's and on & where condition fields.

    I've been checking the create DDL statetement for several tables (automated by Oracle SQL Developer) and I found most tables do not have pk constraints, I think because most of them are collecting numerical data instead of representing entities (please correct me if I'm wrong). There aren't either indexes at all. IŽll dive into Oracle/SQL Documentation about indexing.


    D: You mean dumping all the million-rows tables to a single one? what about all the different field names and disk space? The updates on the system are made from entire harddisks data although it may be every 3-4 months. I don't know if other university teams are inserting data regularly, but I'm sure they are not in the bunch of tables my fellows and I are dealing with.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    aha, materialized views...

    these aren't really views, in the strict sense of the word -- they're more like redundant data in snapshot tables

    as for "collecting numerical data instead of representing entities" without any indexes at all -- you are not wrong, but the database designer was
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Marcky View Post
    D: You mean dumping all the million-rows tables to a single one?
    It doesn't have to be a single table, as such. It depends on the data. Ultimately what you're trying to do is eliminate all costly joins.
    what about all the different field names and disk space?
    Disk space is probably the least of your worries. Space is cheap. As for field names, you can mitigate that by defining some views for the most common query scenarios.

    Cheers,
    D.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •