Oracle Physical Implementation Expert Help required

From: Iain Wiseman <bibble_at_ibm.net>
Date: 2000/07/19
Message-ID: <39755aed_4@news1.prserv.net>#1/1

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

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

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

1. Will clusters help ?

 Iain Wiseman

New Zealand

--

mailto:iwiseman_at_g8labs.co.nz

---

---

For the sword outwears its sheath,
And the soul wears out the breast,
And the heart must pause to breathe,
And Love itself have rest. Received on Wed Jul 19 2000 - 00:00:00 CDT