| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Physical Implementation Expert Help required
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
>
 |
 |