Home » RDBMS Server » Performance Tuning » query tuning with NVL
query tuning with NVL [message #134384] Wed, 24 August 2005 11:59 Go to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

Can somebody help me rewrite this query.especially the one with the NVL part as i wont be able to index them.


SELECT        * 
FROM          hsd_accounts_payable 
WHERE        file_type = NVL (:b6, file_type) 
AND 
( 
(select_for_payment = 'S' AND ap_status IN ('F', 'R') 
) 
OR 
(select_for_payment = 'U' AND ap_status = 'H') 
) 
AND 
ap_type = :b5 AND 
company_code = :b4 
AND NVL(schedule_id, ROWID) = 
DECODE(:b3,NULL, NVL(schedule_id, ROWID),:b3) 
AND 
NVL(vendor_group_id, ROWID) = DECODE(:b2, 
                                         NULL, NVL(vendor_group_id, ROWID), 
                                                    :b2) 
AND SubStr(NVL(payment_method,'B'),1,1) = Decode(:b1,'Y','R','B') 
ORDER BY   seq_vend_id, seq_vend_address,file_type, seq_claim_id 
FOR UPDATE 

plan is like 

| Id  | Operation                       |  Name                     | Rows  | Bytes | Cost  | 
--------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                |                           |     2 |   414 |   133K| 
|   1 |  FOR UPDATE                     |                           |       |       |       | 
|   2 |   SORT ORDER BY                 |                           |     2 |   414 |   133K| 
|   3 |    CONCATENATION                |                           |       |       |       | 
|   4 |     FILTER                      |                           |       |       |       | 
|   5 |      TABLE ACCESS BY INDEX ROWID| ACCOUNTS_PAYABLE          |     1 |   207 | 66965 | 
|   6 |       INDEX RANGE SCAN          | ACC_PAY#CC#APTY#SFP#APST  |  1485K|       |  5812 | 
|   7 |     FILTER                      |                           |       |       |       | 
|   8 |      TABLE ACCESS BY INDEX ROWID| ACCOUNTS_PAYABLE          |     1 |   207 | 66965 | 
|   9 |       INDEX RANGE SCAN          | ACC_PAY#CC#APTY#SFP#APST  |  1485K|       |  5812 | 



regards
Hrishy
Re: query tuning with NVL [message #135558 is a reply to message #134384] Thu, 01 September 2005 03:59 Go to previous message
Frank Naude
Messages: 4571
Registered: April 1998
Senior Member
You can always use function based indexes. Example:

SQL> CREATE TABLE x (c1 NUMBER);

Table created.

SQL>
SQL> INSERT INTO x VALUES (NULL);

1 row created.

SQL>
SQL> INSERT INTO x VALUES (1);

1 row created.

SQL>
SQL> CREATE INDEX x_idx ON x( nvl(c1, 999) );

Index created.

SQL>
SQL> EXPLAIN PLAN FOR SELECT /*+INDEX(x)*/ * FROM x where nvl(c1, 999) = 999;

Explained.

SQL>
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    13 |     2  (50)|
|   1 |  TABLE ACCESS BY INDEX ROWID| X           |     1 |    13 |     2  (50)|
|*  2 |   INDEX RANGE SCAN          | X_IDX       |     1 |       |     1   (0)|
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   2 - access(NVL("X"."C1",999)=999)

13 rows selected.


Best regards.

Frank
Previous Topic: performance tuning
Next Topic: To increase performance Delete statement
Goto Forum:
  


Current Time: Tue Dec 06 22:07:51 CST 2022