Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL query correctness/efficiency of my query

Re: SQL query correctness/efficiency of my query

From: Sybrand Bakker <>
Date: Mon, 26 Sep 2005 23:11:22 +0200
Message-ID: <>

On Mon, 26 Sep 2005 16:41:44 -0400, "newsgroups" <> 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,
>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
Received on Mon Sep 26 2005 - 16:11:22 CDT

Original text of this message