Help - Simple SQL query doing full table scans

Help - Simple SQL query doing full table scans

Post by Sunil Baksh » Wed, 09 Jan 2002 10:15:12



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

 
 
 

Help - Simple SQL query doing full table scans

Post by santysharm » Wed, 09 Jan 2002 10:45:32


well, you are selecting all rows and all columns  from table and hence table
scan has to used.
You can improve upon order by clause by increasing sort area size in your
session.
hth
santysharma

Quote:

> 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


 
 
 

Help - Simple SQL query doing full table scans

Post by Stefa » Wed, 09 Jan 2002 15:33:44


Hi,

santysharma is right. You are reading all the columns so full table scan
is necessary. Want you want to achieve is not doing the sort, but I doubt
that with only one table, that is that small this is worth while.

Having said that, if you want to use the index hint, you should use the
right syntax. Don't use any comma's in the hint description.

Regards
Stefan


> well, you are selecting all rows and all columns  from table and hence
> table scan has to used.
> You can improve upon order by clause by increasing sort area size in your
> session.
> hth
> santysharma


>> 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

 
 
 

Help - Simple SQL query doing full table scans

Post by Herman de Bo » Wed, 09 Jan 2002 16:55:26


Hello Sunil,

it might be the case that the columns CLIENT, SSN, H_COVTP have been
declared as optional (nullable). If so, the index cannot be used as
starting point.

Kind Regards,

Herman de Boer

 
 
 

Help - Simple SQL query doing full table scans

Post by Howard J. Roger » Thu, 10 Jan 2002 04:31:56


You're doing a 'select *', so that returns all columns, which aren't in your
index.  Hence we are going to have to visit the table in any case.  You then
don't have a 'where' clause, so we are going to be returning 100% of the
rows in the table.  Indexes are only used when less than about 5% of the
rows are going to be returned (unless, as I think you are hinting at, but
which doesn't apply in your case, the entire query can be resolved within
the index itself).

You're then using the incorrect syntax for the index hint.

But the real point is that forcing the thing to use the index is not going
to help you very much (because if it did, the optimizer would have chosen to
use it in the first place!).

If it is the 'order by' that is causing it to be too slow, make sure that
the ordering can take place in memory by having a decent-sized
sort_area_size parameter for your session.

Oh -and in future, always make sure you include the platform and Oracle
version in your posts.  Questions related to the optimizer are nearly always
very version-sensitive.

Regards
HJR


Quote:

> 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

 
 
 

Help - Simple SQL query doing full table scans

Post by Keith Boulto » Fri, 11 Jan 2002 17:25:45


Quote:> You're then using the incorrect syntax for the index hint.

> But the real point is that forcing the thing to use the index is not going
> to help you very much (because if it did, the optimizer would have chosen
to
> use it in the first place!).

When was the optimiser made so good!

Just because the query would return all rows doesn't mean that is what is
going to be fetched.

It is quite common (in my experience) to run queries to populate a list of
rows, one of which is to be selected. The users generally look at only the
first few pages so it is very advantageous to use the index to do the  sort.

 
 
 

Help - Simple SQL query doing full table scans

Post by Howard J. Roger » Fri, 11 Jan 2002 20:14:34


I hold no candles for the optimizer, but it really isn't as bad as people
make out, and gets better and subtler every release.

The idea of returning a fistfull of records, when Users are only interested
in the first x-dozen of them, sounds like distinctly dodgy application
design to me.  In any case, if you want the majority of the rows returned in
a sorted order, that's what an order by clause is for.  It's a misuse of an
index otherwise, involving excess I/O that's totally not required.

Regards
HJR
--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


Quote:> > You're then using the incorrect syntax for the index hint.

> > But the real point is that forcing the thing to use the index is not
going
> > to help you very much (because if it did, the optimizer would have
chosen
> to
> > use it in the first place!).

> When was the optimiser made so good!

> Just because the query would return all rows doesn't mean that is what is
> going to be fetched.

> It is quite common (in my experience) to run queries to populate a list of
> rows, one of which is to be selected. The users generally look at only the
> first few pages so it is very advantageous to use the index to do the
sort.

 
 
 

Help - Simple SQL query doing full table scans

Post by Keith Boulto » Sat, 12 Jan 2002 06:47:42


Quote:> The idea of returning a fistfull of records, when Users are only
interested
> in the first x-dozen of them, sounds like distinctly dodgy application
> design to me.  In any case, if you want the majority of the rows returned
in
> a sorted order, that's what an order by clause is for.  It's a misuse of
an
> index otherwise, involving excess I/O that's totally not required.

It doesn't matter if the I/O is excess as long as it doesn't cause a
problem.

The point about using an index to fetch all the required records is that the
work is done as the records are fetched ie a few milliseconds delay every
time the page down key is pressed, compared with a wait of (potentially)
seconds before even the first record is shown if the data is sorted  - all
records are fetched before the sort can occur. A total execution time of 10
seconds where there are 100 0.1 second pauses as the user pages down is
better than a total execution time of 3 seconds where there is a 3 second
delay before any data is shown.

The point is that using an index to do a sort means you get the first page
of data back immediately e.g. show me all outstanding work requests in order
of date requested to select items to work on. The odds are that the user
won't actually fetch all the available records. Fetching them to sort is
"excess I/O that's totally not required."

 
 
 

1. help figuring out why query doing full table scan

Running Oracle 8.0.5 on Solaris 2.6

We have the following statement within a stored procedure that has all
the sudden
decided to do a full table scan on the user table.  I don't understand
why it all the sudden started doing this, as the statistics are up to
date.  The USER table has tens of millions of rows, so this just isn't
going to work.  Any ideas???????


USER,SUMMARY.SUMMARY_EVENT SE  WHERE USER.USAGE_USED_BE_ID =
SE._EVENT_ID  AND SE.CUSTOMER_SUMMARY_ID = :b1

With the following explain plan output.....

SELECT STATEMENT Optimizer=CHOOSE (Cost=84353 Card=1 Bytes=2991832)
SORT (GROUP BY)
HASH JOIN (Cost=84353 Card=20492 Bytes=2991832)
TABLE ACCESS (BY INDEX ROWID) OF SUMMARY_BILLING_EVENT (Cost=1970
Card=6002 Bytes=168056)
INDEX (RANGE SCAN) OF SBE_CUSTOMER_SUMMARY_ID_IDX (NON-UNIQUE) (Cost=47
Card=6002)
PARTITION (CONCATENATED)
TABLE ACCESS (FULL) OF USER (Cost=73345 Card=32531311 Bytes=3838694698)

2. TEXT datatype: compared to CHAR and VARCHAR

3. Help with statement in proc suddenly doing full table scan

4. Alternative for Solid Database

5. Help trying to eliminate a full table scan and optimize query

6. Paradox * Runtime

7. sql statement using full table scan ... HELP

8. PostgreSQL Licence: GNU/GPL

9. Table scan, Table scan, Table scan

10. Index Fast Full Scan vs Index Full Scan?

11. Index scan vs Full table scan

12. Difference between a full scan, and a fast full scan for an INDEX

13. Query engine choosing full table scan over index seek