OraFAQ Forum: SQL & PL/SQL » performance issue

Home » SQL & PL/SQL » SQL & PL/SQL » performance issue

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

performance issue [message #131118]

Thu, 04 August 2005 08:14

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

hi grp.

I have been to ask the script which takes arround 12-18 hours and been ask to redcue the time.

There are couple of things which I have pointed out we can help to reduce the problem but i'm bit confuse at one point

the database version is 8.1.7.4 on solaris 2.8 64 bit

the script does this......
CREATE TABLE load_PDR_GROUP_tmp as
SELECT MAX(pdr_group_id) as pdr_group_id
FROM audit_pdr_group at2
GROUP BY business_org
, baes_site
, emp_cat
, baes_grade
, baes_job_code
, baes_exec_marker
, employee_Catg
, YESNO
, baes_overtime_paid
, baes_pdr_group;

ALTER TABLE load_pdr_group_tmp
ADD CONSTRAINT load_pdr_group_tmp_fk
FOREIGN KEY (pdr_group_id) REFERENCES audit_pdr_group (pdr_group_id);

ANALYZE TABLE load_pdr_group_tmp
COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES;

INSERT INTO load_pdr_group
(
business_org
, baes_site
, emp_cat
, baes_grade
, baes_job_code
, baes_exec_marker
, employee_catg
, yesno
, baes_overtime_paid
, baes_pdr_group
, pdr_group_id
)
SELECT at.business_org
, at.baes_site
, at.emp_cat
, at.baes_grade
, at.baes_job_code
, at.baes_exec_marker
, at.employee_catg
, at.yesno
, at.baes_overtime_paid
, at.baes_pdr_group
, at.pdr_group_id
FROM audit_pdr_group at,
load_pdr_group_tmp at2
WHERE at.pdr_group_ID = at2.pdr_group_ID
AND ((at.exception IS NULL OR exception !='Y' )
AND(at.deleted IS NULL OR deleted !='Y'));

Is there any other way can i re-write this.. cos it is taking a hell lot of time

number of rows in audit_pdr_group is > 1 million.

I have no idea of why they have use Analyse table clause...
I have never use this before also.

Please advice

Puneet Sachar

Report message to a moderator

Re: performance issue [message #131136 is a reply to message #131118]

Thu, 04 August 2005 10:17

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Why not do everything in one step, using analytic functions? You can bypass that annoying temporary table altogether.

INSERT INTO load_pdr_group (
    business_org
,   baes_site
,   emp_cat
,   baes_grade
,   baes_job_code
,   baes_exec_marker
,   employee_catg
,   yesno
,   baes_overtime_paid
,   baes_pdr_group
,   pdr_group_id
)
SELECT x.business_org
,      x.baes_site
,      x.emp_cat
,      x.baes_grade
,      x.baes_job_code
,      x.baes_exec_marker
,      x.employee_catg
,      x.yesno
,      x.baes_overtime_paid
,      x.baes_pdr_group
,      x.pdr_group_id
FROM  (SELECT apg.business_org
       ,      apg.baes_site
       ,      apg.emp_cat
       ,      apg.baes_grade
       ,      apg.baes_job_code
       ,      apg.baes_exec_marker
       ,      apg.employee_catg
       ,      apg.yesno
       ,      apg.baes_overtime_paid
       ,      apg.baes_pdr_group
       ,      apg.pdr_group_id
       ,      NVL(apg.exception,'X') exception
       ,      NVL(apg.deleted,  'X') deleted
       ,      MAX(apg.pdr_group_id)
              OVER (PARTITION BY apg.business_org
                    ,            apg.baes_site
                    ,            apg.emp_cat
                    ,            apg.baes_grade
                    ,            apg.baes_job_code
                    ,            apg.baes_exec_marker
                    ,            apg.employee_catg
                    ,            apg.yesno
                    ,            apg.baes_overtime_paid
                    ,            apg.baes_pdr_group
                    ,            apg.pdr_group_id) max_pdr_group_id
       FROM         audit_pdr_group  apg)          x
WHERE  x.pdr_group_id = x.max_pdr_group_id
AND    x.exception   != 'Y'
AND    x.deleted     != 'Y'
/

Note, please compare the results of the above SELECT with yours, to be sure that the results are identical, before you actually run the INSERT.

Report message to a moderator

Re: performance issue [message #131152 is a reply to message #131136]

Thu, 04 August 2005 11:51

puneetsachar
Messages: 97
Registered: March 2005
Location: New Delhi, India

Member

thanks Art Metzer
and thanks group,

Is this Function valid in DB-8.1.7.4
Also tell me if I go for bulk approach which is better in we have more than 1 million of records

also people here told me whith this script they faces snapshort too old error
ORA:01555 error....

Does these insert the one example i showed but there r 17 like this inserts...are they playing some role... for this error..

Regards
Puneet sachar

Report message to a moderator

Re: performance issue [message #131156 is a reply to message #131152]

Thu, 04 August 2005 12:15

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

The function will be valid in 8.1.7.4 SQL, yes. In straight SQL, analytic functions have been around since version 8.1.6.

A million rows really isn't all that much....why don't you just run this code in your test environment, and see how it behaves there?

Report message to a moderator

Previous Topic:	Need help for designing a "tough" query
Next Topic:	explode a bill of materials

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 18:54:53 CST 2024