Query tuning [message #495594] |
Mon, 21 February 2011 05:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/ed87960bfb924be8ed44fcc437cf594b?s=64&d=mm&r=g) |
preethavinay
Messages: 5 Registered: February 2011
|
Junior Member |
|
|
Please tune the below query since it is running slowly:
select /*+ CHOOSE */ accession, patientkey, max(testreqkey) as testreqkey from testreq where accession || ' ' || patientkey in (select distinct accession || ' ' || patientkey from testreq) group by accession, patientkey;
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=789995 Card=60164844 Bytes=2226099
228)
1 0
SORT (GROUP BY) (Cost=789995 Card=60164844 Bytes=2226099228)
2 1
HASH JOIN (Cost=1246 Card=60164844 Bytes=2226099228)
3 2
VIEW OF 'index$_join$_002' (VIEW) (Cost=322 Card=77566 Bytes=1163490)
4 3
HASH JOIN
5 4
INDEX (FAST FULL SCAN) OF 'AK_TESTREQ_ACCESSIONS' (INDEX (UNIQUE)) (Co
st=57 Card=77566 Bytes=1163490)
6 4
INDEX (FAST FULL SCAN) OF 'IX_TESTREQ_PATIENT2' (INDEX) (Cost=46 Card=
77566 Bytes=1163490)
7 2
VIEW OF 'index$_join$_001' (VIEW) (Cost=697 Card=77566 Bytes=1706452)
8 7
HASH JOIN
9 8
HASH JOIN
10 9
INDEX (FAST FULL SCAN) OF 'AK_TESTREQ_ACCESSIONS' (INDEX (UNIQUE)) (
Cost=57 Card=77566 Bytes=1706452)
11 9
INDEX (FAST FULL SCAN) OF 'IX_TESTREQ_PATIENT2' (INDEX) (Cost=46 Car
d=77566 Bytes=1706452)
12 8
INDEX (FAST FULL SCAN) OF 'PK_TESTREQ' (INDEX (UNIQUE)) (Cost=35 Card=
77566 Bytes=1706452)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
131 consistent gets
0 physical reads
172 redo size
3542 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
213 rows processed
------------------
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4066303761
-----------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | HASH JOIN | |
| 3 | VIEW | index$_join$_002 |
| 4 | HASH JOIN | |
| 5 | INDEX FAST FULL SCAN | AK_TESTREQ_ACCESSIONS |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | INDEX FAST FULL SCAN | IX_TESTREQ_PATIENT2 |
| 7 | VIEW | index$_join$_001 |
| 8 | HASH JOIN | |
| 9 | HASH JOIN | |
| 10 | INDEX FAST FULL SCAN| AK_TESTREQ_ACCESSIONS |
| 11 | INDEX FAST FULL SCAN| IX_TESTREQ_PATIENT2 |
| 12 | INDEX FAST FULL SCAN | PK_TESTREQ |
-----------------------------------------------------------
19 rows selected.
|
|
|
|
Re: Query tuning [message #495598 is a reply to message #495594] |
Mon, 21 February 2011 05:30 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
OraKaran
Messages: 183 Registered: March 2008 Location: United Kingdom
|
Senior Member |
|
|
Hello preethavinay
Do you really need "distinct" in the subquery? Is it making any difference if you remove the distinct clause?
Regards,
OraKaran
|
|
|
Re: Query tuning [message #495600 is a reply to message #495596] |
Mon, 21 February 2011 05:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
select /*+ CHOOSE */ accession, patientkey, max(testreqkey) as testreqkey
from testreq
where accession || ' ' || patientkey in (select distinct accession || ' ' || patientkey from testreq)
group by accession, patientkey;
What exactly do you think that where clause is doing? Cause as far as I can see the only thing it's doing is slowing the query down.
|
|
|
|
Re: Query tuning [message #495733 is a reply to message #495676] |
Tue, 22 February 2011 02:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Can you define 'slowly'? Those stats say there were 0 pio and 131 lio....that's got to be screaming fast, or those numbers are wrong.
Reason I ask is, there comes a point where its just not going to get much faster no matter what you do and I've never seen a query with those stats be "slow".
|
|
|
Re: Query tuning [message #495770 is a reply to message #495676] |
Tue, 22 February 2011 03:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
preethavinay wrote on Tue, 22 February 2011 05:23The query is preventing duplicate accessions across patients, that is why ||''|| is used in the where clause, so that the patientkey and accession can be taken together.
No it doesn't. The where clause you have gives exactly the same rows back as
Only does it slower.
Try it.
preethavinay wrote on Tue, 22 February 2011 05:23
Do you have any other suggestions that can implement the same?
Not without a test case to show us what you're really trying to achieve. We don't know anything about your tables or data other than what you've shown us.
|
|
|
Re: Query tuning [message #498598 is a reply to message #495770] |
Thu, 10 March 2011 07:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
marcodba
Messages: 5 Registered: August 2006
|
Junior Member |
|
|
"Max" function and "Group By" will give you the max(testreqkey) for each accession and patientkey pair (no duplicate here).
You can simplify your query with:
select /*+ CHOOSE */
accession,
patientkey,
max(testreqkey) as testreqkey
from testreq
) group by
accession,
patientkey;
Do you have a reason for using CHOOSE hint?
|
|
|