Re: Need help with query which runs faster in SQL than ORACLE

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 10 Aug 2006 09:05:01 -0700
Message-ID: <1155225903.15072@bubbleator.drizzle.com>

faisal.mansoor_at_gmail.com wrote:
> Thanks once again Grin.
>
> Sorry pasted wrong oracle query plan. This is the right one
>
> 7 border=0 SELECT STATEMENT
> 6 border=0 SORT (UNIQUE)
> 5 border=0 UNION-ALL
> 1 border=0 TABLE ACCESS (FULL), UNION_TEST (SPK)
> 4 border=0 FILTER
> 2 border=0 TABLE ACCESS (FULL), UNION_TEST (SPK)
> 3 border=0 TABLE ACCESS (FULL), UNION_TEST (SPK)
>
> I was using SYS only for testing purpose.

Do not top post.
This is plan from what product?

Explain plan in Oracle should look like this:

PLAN_TABLE_OUTPUT

SQL_ID f01hgmvh4karx, child number 0

SELECT /* XPLAN_CURSOR */ DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id

Plan hash value: 4025208304

| Id  | Operation              | Name         | Rows | Bytes | Cost 
%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |       |      |     3 
(100)|          |
|   1 |  NESTED LOOPS          |              |    11 |   88 |     3 
(34)| 00:00:01 |
|   2 |   SORT UNIQUE          |              |   999 | 3996 |     2 
(0)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN| IX_SERV_INST |   999 | 3996 |     2 
(0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |     1 |    4 |     0 
(0)|          |
--------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

4 - access("S"."SRVR_ID"="I"."SRVR_ID")

22 rows selected.

SQL>

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 10 2006 - 11:05:01 CDT