| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Strange explain plan
Hi all.
I'm having problems with the following query:
select a1.MBR_TYP_CDE MBR_TYP_CDE,
(COUNT ( DISTINCT a1.NOVS_NTWRK_MBR_KYD ))
from MDSS_STAR.NOVUS_NETWRK_MBR_D a1
group by a1.MBR_TYP_CDE
It fails with ora-06000 (parameter 15851). I have
noticed that if I created the new table
NOVUS_NETWRK_MBR_D_tmp as select * from
NOVUS_NETWRK_MBR_D the query runs with no issues.
I have compared the explain plans for the two queries and here what i saw. This is the plan for the query that runs OK
0-0-9721 1.9721 SELECT STATEMENT SQL1 Cost = 9721 1-0-1 2.1 SORT GROUP BY 2-1-1 3.1 TABLE ACCESS FULL NOVUS_NETWRK_MBR_D_TMP
This is the plan for the query that fails.
0-0-5898 1.5898 SELECT STATEMENT SQL1 Cost = 5898 1-0-1 2.1 SORT GROUP BY 2-1-1 3.1 SORT GROUP BY 3-2-1 4.1 SORT GROUP BY 4-3-1 5.1 TABLE ACCESS FULL NOVUS_NETWRK_MBR_D
I can't understand why would oracle do three sorts on that query. May be this is somehow related to my ora-0600. If someone has any ideas as to what can be going on here I would really appreciate it. We are running oracle 817 in ops on 4.3.3 IBM SP/2
thanks in advance
Gene
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Jul 05 2001 - 15:24:41 CDT
 |
 |