Re: SQL query correctness/efficiency of my query

On Mon, 26 Sep 2005 16:41:44 -0400, "newsgroups" <robw_at_physics.umd.edu> wrote:

>Is this the correct way to join 3 tables and if so is there another more
>efficient way to do it.
>The 3 tables item_master,cost_item,item_comments have the common key item_no
>which is the
>pk in item master and the foreign key in the other 2.
>It seems to work and I am checking the data for errors but there are
>thousands of rows and it is slow going. I am using Oracle 9i.
>Thanks Rob
>
>select item_master.item_no,
>item_comments.comment_text,
>item_master.qty_on_hand,
>cost_item.unit_cost,
>item_master.status_flag
>from item_master,cost_item,item_comments
>where cost_item.item_no = item_master.item_no
>and item_comments.item_no = item_master.item_no
>and status_flag = 'A';
>
>

Not much to work on. No explain plan included,no information about optimizer being used, and statistics being accurate, and proper indexes being available.
In short: just the ordinary 'it is slow, why?' post, without any background. 'Thousands of rows' shouldn't be a problem, *except* if you are displaying them. Terminal I/O is always a delaying factor.

The statement is absolutely correct, and it has always been true, that two types of queries often can't be optimized properly M, D, D where M->D-D is not going to work, and M, D, M.
In the first case Oracle can't determine whether the second table is a driving table to table 3, and probably will perform 2 independent joins.
In the second case Oracle can't determine the driving table at all.

Assuming very few records have status 'A', it is appropiate to index the status column (maybe a bitmap index) and to make sure accurate statistics are being present. No indexes being present, will result in a full table scan on all tables, with the appropiate records being filtered in the last stage.  

--
Sybrand Bakker, Senior Oracle DBA