Re: Oracle Physical Implementation Expert Help required

Iain,

Comments inline....

> 1) On DB2 you can reorganize the data in a physical table by an index of
> your choosing (The most used index). Can, and if so how do you do the on
> ORACLE ?
This flies in the face of relational database theory. Accoring to relational theory, the rows (and columns) in a table have no physical order. The order is unimportant. While I have never used DB2, I'm assuming that IBM did it this way so that retrievals on the that index's columns would be faster. The closest thing that Oracle has to this is the Index Organized Table (IOT).  

> 2) What are the factors that make a SELECT statement run faster?

Lots of factors. The order of the tables in the FROM clause, the operations in the WHERE clause, indexes, etc. There's just too much to give a short answer. There are many books written on tuning SQL statements that would greatly help you.

> 3) Given the above example. Would there be any difference in speed if switch
> sides a WHERE clause;

The biggest difference in the above example would probably occur when you switch the order in the FROM clause.

> 4) Tablespaces - Given the above tables, Would I be better to place the
> order, order_detail and product tables in separate tablespaces on different
> disks? Would there be any benefit of having more than one datafile per
> table? These tables being the most frequently accessed for adding and
> updating.

For speed purposed, it would be ideal if every table and index resided on their own, seperate disk with it's own disk controller. But that is not likely in practice. For a general rule of thumb, seperate your tables from their indexes.

It's hard to make a table span more than one datafile (unless the table is partitioned). You put a table in a tablespace. You have very little control over where Oracle places the table in the tablespace.

> 5) What benefit would key partitioning bring on a single machine?

I'm not quite sure what you mean by "key partitioning". Oracle does have a partitioning option which can bring big benefits in the right situation.  

> 6) Is there an easy way to calculate the amount of space required for an
> table/index? On the AS/400 I could state the initial number of records +
> increments and the indexes would change with it. A select statement would be
> good.

The easiest method is to load a representative sample of your data. For example, load 10% of your data. Analyze the table and it's indexes to see how big they are. Multiply your numbers by 10, and you'll know how much room 100% takes.  

> 7) Is it better to create views for frequent queries from a performance
> point of view?

For most situations, yes. This way, you can code hints in your views if you want to and then selecting from the view will use the hint. Also, if the view is used often enough and the query from the view is consistent enough, then it will be preparsed and sitting in the Library Cache waiting for the next use.  

> 8) In DB2 a package is derived from embedded SQL at compile time and it is
> bound to a database it is used on so it has knowledge of access paths to
> data when any query is executed against the compiled code. You can run
> command to explain exactly what will be done to return the results. What do
> you do for ORACLE?

It's pretty similar. A package or procedure will be found preparsed after it's first execution (provided it hasn't aged out of the Library Cache). You can use the EXPLAIN PLAN command to see its execution plan.  

> 9) We use the database to validate uniqueness in a row on triggers. E.g.
> order number in order table above. This would cause a mutating table in
> ORACLE. Is there an ATOMIC equivalent for ORACLE databases. Basically we
> want to do this on an insert/update trigger for my_table.

I'm not sure why anyone would want to use a trigger to verify uniqueness. Just declare a UNIQUE or PRIMARY KEY constraint and uniqueness will be guaranteed. There is no need to jump through extra hoops to accomplish this.  

> My question is, is selecting an ORDER_DETAIL_ID from 12,000,000 records
> faster than select on COMPANY_ID, ORDER_ID and ORDER_DETAIL_ID. I have
> obviously added unique keys to the candicate keys thus generating indexes.

I don't think that I can absolutely give one answer to this question. It depends on many things. The best way to find out is to time your queries and see which ones work the fastest.  

> 11) We often select of five tables or more in one statement on the premise
> that this will run faster than two statements. This obviously may cause
> debugging problems not knowing which table lacks data. If the assumption of
> one statement is always faster correct? And is calling PL/SQL even slower?

The assumption is not always correct. It depends on multiple things. Like before, time the queries to see which works fastest. PL/SQL is not slower by itself. But with PL/SQL, I can do other processing not available in straight SQL. So things might run slower, but might not.  

> 12) Will clusters help ?

Clusters will help in the right situation. If you have two tables that are very frequently joined, then clusters will help. But querying one of the tables in a cluster will be slower than if the table was not part of the cluster. Personally, I've never seen the need for clustering two tables. If you want the tables joined, then de-normalize and store the joined tables as one single table.

HTH,
Brian

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================