Query performance issue

[sonaliak]

Please help !
I have a prity simple query but in reality it takes a long time.
SELECT ProjectCode_Item_Code
FROM mwebProjectCode
WHERE ProjectCode_Item_Code
NOT IN(select distinct Work_Item_Code from mwebWork where Work_Level=6
and Work_Entity_Type=3
and Work_ID <> 2531)
ORDER BY ProjectCode_Item_Code;

I also tried:
SELECT ProjectCode_Item_Code
FROM mwebProjectCode
WHERE NOT EXISTS(select work_id from mwebWork where Work_Level=6 and Work_Entity_Type=3
and Work_Item_Code=ProjectCode_Item_Code and Work_ID <> 2531)
ORDER BY ProjectCode_Item_Code;

It returns 45 rows selected which is small number.

The execution plan for both is same:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
5 4 AND-EQUAL
6 5 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQUE)
7 5 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQUE
)





Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
94702 consistent gets
0 physical reads
0 redo size
1246 bytes sent via SQL*Net to client
1081 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
45 rows processed

SQL>
mwebProjectCode has 425 rows and mwebwork(subquery has 6000 rows and distinct records from mwebwork are just 380 rows.
Why does this has this big 94702 consistent gets and why does it take long time for execution ?

Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 7.22 (Elapsed) 0.00 (CPU)
Total 7.23 0.00



Thanks

[chrisrlong]

I've gotta run right now, so I can't get into all the details, but try this instead:

SELECT
---PC.ProjectCode_Item_Code
FROM
---mwebProjectCode---PC---,
---mwebWork------------W
WHERE
---------PC.Work_Entity_Type---------=---3
---AND---PC.Work_ID------------------<> 2531
---AND---W.Work_Item_Code---------(+)=---PC.ProjectCode_Item_Code
---AND---W.Work_Level------------(+)=---6
---AND---W.Work_Item_Code------------IS---NULL
ORDER BY
---ProjectCode_Item_Code


HTH,

- Chris

[chrisrlong]

Oops, read your query wrong. My bad.

Try this:

SELECT
---PC.ProjectCode_Item_Code
FROM
---mwebProjectCode---PC---,
---(
---SELECT DISTINCT
------Work_Item_Code
---FROM
------mwebWork
---WHERE
---------Work_Entity_Type---=---3
---AND---Work_ID------------<> 2531
---AND---Work_Level---------=---6
---)------------------W
WHERE
---------W.Work_Item_Code---(+)=---PC.ProjectCode_Item_Code
---AND---W.Work_Item_Code---IS---NULL
ORDER BY
---PC.ProjectCode_Item_Code

I had to change the structure because of the <>.

The basic concept is that NOT IN and NOT EXISTS are generally rather painful to do. Better to do an outer join, which will put NULLS in all the columns for the W table when the PC has an entry that the W table does not (NOT IN), then restrict the result set to only those values (W.NonNullableColumn IS NULL). Usually, you will use the PK or the actual join field to do this.

HTH,

- Chris

[sonaliak]

thanks chris,
This took much longer. I think it did not like outer join !!
Any ways, after you replied I realized that I can use MINUS which work at lightning speed.

Select ProjectCode_Item_Code
FROM mwebProjectCode
MINUS
SELECT Work_Item_Code
FROM mwebWork
WHERE Work_Entity_Type = 3
AND Work_ID <> 2531
AND Work_Level= 6
order by ProjectCode_Item_Code ;
Thanks again

[chrisrlong]

Would you mind posting the plans for both? I'd really like to see how it handled them.

Thanks,

- Chris

[sonaliak]

Here are the plans, can you throw light on these if you find something ?

SQL> SELECT PC.ProjectCode_Item_Code
2 FROM mwebProjectCode PC , mwebWork W
3 WHERE w.Work_Entity_Type(+) = 3
4 AND w.Work_ID(+) <> 2531
5 AND W.Work_Item_Code(+)= PC.ProjectCode_Item_Code
6 AND W.Work_Level(+)= 6
7 AND W.Work_Item_Code IS NULL
8 ORDER BY ProjectCode_Item_Code ;

45 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 NESTED LOOPS (OUTER)
4 3 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
6 5 AND-EQUAL
7 6 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQ
UE)

8 6 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQU
E)





Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
170444 consistent gets
0 physical reads
0 redo size
1251 bytes sent via SQL*Net to client
1110 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
45 rows processed

SQL>
SQL>
SQL> Select ProjectCode_Item_Code
2 FROM mwebProjectCode
3 MINUS
4 SELECT Work_Item_Code
5 FROM mwebWork
6 WHERE Work_Entity_Type = 3
7 AND Work_ID <> 2531
8 AND Work_Level= 6
9 order by ProjectCode_Item_Code ;

45 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MINUS
2 1 SORT (UNIQUE)
3 2 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
4 1 SORT (UNIQUE)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
6 5 AND-EQUAL
7 6 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQUE
)

8 6 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
418 consistent gets
0 physical reads
0 redo size
1251 bytes sent via SQL*Net to client
1040 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
45 rows processed

I even tried this:
------------------
SQL> SELECT ProjectCode_Item_Code
2 FROM mwebProjectCode
3 WHERE ProjectCode_Item_Code
4 NOT IN(select distinct Work_Item_Code from mwebWork where Work_Level=6
5 and Work_Entity_Type=3
6 and Work_ID <> 2531)
7 ORDER BY ProjectCode_Item_Code;

45 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
5 4 AND-EQUAL
6 5 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQUE)
7 5 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQUE
)





Statistics
----------------------------------------------------------
264 recursive calls
4 db block gets
94842 consistent gets
266 physical reads
0 redo size
1249 bytes sent via SQL*Net to client
1081 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
45 rows processed

Original one:

Parse 0.05 (Elapsed) 0.00 (CPU)
Execute/Fetch 6.50 (Elapsed) 0.00 (CPU)
Total 6.55 0.00

SQL>
SQL>
SQL> SELECT ProjectCode_Item_Code
2 FROM mwebProjectCode
3 WHERE NOT EXISTS(select work_id from mwebWork where Work_Level=6 and Work_Entity_Type=3
4 and Work_Item_Code=ProjectCode_Item_Code and Work_ID <> 2531)
5 ORDER BY ProjectCode_Item_Code;

45 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'MWEBPROJECTCODE'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBWORK'
5 4 AND-EQUAL
6 5 INDEX (RANGE SCAN) OF 'IX_WORK_LEVEL' (NON-UNIQUE)
7 5 INDEX (RANGE SCAN) OF 'IX_WORK_ENTITY' (NON-UNIQUE
)





Statistics
----------------------------------------------------------
229 recursive calls
3 db block gets
94839 consistent gets
0 physical reads
0 redo size
1251 bytes sent via SQL*Net to client
1080 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
45 rows processed

Only the MINUS one takes 0.12 seconds, outer join takes 13 seconds and rest 9 seconds

Thanks again Chris