Database Hint (%_HINT) to force a full table scan

[Former Member]

Hi All,

I need to implement a database hint so that the SELECT statement ALWAYS does a full table scan. That is, forcing a full table scan and not using any indexes.

Does anybody know how to do that and can provide an example here?

Thanks,

Johannes

[former_member192616]

Hi Johannes,

Hi All,
> 
> I need to implement a database hint so that the SELECT statement ALWAYS does a full table scan. That is, forcing a full table scan and not using any indexes.
> Does anybody know how to do that and can provide an example here?
> 
> Thanks,
> 
> Johannes

since hints are database dependent we need to know the database plattform you are running on.

on ORACLE it would be:

%_hints ORACLE 'FULL("TABNAME")'

other databases have other hints...

Kind regards,

Hermann

[Former Member]

The database is MSSQLNT..

Does anybody know the syntax to force the full table scan for this database?

Thanks, Johannes

[former_member192616]

Hi Johannes,

as far as i know there is no real/true full table scan (at least not name wise in the execution plan).

You can find all hints for MSSQL in SAP note: 133381.

A "full table scan" on MSSQL would be an index scan on the primary index (which is the table) with no SEEK predicates (only WHERE predicates or none at all). So it is all about to enforce primary index usage... .

In this scenario all possible i/O optimizations (multiblock i/O and prefetch) should be used and i think this is the main reason why you ask for a full table scan, right?

Kind regards,

Hermann

P.S.: for client dependent tables you might see an index seek with a seek predicate on the client instead of an index scan.

Edited by: Hermann Gahm on May 4, 2011 11:08 AM

[yuri_ziryukin]

Hello Johannes,

please refer to the SAP note 133381.

I have quickly checked with MS SQL colleague.

Full table scan in MS SQL server is a clustered index scan with index 0. Index 0 is a table itself.

So please use information from note 133381 to force the usage of index 0.

I hope it helps.

Regards,

Yuri

P.S. Hermann is faster

Edited by: Yuri Ziryukin on May 4, 2011 10:11 AM