Hi All,
Database Version: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
Database Version: PL/SQL Release 10.2.0.5.0 - Production
I've written a query which runs in under a second and produces a few hundred rows (as you can see below). I only wanted to find the number of distinct incident ID's returned so I put a group by at the end and it ran for ever.
I produced an explain plan (as shown below) for the query with the group by in it and it is trying to perform a cartesian join!
WTF is going on? I've never known a group by statement to do that before.
basic structure of the tables is as follows:
SQL> desc answer_master
Name Null? Type
------------------------------------------------------------------------ -------- -----------------
ANSWER_ID NOT NULL NUMBER(12)
INCIDENT_ID NOT NULL NUMBER(12)
PLAN_ID NOT NULL NUMBER(12)
QUESTION_ID NOT NULL NUMBER(12)
SQL> desc question_master
Name Null? Type
------------------------------------------------------------------------ -------- -----------------
QUESTION_PLAN_ID NOT NULL NUMBER(12)
QUESTION_ID NOT NULL NUMBER(12)
QUESTION_TYPE NOT NULL NUMBER(2)
QUESTION_TEXT NOT NULL VARCHAR2(255)
QUESTION_PARENT_ID NUMBER(12)
the offending SQL statement
SQL> explain plan for
2 select incident_id
3 from answer_master am
4 where exists (select 'x'
5 from answer_master am1
6 where question_id in (select question_id
7 from question_master qms
8 where question_plan_id = 1477
9 and question_parent_id = 69067
10 and substr(question_text,-3) = 'PDF')
11 and am1.incident_id = am.incident_id)
12 and exists (select 'x'
13 from answer_master am1
14 where question_id in (select question_id
15 from question_master qms
16 where question_plan_id = 1477
17 and question_parent_id = 69067
18 and substr(question_text,-3) != 'PDF')
19 and am1.incident_id = am.incident_id);
Explained.
Elapsed: 00:00:00.01
SQL> set linesize 132;
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710257923
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 171 (6)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 32 | 171 (6)| 00:00:01 |
| 2 | VIEW | VW_SQ_2 | 71 | 923 | 70 (5)| 00:00:01 |
| 3 | NESTED LOOPS | | 71 | 3266 | 70 (5)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | QUESTION_MASTER | 1 | 35 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | QUESTION_MASTER_IX1 | 3 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX2 | 18731 | 201K| 68 (5)| 00:00:01 |
| 7 | NESTED LOOPS | | 304 | 5776 | 100 (5)| 00:00:01 |
| 8 | VIEW | VW_SQ_1 | 14 | 182 | 70 (5)| 00:00:01 |
| 9 | HASH UNIQUE | | 14 | 644 | | |
| 10 | NESTED LOOPS | | 14 | 644 | 70 (5)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| QUESTION_MASTER | 1 | 35 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | QUESTION_MASTER_IX1 | 3 | | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX2 | 18731 | 201K| 68 (5)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX3 | 22 | 132 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_2"="AM"."INCIDENT_ID")
4 - filter("QUESTION_PLAN_ID"=1477 AND SUBSTR("QUESTION_TEXT",-3)!='PDF')
5 - access("QUESTION_PARENT_ID"=69067)
6 - access("QUESTION_ID"="QUESTION_ID")
11 - filter("QUESTION_PLAN_ID"=1477 AND SUBSTR("QUESTION_TEXT",-3)='PDF')
12 - access("QUESTION_PARENT_ID"=69067)
13 - access("QUESTION_ID"="QUESTION_ID")
14 - access("ITEM_1"="AM"."INCIDENT_ID")
33 rows selected.
Elapsed: 00:00:00.06
SQL> explain plan for
2 select incident_id
3 from answer_master am
4 where exists (select 'x'
5 from answer_master am1
6 where question_id in (select question_id
7 from question_master qms
8 where question_plan_id = 1477
9 and question_parent_id = 69067
10 and substr(question_text,-3) = 'PDF')
11 and am1.incident_id = am.incident_id)
12 and exists (select 'x'
13 from answer_master am1
14 where question_id in (select question_id
15 from question_master qms
16 where question_plan_id = 1477
17 and question_parent_id = 69067
18 and substr(question_text,-3) != 'PDF')
19 and am1.incident_id = am.incident_id)
20 group by incident_id;
Explained.
Elapsed: 00:00:00.00
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1433543102
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 77 (6)| 00:00:01 |
| 1 | HASH GROUP BY | | 1 | 98 | 77 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 98 | 76 (4)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 87 | 74 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 81 | 72 (5)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 70 | 4 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | QUESTION_MASTER | 1 | 35 | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | QUESTION_MASTER_IX1 | 3 | | 1 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 35 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID| QUESTION_MASTER | 1 | 35 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | QUESTION_MASTER_IX1 | 3 | | 1 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX2 | 18731 | 201K| 68 (5)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX3 | 22 | 132 | 2 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | ANSWER_MASTER_QID_IX2 | 1 | 11 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("QUESTION_PLAN_ID"=1477 AND SUBSTR("QUESTION_TEXT",-3)='PDF')
7 - access("QUESTION_PARENT_ID"=69067)
9 - filter("QUESTION_PLAN_ID"=1477 AND SUBSTR("QUESTION_TEXT",-3)!='PDF')
10 - access("QUESTION_PARENT_ID"=69067)
11 - access("QUESTION_ID"="QUESTION_ID")
12 - access("AM1"."INCIDENT_ID"="AM"."INCIDENT_ID")
13 - access("QUESTION_ID"="QUESTION_ID" AND "AM1"."INCIDENT_ID"="AM"."INCIDENT_ID")
31 rows selected.
Elapsed: 00:00:00.00
SQL> spool off
edit: changing greater-than/less-than signs to != so it displays on forum
Edited by: WhiteHat on Feb 11, 2011 9:37 AM