RE: performance issue for a specific column selection - another tuneing issue

Yes Amit.
 
Your question fixed the problem.
 
There is a bitmap index on the column. I just tried dropping that. 
 
Thats excellent. It worked for me. Thanks for your time.
 
However, I have one more issue with the below query. Can you help me on this:
 
SELECT   to_char(hName.ADT_LOADED_DATE,'yyyy-mm-dd hh24:mi:ss') "col1" ,
         to_char(hName.IMAGE_RECEIVED_DATE,'yyyy-mm-dd hh24:mi:ss'),
         
floor(hName.CODED_LOADED_DATE-GREATEST(hName.ADT_LOADED_DATE,hName.IMAGE_RECEIVED_DATE))
 "col2" ,
         hName.PATIENT_CASE_TYPE "case   " ,
         hName.drg_1,
         hName.CODED_LOADED_DATE,
         hName.ACCT# 
      FROM HOSP_AUXILIO hName 
          WHERE hName.PATIENT_CASE_TYPE
              in (select PST.PATIENT_CASE_TYPE 
                  from PATIENT_CASE_TYPE PST,HIM_PATIENT_CASE_TYPE HPST 
                  where PST.HOSPITAL_ID='AUXILIO' 
                 AND HPST.HIMCODE=PST.HIMCODE )
                                  and trunc(hName.CODED_LOADED_DATE) 
                 between to_date ('09/01/2005', 'mm/dd/yyyy') 
                 AND to_date('09/02/2005', 'mm/dd/yyyy') 
                 and hName.coded_flag='Y' 
                 and hName.adt_flag='Y'  
                 order by hName.ACCT#;
 
THe above query taking 9+ seconds to get the data.
 
The explain plan out put i like this:
 
SELECT STATEMENT
  SORT ORDER BY
    TABLE ACCESS BY INDEX ROWID HOSPITAL
      NESTED LOOPS
        VIEW  VW_NSO_1
          SORT UNIQUE
            HASH JOIN
              TABLE ACCESS FULL PATIENT_CASE_TYPE
              TABLE ACCESS FULL HIM_PATIENT_CASE_TYPE
        INDEX RANGE SCAN PAT_CASE_TYPE
 
 
But when i put the   rownum <2  (to get 1 row output) it is taking less than a 
second. But when i remove teh rownum condition it is retrieving 9+ seconds to 
retrieve total of 148 records.
 
Can you help me why it is taking that much of time.
 
( query with rownum is:
 
         to_char(hName.IMAGE_RECEIVED_DATE,'yyyy-mm-dd hh24:mi:ss'),
         
floor(hName.CODED_LOADED_DATE-GREATEST(hName.ADT_LOADED_DATE,hName.IMAGE_RECEIVED_DATE))
 "col2" ,
         hName.PATIENT_CASE_TYPE "case   " ,
         hName.drg_1,
         hName.CODED_LOADED_DATE,
         hName.ACCT# 
      FROM HOSP_AUXILIO hName 
          WHERE hName.PATIENT_CASE_TYPE
              in (select PST.PATIENT_CASE_TYPE 
                  from PATIENT_CASE_TYPE PST,HIM_PATIENT_CASE_TYPE HPST 
                  where PST.HOSPITAL_ID='AUXILIO' 
                 AND HPST.HIMCODE=PST.HIMCODE 
                 and HPST.PATIENT_CASE_TYPE like '%') 
                 and trunc(hName.CODED_LOADED_DATE) 
                 between to_date ('09/01/2005', 'mm/dd/yyyy') 
                 AND to_date('09/02/2005', 'mm/dd/yyyy') 
                 and hName.coded_flag='Y' 
                 and hName.adt_flag='Y'  and rownum <2
                 order by hName.ACCT#;

Thanks,
RAj

amit.sharma@xxxxxxxxxxxxx wrote:
Is there an index on hospital_id column, how big is the table?
 

 

 

 

Regards

Amit

 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On 
Behalf Of raja rao
Sent: 28 September 2005 11:37
To: oracle list
Subject: performance issue for a specific column selection


Hi All,
 
Can someone help me why a simple select is behaving strage:
 
select hospital_id,acct# from HSTAB where rownum=1;

 
This is almost taking 20 seconds to get the data. Where as teh same sql
without hospital_id column is taking just less than a second.
 
select acct#,mr# ,admit_dt    from ...  
This is taking less than a second. The only problem is with the hospital_id 
column.
 
When I checked the wait events, the below are the waits:
 

  SID EVENT                          MODULE
----- ------------------------------ -----------------------------------
USERNAME           P1         P2         P3
---------- ---------- ---------- ----------
   73 direct path write              SQL*Plus
OPS$ORACLE        201       6365          7
 
my db_files param is 200. So this file is temp file the wait is happening on.
 
I already have DISK_ASYNCH_IO=TRUE .
 
What else i have to do to fix this issue.
 
Thanks in advance,
Raj


---------------------------------
Yahoo! for Good
Click here to donate to the Hurricane Katrina relief effort. PLEASE READ: The 
information contained in this email is confidential and 
intended for the named recipient(s) only. If you are not an intended 
recipient of this email please notify the sender immediately and delete your 
copy from your system. You must not copy, distribute or take any further 
action in reliance on it. Email is not a secure method of communication and 
Nomura International plc ('NIplc') will not, to the extent permitted by law, 
accept responsibility or liability for (a) the accuracy or completeness of, 
or (b) the presence of any virus, worm or similar malicious or disabling 
code in, this message or any attachment(s) to it. If verification of this 
email is sought then please request a hard copy. Unless otherwise stated 
this email: (1) is not, and should not be treated or relied upon as, 
investment research; (2) contains views or opinions that are solely those of 
the author and do not necessarily represent those of NIplc; (3) is intended 
for informational purposes only and is not a recommendation, solicitation or 
offer to buy or sell securities or related financial instruments. NIplc 
does not provide investment services to private customers. Authorised and 
regulated by the Financial Services Authority. Registered in England 
no. 1550505 VAT No. 447 2492 35. Registered Office: 1 St Martin's-le-Grand, 
London, EC1A 4NP. A member of the Nomura group of companies. 

                
---------------------------------
Yahoo! for Good
 Click here to donate to the Hurricane Katrina relief effort. 

Other related posts:

  • » RE: performance issue for a specific column selection - another tuneing issue