4 Replies Latest reply: May 4, 2011 10:11 AM by Yuri Ziryukin RSS

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

Johannes Schnatz
Currently Being Moderated

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

  • Re: Database Hint (%_HINT) to force a full table scan
    Hermann Gahm
    Currently Being Moderated

    Hi Johannes,

     

    Johannes Schnatz wrote:

     

    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

    • Re: Database Hint (%_HINT) to force a full table scan
      Johannes Schnatz
      Currently Being Moderated

      The database is MSSQLNT..

       

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

       

      Thanks, Johannes

      • Re: Database Hint (%_HINT) to force a full table scan
        Hermann Gahm
        Currently Being Moderated

        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

      • Re: Database Hint (%_HINT) to force a full table scan
        Yuri Ziryukin
        Currently Being Moderated

        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

Actions