Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 10g)
Query Tuning [message #356598] Fri, 31 October 2008 07:30 Go to next message
rsoma
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member
Hi

Is that a better way to tune the following query. This table contains around 3 millions of record.

SELECT a.workflow_task_ods_id, 
       CASE
          WHEN (SELECT MIN (b.rework_version_number)
                  FROM workflow_task b
                 WHERE b.workflow_job_ods_id =
                                            a.workflow_job_ods_id
                   AND b.workflow_task_name = a.workflow_task_name) =
                                                       a.rework_version_number
             THEN 'Y'
          ELSE 'N'
       END min_version,
       CASE
          WHEN (SELECT MAX (b.rework_version_number)
                  FROM workflow_task b
                 WHERE b.workflow_job_ods_id =
                                            a.workflow_job_ods_id
                   AND b.workflow_task_name = a.workflow_task_name) =
                                                       a.rework_version_number
             THEN 'Y'
          ELSE 'N'
       END max_version,
       SYSDATE AS dw_effect_date
  FROM workflow_task a



Explain Plan

Rows    Plan
3277060  SELECT STATEMENT  


Thanks in advance
Soma.

[Updated on: Fri, 31 October 2008 07:35]

Report message to a moderator

Re: Query Tuning [message #356642 is a reply to message #356598] Fri, 31 October 2008 12:55 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Didnt get the full explain plan there, but try something like this:- (Dont have access to a terminal but the logic should be ok).

Ive done the MIN version, you should be able to apply the MAX version by using the same logic (just so I dont give you the 100% answer as required by board rules).

SELECT a.workflow_task_ods_id, 
       CASE
          WHEN MIN(rework_version_number) over (partition by workflow_job_ods_id, workflow_task_name) = rework_version_number
             THEN 'Y'
          ELSE 'N'
       END min_version,
       SYSDATE AS dw_effect_date
  FROM workflow_task a;

[Updated on: Fri, 31 October 2008 12:55]

Report message to a moderator

Re: Query Tuning [message #357213 is a reply to message #356598] Tue, 04 November 2008 05:26 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - your approach is incorrect - you are doing row-by-row (slow-by-slow) search for MIN and MAX values:

FOR EACH ROW IN table LOOP
  SELECT MIN...
  SELECT MAX...
END LOOP


So your statement performs FULL table scan of 3M rows +
3M times it performs SELECT MIN and another 3M times - SELECT MAX.
Even if you have an index on ( workflow_job_ods_id,workflow_task_name ) - it will take time.


Try following query instead:

SELECT a.workflow_task_ods_id,
  CASE WHEN a.rework_version_number = b.MIN_VERSION
       THEN 'Y' ELSE 'N' END min_version,
  CASE WHEN a.rework_version_number = b.MAX_VERSION
       THEN 'Y' ELSE 'N' END max_version,
  SYSDATE AS dw_effect_date
FROM workflow_task a,
  (SELECT workflow_job_ods_id, 
          workflow_task_name, 
          MIN (rework_version_number) MIN_VERSION,
          MAX (rework_version_number) MAX_VERSION
   FROM workflow_task
   GROUP BY workflow_task_ods_id, 
          workflow_job_ods_id ) b
WHERE b.workflow_job_ods_id = a.workflow_job_ods_id
  AND b.workflow_task_name = a.workflow_task_name


HTH.
Michael
Re: Query Tuning [message #357295 is a reply to message #356598] Tue, 04 November 2008 13:52 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
IO wise, my query is much more efficient, as it will read the table only once, although perform a window sort on the rows.

Michaels query will read the table twice if you are lucky, otherwise, it may just unnest his inline query and do a nested loops query, which will probably be really slow.

I guess if your box is CPU bound, michaels 2 table scans might perform better, but otherwise, a single table scan is all you should need.

[Updated on: Tue, 04 November 2008 13:52]

Report message to a moderator

Previous Topic: Gather statistics for scale up / down decision process
Next Topic: Purgeing data
Goto Forum:
  


Current Time: Sat Dec 03 18:11:47 CST 2016

Total time taken to generate the page: 0.07426 seconds