this query takes less time, but it's getting executed 20000 times a day. could any one suggest me?
We have a table called RCR that contains 4.5 million records. One particular query on this table takes an average of 3 seconds to execute even though the table is indexed. The query is:
WHERE RCR_CUST_CODE = '1001038356'
AND RCR_ACCOUNT_CODE = '001'
AND RCR_MOBILE_NO = '62876013'
AND NVL(RCR_POSTED_IND,'N') <> 'Y'
AND NVL(RCR_ROAMING_IND,'L') <> 'R'
AND TRUNC(RCR_START_TIMESTAMP) <= TO_DATE('20020616','YYYYMMDD')
AND NVL(RCR_FREE_AIRTIME_IND, '1') <> '3'
AND RCR_SEQ_NO IS NULL
AND RCR_CALL_RATING_IND = 'NW';
There are three indexes on this table:
1. (RCR_INVOICE_NO, RCR_MOBILE_NO)
2. (RCR_CUST_CODE, RCR_ACCOUNT_NO, RCR_MOBILE_NO) - all three fields are VARCHAR2(x)
Even if we include only the first three conditions of the WHERE clause (till RCR_MOBILE_NO), then also it is taking the same time.
This table and its indexes are created every month (actually, different tables are created each time, but they have the same description; so for simplicity, you can consider it as the same table). After table creation, some process inserts 4.5 million records into this table over the period of a month. After the month is over, another process reads and updates this table. The above query is one of these reads.
One option we tried was dropping and recreating the index before reading this table. There was some minor reduction in the execution time of the above query.
What other options do we have for drastically tuning this query? We have already checked the row chaining (nil) and fragmentation. We checked disk I/O as well but did not have any major recommendation for this table. The disk array is RAID 1.
Or is this performance normal for these conditions?
The server is a 6 CPU (each CPU 700 MHz), 12 GB RAM Compaq Alpha GS140 box. The OS is Alpha OpenVMS 7.2-1. The Oracle server version is 220.127.116.11. The database size is around 220 GB.
Any suggestions will be appreciated.
Prashant & Bhargav
NWM project, Hong Kong
Some more info:
RCR TABLE ATTRIBUTES
FREELISTS 3 FREELIST GROUPS 1
INDEX 2 ATTRIBUTES
Could you please show the explain plan of this query (in SQL*Plus you see it by typing "SET AUTOTRACE ON").
How many results will be returned?
How many rows have in the average the same RCR_CUST_CODE, RCR_ACCOUNT_NO, RCR_MOBILE_NO values?
Have you tried it with an index organized table?
Is it correct that when you execute this type of query, the table does not any more get inserts/updates?
Click Here to Expand Forum to Full Width