Home » SQL & PL/SQL » SQL & PL/SQL » Correct script using indexes
Correct script using indexes [message #264933] Wed, 05 September 2007 01:18 Go to next message
ruzibizam
Messages: 1
Registered: September 2007
Junior Member
Hi! Please help if anybody know the solution.

My oracle 9i database has a performance problem using indexes.
Please is anyone who can correct this script using indexes?

  • Attachment: Script.doc
    (Size: 36.50KB, Downloaded 381 times)
Re: Correct script using indexes [message #264934 is a reply to message #264933] Wed, 05 September 2007 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't download doc files, just txt ones.

Please read and follow OraFAQ Forum Guide.
Please always post your Oracle version (4 decimals).

If it is a performances problem, post it in the appropriate forum and first read the sticky on top of this performances forum.

Regards
Michel
Re: Correct script using indexes [message #265195 is a reply to message #264933] Wed, 05 September 2007 13:37 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi, as I see it - your script (is it a FORM ?) has a number of problems:

1. Cursor:

    CURSOR C_EMP IS
    SELECT /*+ INDEX(COMPTE_COTISANT CPTCOT_NUM_COTI_I)*/ DISTINCT NUM_COTI,TRI_REFE FROM COMPTE_COTISANT 
    WHERE NUM_COTI <= NVL(:NUM_COTI_FIN,NUM_COTI) AND NUM_COTI >= NVL(:NUM_COTI_DEB,NUM_COTI) AND COD_OPE_COTI NOT IN (21,23)
    AND MATR IS NOT NULL
    ORDER BY NUM_COTI, TRI_REFE;

is performing FULL table scan of COMPTE_COTISANT table, because you have following condition:
NUM_COTI <= NVL(:NUM_COTI_FIN,NUM_COTI) AND NUM_COTI >= NVL(:NUM_COTI_DEB,NUM_COTI)

NUM_COTI is used on both sides of logical operator, so no index will help you.

What are the chances of passing NULL as a value for :NUM_COTI_DEB and :NUM_COTI_FIN ?
If your application passing NULLS and values then split the statement in 2 ( 1 with values and the second without any condition on NUM_COTI ). At least in first case you will be able to use indexes.

2. The statement
SELECT /*+ INDEX(COMPTE_COTISANT CPTCOT_NUM_COTI_I)*/ COUNT(DISTINCT NUM_COTI||TO_CHAR(TRI_REFE,'Q/YYYY')) INTO NBR_EMP
    FROM COMPTE_COTISANT 
    WHERE MATR IS NOT NULL  AND COD_OPE_COTI NOT IN (21,23)
    AND NUM_COTI <= NVL(:NUM_COTI_FIN,NUM_COTI) AND NUM_COTI >= NVL(:NUM_COTI_DEB,NUM_COTI);


Performs another FULL table scan for the same reason.

3. The statement
SELECT /*+ INDEX_JOIN(COMPTE_COTISANT )*/ MIN(DAT_VALE) INTO MIN_DAT_VALE
	FROM COMPTE_COTISANT
	WHERE NUM_COTI = V_EMP.NUM_COTI AND TRI_REFE=V_EMP.TRI_REFE AND COD_OPE_COTI NOT IN (21,23);


is used to select rows that are already selected inside the cursor, so I recommend rewriting the cursor to avoid duplicate access to the same data:

CURSOR C_EMP IS
    SELECT NUM_COTI,TRI_REFE,MIN(DAT_VALE) DAT_VALE FROM COMPTE_COTISANT 
    WHERE NUM_COTI <= NVL(:NUM_COTI_FIN,NUM_COTI) AND NUM_COTI >= NVL(:NUM_COTI_DEB,NUM_COTI) AND COD_OPE_COTI NOT IN (21,23)
    AND MATR IS NOT NULL
GROUP BY NUM_COTI, TRI_REFE
    ORDER BY NUM_COTI, TRI_REFE;


4. The statement
SELECT /*+ INDEX(EMPLOYEUR EMP_PK) */COD_POS_EMPL, DAT_POSI INTO V_COD_POS, V_DAT_POSI
FROM EMPLOYEUR
WHERE MATR = V_EMP.NUM_COTI;
may be replaced by JOIN inside the cursor:
CURSOR C_EMP IS
  SELECT T.NUM_COTI, T.TRI_REFE, T.DAT_VALE, E.COD_POS_EMPL, E.DAT_POSI 
  FROM 
    ( SELECT NUM_COTI,TRI_REFE,MIN(DAT_VALE) DAT_VALE FROM COMPTE_COTISANT JOIN 
      WHERE NUM_COTI <= NVL(:NUM_COTI_FIN,NUM_COTI) AND NUM_COTI >= NVL(:NUM_COTI_DEB,NUM_COTI) AND COD_OPE_COTI NOT IN (21,23)
      AND MATR IS NOT NULL
      GROUP BY NUM_COTI, TRI_REFE ) T LEFT JOIN EMPLOYEUR E ON E.MATR = T.NUM_COTI 
       ORDER BY NUM_COTI, TRI_REFE;


5. The statement
SELECT VAL_NUME + 1 INTO V_NUM_OPER
	    FROM NUMERO_AUTOMATIQUE
	    WHERE COD_NUME = 3
	    FOR UPDATE OF VAL_NUME;

is locking the selected row, so you have severe concurrency problem ( if a number of users running the script - they have to wait one on the oother).

Why don't you use sequence instead?

Michael









Previous Topic: DBMS_SCHEDULER job does not run
Next Topic: Please help in refining/modifying the sql.
Goto Forum:
  


Current Time: Mon Dec 05 15:16:10 CST 2016

Total time taken to generate the page: 0.12313 seconds