OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Tuning [message #356598]

Fri, 31 October 2008 07:30

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

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

michael_bialik
Messages: 621
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

Report message to a moderator

Re: Query Tuning [message #357295 is a reply to message #356598]

Tue, 04 November 2008 13:52

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 02:58:21 CST 2024