Re: Oracle Physical Implementation Expert Help required

Jerry Gitomer wrote:
>
> I know that this might sound strange, but in Oracle the
> physical storage of a table is completely divorced from the
> logical order of the table.
>
> If your SQL statements aren't doing stupid stuff, e.g.
> Applying a function to your only index on a 7 million row table
> (like some of the ERP systems do) and your configuration
> parameters are set properly and your hardware environment is
> adequate for the job at hand, and ...
>
> The answer for your specific example is to create an
> index on order. Whatever you do don't create a standard
> B-tree index on company - it isn't selective enough to ever
> be used.
>
> If you are going to work with Oracle pick up one of the
> many Oracle tuning books.
>
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 7/19/00, 2:33:44 AM, "Iain Wiseman" <bibble_at_ibm.net> wrote regarding
> Oracle Physical Implementation Expert Help required:
>
> > I am relatively new to Oracle and new to phyical planning
 

> > 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 ?
 
> > 2) What are the factors that make a SELECT statement run faster?
 

> > E.g
 

> > Given the tables contain
 

> > COMPANY 5
 
> > ORDER 12,000,000
 
> > ORDER_DETAIL 24,000,000
 
> > PRODUCT 5,000,000
 
> > SELECT
 
> > a.company, b.order_number, d.order_line, d.product_name,
 

> > FROM
 
> > company a, order b, order_detail c, product d
 

> > WHERE
 
> > a.company = 1 AND
 

> > b.company = a.company AND
 

> > b.order_number = 125 AND
 

> > c.company = b.company AND
 

> > c.order_number = b.order_number AND
 

> > c.order_line = 12 AND
 

> > d.company = c.company AND
 

> > d.product_name = c.product_name;
 

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

> > E.g
 

> > Does
 

> > SELECT
 
> > a.company, b.order_number
 

> > FROM
 
> > company a, order b
 

> > WHERE
 
> > a.company = 1
 

> > b.company = a.company;
 

> > Run faster than
 

> > SELECT
 
> > a.company, b.order_number
 

> > FROM
 
> > company a, order b
 

> > WHERE
 
> > 1 = a.company AND
 

> > a.company = b.company;
 

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

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

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

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

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

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

> > SELECT count(*) INTO :count FROM my_table;
 

> > IF :count > 0 THEN
 

> > RAISE error
 

> > END IF
 
> > 10) Keys and Indexes
 

> > We current use an ID driven database on our tables. This consists of
 putting
> > a generated sequence number on each table via an insert trigger. This
 then
> > ensures that each row on every table is unique and selecting on ID
 will
> > always retrieve the correct record.
 

> > Given
 

> > COMPANY
 
> > COMPANY_ID
> > NUMBER(38)
 
> > NAME
> > CHARACTER(50)
 
> > ORDER
 
> > COMPANY_ID
> > NUMBER(38)
 
> > ORDER_ID
> > NUMBER(38)
 
> > NAME
> > CHARACTER(50)
 
> > ORDER_DETAIL
 
> > COMPANY_ID
> > NUMBER(38)
 
> > ORDER_ID
> > NUMBER(38)
 
> > ORDER_DETAIL_ID
> > NUMBER(38)
 
> > NAME
> > CHARACTER(50)
 
> > Where COMPANY_ID,ORDER_ID and ORDER_DETAIL_ID are derived from unique
> > sequences. I.e ORDER_DETAIL_ID is unique.
 

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

> > Example 1
 

> > SELECT
 
> > NAME
 
> > FROM
 
> > ORDER_DETAIL
 
> > WHERE
 
> > ORDER_DETAIL_ID = :hOrderDetailID ;
 

> > Example 2
 

> > SELECT
 
> > NAME
 
> > FROM
 
> > ORDER_DETAIL
 
> > WHERE
 
> > COMPANY_ID = :hCompanyID
 

> > ORDER_ID = :hOrderID ;
 

> > ORDER_DETAIL_ID = :hOrderDetailID ;
 

> > 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?
>
> > Well that's it. I hope you can help
 

> > 12) Will clusters help ?
 

> > Iain Wiseman
 

> > New Zealand
 

> > --
> > mailto:iwiseman_at_g8labs.co.nz
> >

True but even for Oracle you can benefit loading the data in index order if possible, since typically you get a better cache hit ratio.

Needless to say, this generally is not possible for OLTP, but can be useful for more static systems.

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse