Grouping

[pkohli88]

Hi,
i have a table with various columns but i need to Grouping on two columns...1) Group Name 2) Priority.
Moreover I have to choose only certain Groups from Group Name List.
The Problem I am facing is When i Do grouping only on Group Name , it takes only couple of seconds but when i do grouping on both Group Name & Priority , it takes ard 3 min. but if i remove 3-4 groups from the group list then again grouping on both group name and priority happens very quick.

fyi...the no of groups i am selecting is ard 35-40.does this effect the performance?

for example code

Select
grp,priority

FROM PROBLEM

WHERE
grp IN ('X1DCSEMEASCM',........,'X2DCSGBTECBMTOSD','X2DCSGBTECBMTUNIX3')
GROUP BY grp,priority

 

[SantaMufasa]

Pkohli,

This alternative may work faster for you. 1) Create a table that contains the 35-40 group labels:

CREATE TABLE groups (grp_label varchar2(30));
INSERT INTO groups VALUES ('X1DCSEMEASCM');
INSERT INTO groups VALUES ('...');
INSERT INTO groups VALUES ('X2DCSGBTECBMTOSD');
INSERT INTO groups VALUES ('X2DCSGBTECBMTUNIX3');
COMMIT;

Then, run this query:

Select grp,priority
FROM (SELECT DISTINCT grp, priority from PROBLEM)
WHERE exists (SELECT 'X'
                FROM groups
               WHERE group = grp_label)
ORDER BY grp,priority;

Let us know if this is any quicker.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]