Re: formula help please
Date: Sat, 5 Jan 2008 18:21:28 -0800 (PST)
Message-ID: <d642e168-7e38-4e9d-86d6-ee029ce5e110@n22g2000prh.googlegroups.com>
On Jan 5, 6:58 pm, Totti <saliba.toufic.geo..._at_gmail.com> 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.
Received on Sat Jan 05 2008 - 20:21:28 CST