Help - Simple SQL query doing full table scans

From: Sunil Bakshi <Sunil_Bakshi_at_premierintl.com>
Date: Mon, 7 Jan 2002 19:15:12 -0600
Message-ID: <u3ki18iarq1ncc@corp.supernews.com>

Hi All,

I have a table that I need to query which has around 64 fields and around 28,000 recs with a ORDERBY clause.
and it takes 12 secs. I am pasting the query below. I am also listing all the options I tried out. Please help if you have suggestions.

SELECT
    *
FROM
    SYBASE.HW
ORDER BY
    CLIENT, SSN, H_COVTP There is an index defined on CLIENT, SSN, H_COVTP called HW_IDX_01

I analyzed the table with ANALYSE TABLE HW COMPUTE STATISTICS;

I also changed the optimizer goal to FIRST_ROWS by using the statement ALTER TABLE SET OPTIMIZER_GOAL = FIRST_ROWS. I tried to hint the optimizer by specifying the following SELECT /*+ INDEX(HW, HW_IDX_01) */ * FROM SYBASE.HW ORDER BY CLIENT, SSN, H_COVTP I am sure that the ORDER BY is talking time, but I have to specify it and get it to work faster.

I tried to use the EXPLAIN PLAN on each of the modified form of the query and it still says TABLE ACCESS as FULL which is why I think I am not getting the speed, Please let me know if I can get it work faster.

Thank you very much for your help,
Sunil Received on Mon Jan 07 2002 - 19:15:12 CST