Re: why does this statement take so long to return?

I apologize for the delay and appreciate all the responses. First off, to provide more information requested:

Here's the index schema for the tables/columns involved:

Table Name            Index Name                Column Name
DATASTAGE             DSTAGE_TYPE_IDX	        TYPE  (bitmap
index)
DATASTAGE             DATASTAGE_BODY_IDX	    BODY
DATASTAGE             DATASTAGE_RUN_ID_IDX	    RUN_ID
DATASTAGE             DATASTAGE_UQ_ID_IDX	    UNIQUE_ID
DATASTAGE             DATASTAGE_CONTENT_ID_IDX	CONTENT_ID
HIT                   HIT_SUBJ_ID_IDX           SUBJECT_ID
HIT                   HIT_HI_ID_IDX             HIT_ID
HIT	                  HIT_UNQ_ID_IDX            UNIQUE_ID
HIT	                  HIT_TRAN_IDX              TRANSFORM_ID
ORG	                  ORG_ORG_ID_IDX            ORG_ID
ORG	                  ORG_ACCOUNT_ID_IDX	    ACCOUNT_ID
ORG	                  ORG_TYPE_IDX	            TYPE (bitmap
index)
SUBJECT	              SUBJECT_SUBJ_ID_IDX	    SUBJECT_ID
SUBJECT	              SUBJECT_ORG_ID_IDX	    ORG_ID
SUBJECT	              SUBJECT_SUBJECT_TYPE_IDX	SUBJECT_TYPE

And is the execution plan via explain plan:

Id	Operation           Options            Object
Name          Object Type	Optimizer	Cost
0	SELECT STATEMENT	<null>


<null>            	<null>		CHOOSE		3237

1	SORT            	GROUP BY		


<null>            	<null>		<null>		3237

2	FILTER            	<null>


<null>            	<null>		<null>		<null>	

3	NESTED LOOPS		<null>


<null>            	<null>		<null>		2731

4	NESTED LOOPS		<null>


<null>            	<null>		<null>		79

5	NESTED LOOPS		<null>


<null>            	<null>		<null>		2

6	TABLE ACCESS		FULL              ORG
		<null>		ANALYZED	1
7	TABLE ACCESS		BY INDEX ROWID	
SUBJECT            	<null>		ANALYZED	1
8	INDEX            	RANGE SCAN		
SUBJECT_ORG_ID_IDX	NON-UNIQUE	ANALYZED	
9	TABLE ACCESS		FULL              HIT
		<null>		ANALYZED	77
10	TABLE ACCESS		BY INDEX ROWID	
DATASTAGE            <null>		ANALYZED	2
11	INDEX            	RANGE SCAN		
DATASTAGE_UQ_ID_IDX	NON-UNIQUE	ANALYZED	1
12	COUNT            	STOPKEY


<null>            	<null>		<null>	

13	VIEW            	<null>


<null>            	<null>		<null>		4182

14	SORT            	ORDER BY STOPKEY


<null>            	<null>		<null>		4182

15	SORT            	GROUP BY		


<null>            	<null>		<null>		4182

16	HASH JOIN           <null>            <null>
	<null>		<null>		3219
17	TABLE ACCESS		FULL              ORG
		<null>		ANALYZED	1
18	HASH JOIN            <null>           <null>
	<null>		<null>		3206
19	TABLE ACCESS		FULL
SUBJECT            	<null>		ANALYZED	1
20	HASH JOIN            <null>           <null>
	<null>		<null>		3176
21	TABLE ACCESS		FULL
DATASTAGE             <null>		ANALYZED	703
22	TABLE ACCESS		FULL              HIT
		<null>		ANALYZED	77

Now, I agree that splitting the query into two will be much faster. The data is not changing very rapidly so the queries not looking at the "same picture" shouldn't be that much of a concern.

I originally set out to do this query in this manner for two reasons:

1. It's a web based application. I'm shying away from doing any iterative processing since that creates more roundtrips between the application server and the database. If could get the answer in a single roundtrip it would be better (but obviously not in this case).
2. This is a decision support application. Oracle along with IBM have been touting that now you can push alot of this type of functionality down to the backend. They've added numerous statistical functions and "olap" functions and they've even submitted a modification to ANSI to have some of these things become part of the SQL standard. Even in 8i, Oracle claims that Top N queries are now optimized. This type of query is such a common one in a decision support environment, "Show me all the details of my top customer (or org)..." I just thought that inlight of the direction that they were moving in, that the DB should be able to handle this query.

I appreciate everyone's responses, I will also try adding the USE_NL hint and let you know how it fairs.

Thanks,
Gavin

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Sat Jun 24 2000 - 00:00:00 CDT