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.
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
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.
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.
