Home » RDBMS Server » Performance Tuning » slow sql
| slow sql [message #613399] |
Tue, 06 May 2014 06:17  |
 |
fenor
Messages: 4
Registered: May 2014
|
Junior Member |
|
|
Hi!
For example I have the following tables:
maintable (
maintableid NUMBER(12,0),
baseid NUMBER(12,0),
type NUMBER(1,0),
name NVARCHAR2(50),
PRIMARY KEY (maintableid),
FOREIGN KEY (baseid) REFERENCES maintable (maintableid)
);
subtable (
subtableid NUMBER(12,0),
maintableid NUMBER(12,0),
groupcode NUMBER(2,0),
status NUMBER(3,0),
PRIMARY KEY (subtableid),
FOREIGN KEY (maintableid) REFERENCES maintable (maintableid),
CONSTRAINT CHK_SUBTABLE_GROUPCODE CHECK (groupcode IN (1,10)),
CONSTRAINT CHK_SUBTABLE_STATUS CHECK (status IN (0,100))
);
CREATE INDEX "I_ST_INDEX1" ON subtable (maintableid, groupcode, STATUS);
CREATE INDEX "I_ST_INDEX2" ON subtable (maintableid);
I need all the minimum of subtable.status grouped by groupcode in one list.
What makes the problem a bit more complicated is, that more maintable rows can be linked together with the maintable.baseid.
In the case the maintable.type is 9 then I need the minimum of subtable.status of every maintable row linked together not just one maintable row.
I wrote the following sql that gives a good result but is is extremly slow:
Select m.maintableid, m.name, s.status_1, s.status_2, ..., s.status_10
FROM (
SELECT mt.maintableid,
(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 1) AS status_1,
(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 2) AS status_2,
...
(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 10) AS status_10,
FROM maintable mt
WHERE mt.type != 9
UNION ALL
SELECT mt.baseid,
MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 1) AS status_1,
MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 2) AS status_2,
...
MIN(SELECT min(st.status) FROM subtable st WHERE st.maintableid = mt.maintableid AND groupcode = 10) AS status_10,
FROM maintable mt
WHERE mt.type = 9
GROUP BY mt.baseid
) s
JOIN maintable m ON (m.maintableid = s.maintableid)
To make it more quicker I made a table with the structure of the result of the previous sql:
maintable (
maintableid NUMBER(12,0),
status_1 NUMBER(3,0),
status_2 NUMBER(3,0),
...
status_10 NUMBER(3,0),
PRIMARY KEY (maintableid),
FOREIGN KEY (maintableid) REFERENCES maintable (maintableid)
);
and I wanted to fill it with after triggers but it had not worked. A wrote a procedure witch worked fine but if I called it from the trigger it did nothing.
As I read triggers are rarely a good solution.
Now I am stucked. Has somebody an idea how can I solve this problem?
Thanks.
|
|
|
|
|
|
| Re: slow sql [message #613421 is a reply to message #613399] |
Tue, 06 May 2014 08:04  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You are projecting all those scalar subqueries. They are going to run as correlated subqueries, which will be pretty slow. Can you not re-write the query to use a join instead?
|
|
|
|
| Re: slow sql [message #613429 is a reply to message #613404] |
Tue, 06 May 2014 10:32 |
|
fenor
Messages: 4
Registered: May 2014
|
Junior Member |
|
|
This is the ddl:
CREATE TABLE "MINTA"
( "MINTAID" NUMBER(12,0) NOT NULL ENABLE,
"TIPUS" NUMBER(1,0) NOT NULL ENABLE,
"BAZISID" NUMBER(12,0) NOT NULL ENABLE,
"STATUS" NUMBER(3,0) DEFAULT 2 NOT NULL ENABLE,
"MINTATIPUS" NUMBER(2,0) NOT NULL ENABLE,
"SURGOS" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"MINOSITES" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
PRIMARY KEY ("MINTAID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "I_M_MTIPUSSTATUS" ON "MINTA" ('MINTATIPUS', 'STATUS')
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE OR REPLACE TRIGGER "T_AU_MINTA"
AFTER UPDATE ON MINTA
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaStatus(:NEW.mintaid);
COMMIT;
END;
/
ALTER TRIGGER "T_AU_MINTA" ENABLE;
CREATE OR REPLACE TRIGGER "T_AD_MINTA"
AFTER DELETE ON MINTA
REFERENCING OLD AS OLD FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaStatus(:OLD.mintaid);
COMMIT;
END;
/
ALTER TRIGGER "T_AD_MINTA" ENABLE;
CREATE OR REPLACE TRIGGER "T_AI_MINTA"
AFTER INSERT ON MINTA
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaStatus(:NEW.mintaid);
COMMIT;
END;
/
ALTER TRIGGER "T_AI_MINTA" ENABLE;
CREATE TABLE "MINTA_VIZSGALAT"
( "MINTA_VIZSGALATID" NUMBER(12,0) NOT NULL ENABLE,
"MINTAID" NUMBER(12,0) NOT NULL ENABLE,
"STATUS" NUMBER(3,0) DEFAULT 2 NOT NULL ENABLE,
"MINOSITES" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE,
"CSOPORT" NUMBER(3,0) NOT NULL ENABLE,
"ACTIVE" NUMBER(1,0) DEFAULT 1,
PRIMARY KEY ("MINTA_VIZSGALATID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
CONSTRAINT "FK_MBL_V_MINTAID" FOREIGN KEY ("MINTAID")
REFERENCES "MINTA" ("MINTAID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2097152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "I_MV_MIDCSAS" ON "MINTA_VIZSGALAT" ('MINTAID', 'CSOPORT', 'ACTIVE', 'STATUS')
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 524288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE INDEX "I_MV_MINTAID" ON "MINTA_VIZSGALAT" ('MINTAID')
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE OR REPLACE TRIGGER "T_AD_MINTAVIZSGALAT"
AFTER DELETE ON MINTA_VIZSGALAT
REFERENCING OLD AS OLD FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaCsoportStatus(:OLD.mintaid, :OLD.csoport);
COMMIT;
END;
/
ALTER TRIGGER "T_AD_MINTAVIZSGALAT" ENABLE;
CREATE OR REPLACE TRIGGER "T_AU_MINTAVIZSGALAT"
AFTER UPDATE ON MINTA_VIZSGALAT
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaCsoportStatus(:NEW.mintaid, :NEW.csoport);
COMMIT;
END;
/
ALTER TRIGGER "T_AU_MINTAVIZSGALAT" ENABLE;
CREATE OR REPLACE TRIGGER "T_AI_MINTAVIZSGALAT"
AFTER INSERT ON MINTA_VIZSGALAT
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
setMintaCsoportStatus(:NEW.mintaid, :NEW.csoport);
COMMIT;
END;
/
ALTER TRIGGER "T_AI_MINTAVIZSGALAT" ENABLE;
The original sql is:
SELECT m.mintaid,
m.tipus,
m.mintatipus,
m.surgos,
CASE
WHEN m.status = 100 THEN opm.minosites
ELSE
CASE
WHEN opm.vminosites = 2 THEN 2
ELSE -1
END
END AS minosites,
CASE
WHEN m.surgos = 1
AND opm.status < 70 THEN 1
ELSE 0
END AS hilight,
opm.status AS status,
csoportstatus_1,
csoportstatus_2,
csoportstatus_3,
csoportstatus_4,
csoportstatus_5,
csoportstatus_6,
csoportstatus_7,
csoportstatus_8,
csoportstatus_9,
csoportstatus_10
FROM (SELECT m.mintaid,
m.status,
m.minosites,
(SELECT Max(minmv.minosites)
FROM minta_vizsgalat minmv
WHERE minmv.mintaid = m.mintaid
AND minmv.active = 1
AND minmv.status >= 50
AND minmv.status <= 100) AS vminosites,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 1) AS csoportstatus_1,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 2) AS csoportstatus_2,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 3) AS csoportstatus_3,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 4) AS csoportstatus_4,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 5) AS csoportstatus_5,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 6) AS csoportstatus_6,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 7) AS csoportstatus_7,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 8) AS csoportstatus_8,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 10) AS csoportstatus_10,
(SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 9) AS csoportstatus_9
FROM minta m
WHERE m.mintatipus != 9
UNION ALL
SELECT m.bazisid,
Min(m.status),
Max(m.minosites),
Max((SELECT Max(minmv.minosites)
FROM minta_vizsgalat minmv
WHERE minmv.mintaid = m.mintaid
AND minmv.active = 1
AND minmv.status >= 50
AND minmv.status <= 100)) AS vminosites,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 1)) AS csoportstatus_1,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 2)) AS csoportstatus_2,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 3)) AS csoportstatus_3,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 4)) AS csoportstatus_4,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 5)) AS csoportstatus_5,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 6)) AS csoportstatus_6,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 7)) AS csoportstatus_7,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 8)) AS csoportstatus_8,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 10)) AS csoportstatus_10,
Min((SELECT Min(stmv.status)
FROM minta_vizsgalat stmv
WHERE stmv.mintaid = m.mintaid
AND stmv.active = 1
AND stmv.status >= 50
AND stmv.status <= 100
AND csoport = 9)) AS csoportstatus_9
FROM minta m
WHERE m.tipus != 3
AND m.mintatipus = 9
GROUP BY m.bazisid) opm
join minta m
ON ( opm.mintaid = m.mintaid )
WHERE opm.status < 100
AND opm.status >= 50
AND opm.status <= 60
AND ( opm.status >= '50' )
ORDER BY CASE
WHEN m.surgos = 1
AND opm.status < 70 THEN 1
ELSE 0
END DESC,
mintaid
The explain plan:
Plan hash value: 573517320
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 606 | 110K| 210 (2)| 00:00:03 |
| 1 | SORT ORDER BY | | 606 | 110K| 210 (2)| 00:00:03 |
|* 2 | HASH JOIN | | 606 | 110K| 209 (1)| 00:00:03 |
| 3 | TABLE ACCESS FULL | MINTA | 2838 | 36894 | 69 (0)| 00:00:01 |
| 4 | VIEW | | 606 | 102K| 139 (1)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
| 6 | SORT AGGREGATE | | 1 | 11 | | |
|* 7 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 2 | 22 | 52 (0)| 00:00:01 |
| 8 | SORT AGGREGATE | | 1 | 11 | | |
|* 9 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 11 | | |
|* 11 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 12 | SORT AGGREGATE | | 1 | 11 | | |
|* 13 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 14 | SORT AGGREGATE | | 1 | 11 | | |
|* 15 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 11 | | |
|* 17 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 11 | | |
|* 19 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 20 | SORT AGGREGATE | | 1 | 11 | | |
|* 21 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 11 | | |
|* 23 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 11 | | |
|* 25 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 26 | SORT AGGREGATE | | 1 | 11 | | |
|* 27 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | MINTA | 605 | 6655 | 69 (0)| 00:00:01 |
| 29 | SORT AGGREGATE | | 1 | 11 | | |
|* 30 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 2 | 22 | 52 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 11 | | |
|* 32 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 33 | SORT AGGREGATE | | 1 | 11 | | |
|* 34 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 35 | SORT AGGREGATE | | 1 | 11 | | |
|* 36 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 11 | | |
|* 38 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 39 | SORT AGGREGATE | | 1 | 11 | | |
|* 40 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 41 | SORT AGGREGATE | | 1 | 11 | | |
|* 42 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 43 | SORT AGGREGATE | | 1 | 11 | | |
|* 44 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 45 | SORT AGGREGATE | | 1 | 11 | | |
|* 46 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 47 | SORT AGGREGATE | | 1 | 11 | | |
|* 48 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
| 49 | SORT AGGREGATE | | 1 | 11 | | |
|* 50 | TABLE ACCESS FULL | MINTA_VIZSGALAT | 1 | 11 | 52 (0)| 00:00:01 |
|* 51 | FILTER | | | | | |
| 52 | HASH GROUP BY | | 1 | 18 | 70 (2)| 00:00:01 |
|* 53 | TABLE ACCESS FULL| MINTA | 426 | 7668 | 69 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OPM"."MINTAID"="M"."MINTAID")
7 - filter("MINMV"."MINTAID"=:B1 AND "MINMV"."ACTIVE"=1 AND
"MINMV"."STATUS">=50 AND "MINMV"."STATUS"<=100)
9 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=1 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
11 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=2 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
13 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=3 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
15 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=4 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
17 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=5 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
19 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=6 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
21 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=7 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
23 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=8 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
25 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=10 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
27 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=9 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
28 - filter("SYS_ALIAS_11"."STATUS">=50 AND "SYS_ALIAS_11"."STATUS"<=60 AND
"SYS_ALIAS_11"."MINTATIPUS"<>9)
30 - filter("MINMV"."MINTAID"=:B1 AND "MINMV"."ACTIVE"=1 AND
"MINMV"."STATUS">=50 AND "MINMV"."STATUS"<=100)
32 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=1 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
34 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=2 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
36 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=3 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
38 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=4 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
40 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=5 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
42 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=6 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
44 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=7 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
46 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=8 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
48 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=10 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
50 - filter("STMV"."MINTAID"=:B1 AND "CSOPORT"=9 AND "STMV"."ACTIVE"=1 AND
"STMV"."STATUS">=50 AND "STMV"."STATUS"<=100)
51 - filter(MIN("SYS_ALIAS_11"."STATUS")<100 AND
MIN("SYS_ALIAS_11"."STATUS")>=50 AND MIN("SYS_ALIAS_11"."STATUS")<=60 AND
MIN("SYS_ALIAS_11"."STATUS")>=50)
53 - filter("SYS_ALIAS_11"."MINTATIPUS"=9 AND "SYS_ALIAS_11"."TIPUS"<>3)
115 rows selected
I could not create a trace because the trace file was full of
OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
O/S-Error: (OS 1) Incorrect function. !
I use a 64bit window and XE.
|
|
|
|
|
|
| Re: slow sql [message #613505 is a reply to message #613432] |
Wed, 07 May 2014 10:18 |
|
fenor
Messages: 4
Registered: May 2014
|
Junior Member |
|
|
I wrote this to make the triggers working and it looks like it would work but I have not used this kind of trigger before so if somebody has an advice or something I would be appreciated.
CREATE GLOBAL TEMPORARY TABLE MINTACSOPORTSTATUS_TRHELP
(
mintaid NUMBER(12,0) NOT NULL,
csoport NUMBER(3,0)
) ON COMMIT DELETE ROWS;
CREATE OR REPLACE TRIGGER mintavizsgalat_compound_tr
FOR INSERT OR UPDATE OR DELETE ON minta_vizsgalat
COMPOUND TRIGGER
AFTER EACH ROW IS
lcount NUMBER;
lmintaid NUMBER(12,0);
lcsoport NUMBER(12,0);
BEGIN
IF DELETING THEN
lmintaid := :old.mintaid;
lcsoport := :old.csoport;
ELSE
lmintaid := :new.mintaid;
lcsoport := :new.csoport;
END IF;
INSERT INTO MINTACSOPORTSTATUS_TRHELP VALUES (lmintaid, lcsoport);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN (SELECT DISTINCT * FROM MINTACSOPORTSTATUS_TRHELP WHERE csoport IS NOT NULL) LOOP
setMintaCsoportStatus(i.mintaid, i.csoport);
END LOOP;
END AFTER STATEMENT;
END mintavizsgalat_compound_tr;
/
CREATE OR REPLACE TRIGGER minta_compound_tr
FOR INSERT OR UPDATE OR DELETE ON minta
COMPOUND TRIGGER
AFTER EACH ROW IS
lcount NUMBER;
lmintaid NUMBER(12,0);
BEGIN
IF DELETING THEN
lmintaid := :old.mintaid;
ELSE
lmintaid := :new.mintaid;
END IF;
INSERT INTO MINTACSOPORTSTATUS_TRHELP VALUES (lmintaid, null);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR i IN (SELECT DISTINCT * FROM MINTACSOPORTSTATUS_TRHELP WHERE csoport IS NULL) LOOP
setMintaStatus(i.mintaid);
END LOOP;
END AFTER STATEMENT;
END minta_compound_tr;
/
|
|
|
|
| Re: slow sql [message #613507 is a reply to message #613505] |
Wed, 07 May 2014 10:38 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>OER 7451 in Load Indicator : Error Code = OSD-04500: illegal option specified
>O/S-Error: (OS 1) Incorrect function. !
>I use a 64bit window and XE.
it appears that you installed 32-bit Oracle on 64-bit OS & we can't fix this situation.
Only you can install & run complaint software on your system.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 22:19:02 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
