Re: Huge query speed difference between 2 similar queries

From: Marcus <marcusyeoh_at_gmail.com>
Date: 22 Nov 2004 15:18:27 -0800
Message-ID: <1101165507.304063.266330@z14g2000cwz.googlegroups.com>

This is not an Oracle Financials database. The oracle version is 8.0.5 Here are the EXPLAIN_PLANS
columns id, parent_id, lpad(' ', 2*(level-1))||operation||'
'||options||' '||object_name||' '||object_type||' '||decode(id, 0,
'Cost = '||position) "Query Plan"

Query 1
ID PARENT_ID Query Plan

-- --------- -------------------------------------------------------
0           SELECT STATEMENT    Cost =
1         0   NESTED LOOPS
2         1     NESTED LOOPS
3         2       TABLE ACCESS FULL PRODUCT
4         2       TABLE ACCESS BY INDEX ROWID INVOICE_DETAIL
5         4         INDEX RANGE SCAN IND_INVOICE_DETAIL NON-UNIQUE
6         1     TABLE ACCESS BY INDEX ROWID INVOICE_HEADER
7         6       INDEX RANGE SCAN SYS_C002418 UNIQUE

Query 2 -- 14.210 sec
ID PARENT_ID Query Plan

-- --------- --------------------------------------------------
0           SELECT STATEMENT    Cost =
1         0   NESTED LOOPS
2         1     NESTED LOOPS
3         2       NESTED LOOPS
4         3         VIEW
5         4           SORT UNIQUE
6         5             TABLE ACCESS FULL INVOICE_HEADER
7         3         INDEX RANGE SCAN SYS_C002418 UNIQUE
8         2       TABLE ACCESS BY INDEX ROWID INVOICE_DETAIL
9         8         INDEX RANGE SCAN PK_INVOICE_DETAIL UNIQUE
10         1     TABLE ACCESS BY INDEX ROWID PRODUCT
11        10       INDEX UNIQUE SCAN PK_PRODUCT UNIQUE

Thank you for your help. Received on Mon Nov 22 2004 - 17:18:27 CST