Tuning SQL statements

[kwartz]

Can somebody please help me enhance this sql statement to run faster.




[code=oracle]CREATE VIEW tryView AS
(SELECT ser_id, (SELECT dy_id FROM dy WHERE ser.dsy_id=dy_r id) as new_dy_id FROM sequence ser
WHERE dsy_id IN (SELECT dy_rid FROM dy WHERE dy_id IN (SELECT dy_id FROM lookuptab GROUP BY dy_id HAVING count(*) = 1)))
UNION
(SELECT
ser_id,
substr(
concat(
concat((SELECT dy_id FROM dy WHERE ser.dsy_id=dy_r id), '.'),
(SELECT count(*) FROM lookuptab am
WHERE
isused='YES' and
am.dy_id=(SELEC T dy_id FROM dy WHERE dy_rid = ser.dsy_id)
)
),
1,
64
)
as new_dy_id
FROM series ser
WHERE dsy_id IN (SELECT dy_rid FROM study WHERE dy_id IN (SELECT dy_id FROM lookuptab GROUP BY dy_id HAVING count(*) > 1)));[/code]

[subashsavji]

Can somebody please help me enhance this sql statement to run faster.


Code: ( oracle8 )

try to use index on that columns which in beging used in the conditional clause
use proper index which suitable for your query.

[Dave44]

Can somebody please help me enhance this sql statement to run faster.


Code: ( oracle8 )

ok, first it would help us a lot if you could provide the create table statements for us, it makes it easier for us to help you.

second as a general rule of thumb you want to avoid using IN and NOT IN unless the lists are very small or specific strings which you hard code in and DONT CHANGE. unless the IN list is some static strings you usually can replace an IN with a JOIN and similarly a NOT IN with an ANTI-JOIN.
And you definately want to avoid nested IN statements like the plague. Do some reading on how oracle must process an IN list and you will understand why large IN lists are a performance killer.

so while i dont have the tables and thus cannot test this out the first part of your union query would become something like this:

SELECT ser_id,
       dy_id
FROM   sequence ser,
       dy,
       (SELECT dy_id 
        FROM   lookuptab 
        GROUP  BY dy_id 
        HAVING count(*) > 1) lut
WHERE  lut.dy_id = dy.dy_id
AND    ser.dsy_id = dy.dy_rid

other than what i have written i would really need table and index create statements to see what the query is doing.

there are so many factors involved in tuning there literally are whole volumes books written on the subject. things like size of tables, poorly written SQL, are indexes being used by the RBO or hints needed. lots of stuff.

[kwartz]

Here are the tables. Thanks

code (oracle 8)

DROP TABLE Lookuptab;
CREATE TABLE Lookuptab (
  DY_ID         VARCHAR2(64) NOT NULL,
  ACCN_NUM         VARCHAR2(16) NOT NULL,
  COMBO_DY_COUNT  VARCHAR2(4) DEFAULT 0 NOT NULL,
  ISUSED           VARCHAR2(3) DEFAULT 'NO' NOT NULL,
  UNIQUE (ACCN_NUM)
);

CREATE INDEX AM_ISUSED ON Lookuptab (ISUSED);
CREATE INDEX AM_DY_ID ON Lookuptab (DY_ID);
--CREATE INDEX AM_ACCN_NUM ON Lookuptab (ACCN_NUM);

COMMIT;





SQL> desc dy
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------
 DY_RID                                   NOT NULL NUMBER(9)
 SPAT_RID                                 NOT NULL NUMBER(9)
 SVIS_RID                                           NUMBER(9)
 SRP_RID                                            NUMBER(9)
 DY_ID                                    NOT NULL VARCHAR2(64)
 SDY_ID                                           VARCHAR2(16)
 STD_LAST_UPD_DATE                                  DATE
 STD_STATUS_ID                                      VARCHAR2(16)
 STD_LOCK                                           VARCHAR2(64)
 STD_LOCK_LOCATION                                  VARCHAR2(64)
 STD_MARKED_BY                                      VARCHAR2(64)
 STD_MARK_LOCATION                                  VARCHAR2(64)
 MOD_ID                                           VARCHAR2(64)
 STD_PRI_ID                                         VARCHAR2(16)
 SCH_STD_START_DATE                                 DATE
 SCH_STD_STOP_DATE                                  DATE
 SCH_STD_LOC                                        VARCHAR2(64)
 REASON_FOR_STD                                     VARCHAR2(64)
 STD_ARRIVAL_DATE                                   DATE
 STD_DATE                                           DATE
 STD_COMPLETION_DATE                                DATE
 STD_VERIF_DATE                                     DATE
 STD_ID_ISSUER                                      VARCHAR2(64)
 NUM_SERIES                                         VARCHAR2(12)
 NUM_ACQS                                           VARCHAR2(12)
 STD_READ_DATE                                      DATE
 STD_DESCRIPTION                                    VARCHAR2(64)
 STD_COMPO_STATUS_ID                                VARCHAR2(10)
 SCH_STD_LOC_AE_TITLES5                             VARCHAR2(85)
 STD_COMMENTS                                       VARCHAR2(4000)
 READING_PHYS5                                      VARCHAR2(325)
 PROC_CODE_SQ1                                      VARCHAR2(16)
 PERF_PHYS_NAMES                                    VARCHAR2(325)
 OTHER_STUDY_NUMS5                                  VARCHAR2(65)
 STD_STATUS                                         VARCHAR2(16)
 STD_EVER_COMPLETED                                 VARCHAR2(1)
 STD_TR_LOCK                                        VARCHAR2(64)
 STD_TR_LOCK_LOCATION                               VARCHAR2(64)
 STD_STAT                                           VARCHAR2(1)
 STD_STAGE                                          VARCHAR2(16)
 STD_REL_IMG_SEND                                   VARCHAR2(1)

[amitpatel66]

Try this query:

[code=oracle]
CEATE view tryview AS
(SELECT s.ser_id,
select dy_id from dy where dy_rid = d.dy_rid) as new_dy_id
FROM sequence ser, dy d
WHERE ser.dsy_id = d.dy_rid
AND d.dy_id IN (SELECT dy_id FROM lookuptab GROUP BY dy_id HAVING COUNT(*) = 1))
UNION
(SELECT s.ser_id,SUBSTR (d.dy_id||'.'|| (select count(*) from lookuptab WHERE isused='YES' AND dy_id = d.dy_id),1,64) AS new_dy_id
FROM series ser, dy d
WHERE ser.dsy_id = d.dy_rid
AND ser.dsy_id IN (SELECT dy_rid FROM study WHERE dy_id IN (SELECT dy_id FROM lookuptab GROUP BY dy_id HAVING COUNT(*) > 1)));
[/code]