Re: formula help please

On Jan 5, 6:58 pm, Totti <saliba.toufic.geo...@xxxxxxxxx> wrote:
Charles, the tables are huge > 2000 rows each. so as u pointed that
must be a problem;
the other problem is that as i am checking the .txt file , the data is
generated from, i can see no costraints in the creating tables
statments, and as i understood from you, the constraints and the
primary and foreign keys are essentials in solving such problems.
what do you suggest now? should i drop present tables and put
constraints in re-generating the data? or could i work it out in
another more sophisticated way?

Thank you very much Charles you are being very helpful for me.

You might have experience working with Microsoft Access, Crystal
Reports, or other programs that do not require you to describe how
each of the tables are related to each other with each SQL statement.
Instead, programs like this use a graphical tool that allows people to
"link" tables together based on certain table columns (fields). When
the actual SQL statement is then executed (or submitted to a database
engine like Oracle), the program adds additional lines to the actual
WHERE clause that describes how the tables are to be linked together.
This automatic help that is provided by Microsoft Access, Crystal
Reports, and other programs may cause problems for the developer when
SQL statements are created outside of these tools.

The short answer is this: Unless a table has only one row, you must
tell Oracle, in every SQL statement, how each table is related to
other tables included in a FROM clause - unless you want a Cartesian
join as described in my previous post (there are times when you will
want a Cartesian join, but most times you want to avoid those types of
joins). If your tables on average have 2,000 rows, your query might
attempt to return 2000*2000*2000*2000*2000*2000 rows =
64,000,000,000,000,000,000 rows to be processed by the GROUP BY. DA
Morgan is correct that 2,000 rows in very small for an Oracle
database, but 2000^6 rows is a very large number of rows.

When you are attempting to describe to Oracle how the tables are
related, take a close look at any foreign keys that are defined in the
database, but also look at the column names, which may self describe
how the tables are related. For example, consider the following SQL
statement that pulls information from 4 tables in an ERP package:
SELECT
WO.BASE_ID,
WO.LOT_ID,
WO.DESIRED_QTY,
WO.PART_ID,
P.DESCRIPTION PART_DESC,
WO.DESIRED_QTY,
O.RESOURCE_ID,
SR.DESCRIPTION RESOURCE_DESC
FROM
WORK_ORDER WO,
PART P,
OPERATION O,
SHOP_RESOURCE SR
WHERE
WO.TYPE='W'
AND WO.SUB_ID='0'
AND WO.STATUS='R'
AND WO.PART_ID=P.ID
AND WO.TYPE=O.WORKORDER_TYPE
AND WO.BASE_ID=O.WORKORDER_BASE_ID
AND WO.LOT_ID=O.WORKORDER_LOT_ID
AND WO.SPLIT_ID=O.WORKORDER_SPLIT_ID
AND O.RESOURCE_ID=SR.ID;

People familar with Oracle will recognize that some of the column
names in the above SQL statement are Oracle reserved words, but for
this case, the naming of the columns makes it easy to see how the
tables should be related. In the above:
WORK_ORDER is related to PART
WORK_ORDER is related to OPERATION using a partial Cartesian join
that was intentional - the foreign key indicated that there should
have been another WHERE clause statement showing: AND
WO.SUB_ID=O.WORKORDER_SUB_ID
OPERATION is related to SHOP_RESOURCE

In the above example, the foreign key column is named similar to
TABLE.COLUMN, with the "." replaced with an "_", but other programs
will use different naming standards.

Do not drop the tables currently in your database. Instead, add
additional restrictions in the WHERE clause to describe how the tables
are related, as I showed in the above (even though there are foreign
and primary keys defined, you should still list those relationships in
the WHERE clause).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
.

Relevant Pages

  • Re: Optimisation of a sql query
    ... About using group by and having, I have to confess that the query I ... Shall I go ahead with the where clause as suggested ... passed on to the grouping (and possible sorting operation for Oracle ... you can start tuning the SQL statement. ...
    (comp.databases.oracle.server)
  • Why functions in Select Clause affect performance?
    ... I have a SQL statement that uses a user defined function like this ... What puzzles me is it seems the functions in the Select clause are NOT ... Can someone explain the reasons why Oracle is doing that? ...
    (comp.databases.oracle.server)
  • Re: CBO & different execution plans
    ... a SQL statement in a packaged application that was taking excessively ... long to execute - roughly 3.4 seconds per execution and the packaged ... The problem in this case had to do with Oracle picking ... Providing a hint to Oracle to use ...
    (comp.databases.oracle.server)
  • Re: DBD::Oracle installation - make test failed - ORA-28000: accout locked
    ... tests so no more complaints about the wrong return values from exit. ... But the test 20 and 21 still failed, the errors seem to be Oracle ... SQL statement ... Subject: DBD::Oracle installation - make test failed - ORA-28000: accout ...
    (perl.dbi.users)
  • Re: SELECT statement efficiency question
    ... Keep in mind that Oracle may transform your SQL statement into another ... suppose on-the-fly compiling of the PL/SQL code if not using Native ...
    (comp.databases.oracle.server)