Indexes Based on Deleted Records

When records are marked for deletion in Visual FoxPro, they are not physically removed until the PACK command is called. Therefore, these records remain visible and accessible while performing basic table operations unless you set the SET DELETED command to ON, which hides these records from basic operations.

To help ensure that queries use Rushmore optimization, some developers create an index tag for deleted records by using the DELETED( ) function in the index expression. The DELETED( ) function determines whether a record is marked for deletion. For example, the following code creates an index named Deleted using an index expression that includes the DELETED( ) function that selects records marked for deletion:

INDEX ON DELETED() TAG Deleted

However, performance issues can still exist with this method, usually when working with large data sets in a LAN/WAN networking environment where deleted records are scattered in a table and when transmitting a large index over the network.

Instead, you can create and use a binary index, which optimizes the bit that marks records for deletion, to improve performance when you set the SET DELETED command to ON. You can create a binary index by choosing the Binary index type in the Table Designer or by including the BINARY keyword in the INDEX command. For example, the following code creates a binary index named myDeleted that selects records marked for deletion:

INDEX ON DELETED() TAG DeletedTag BINARY

For more information, see Visual FoxPro Index Types, DELETED( ) Function, and SET DELETED Command.

Rushmore Optimization for Indexes Based on Deleted Records

For indexes based on deleted records, Visual FoxPro includes the following Rushmore optimization enhancements in the SQL engine:

• INDEX ON NOT(DELETED()) optimizes NOT(DELETED()) and DELETED() query conditions. In versions prior to Visual FoxPro 9.0, only DELETED() was optimized.

  For example, the following queries are now optimized:

  CLEAR
  CLOSE DATABASE ALL
  SYS(3054,1)
  CREATE TABLE myTable (f1 I)
  INDEX ON NOT(DELETED()) TAG NotDel
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
  SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor
  
  SET DELETED ON
  SELECT * FROM myTable INTO CURSOR tempCursor
  INDEX ON DELETED() TAG Del
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
  SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor
  
  SET DELETED ON
  SELECT * FROM myTable INTO CURSOR tempCursor
  CLOSE DATABASE ALL
  

• INDEX ON ... FOR DELETED( ) or INDEX ON ... FOR NOT(DELETED()) optimizes DELETED() or NOT(DELETED()) query conditions respectively when INDEX ON DELETED() or INDEX ON NOT(DELETED()) is not present.

  For example, the following queries are now optimized:

  CLEAR
  CLOSE DATABASES ALL
  SYS(3054,1)
  CREATE TABLE myTable (f1 I)
  INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED())
  INDEX ON f1 TAG f1_Del FOR DELETED()
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
  SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor
  
  SET DELETED ON
  SELECT * FROM myTable INTO CURSOR tempCursor
  INDEX ON DELETED() TAG DeletedRec
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE NOT(DELETED()) INTO CURSOR tempCursor
  SELECT * FROM myTable WHERE DELETED() INTO CURSOR tempCursor
  
  SET DELETED ON
  SELECT * FROM myTable INTO CURSOR tempCursor
  CLOSE DATABASES ALL
  

• When Visual FoxPro can determine that a query should not return deleted or undeleted records, and if no unfiltered index is present, it uses INDEX ON <expression> ... FOR DELETED() or INDEX ON <expression> FOR NOT(DELETED()).

  For example, the following queries are now optimized:

  CLEAR
  CLOSE DATABASE ALL
  SYS(3054,1)
  CREATE TABLE myTable (f1 I,f2 I)
  INDEX ON f1 TAG f1_NotDel FOR NOT(DELETED())
  INDEX ON f1 TAG f1_Del FOR DELETED()
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE ;
    (NOT(DELETED()) AND f1>3) OR ;    && Tag f1_NotDel used for optimization.
    (DELETED() AND f1<3) ;       && Tag f1_Del used for optimization.
    INTO CURSOR tempCursor
  SELECT * FROM myTable WHERE ;
    (f1>3 AND NOT(DELETED())) OR ;   && Not optimized. f1>3 comes first.
    (f1<3 AND DELETED()) ;      && Not optimized. f1<3 comes first.
    INTO CURSOR tempCursor
  
  SET DELETED ON
  SELECT * FROM myTable WHERE ;
    (f1>3000) OR ;       && Tag f1_NotDel used for optimization.
    (f1<1000) ;         && Tag f1_NotDel used for optimization.
    INTO CURSOR tempCursor
  INDEX ON f1 TAG f1
  
  SET DELETED OFF
  SELECT * FROM myTable WHERE ;
    (NOT(DELETED()) AND f1>3) OR ;   && Tag f1 used for optimization.
    (DELETED() AND f1<3) ;    && Tag f1 used for optimization.
    INTO CURSOR tempCursor
  
  SET DELETED ON 
  SELECT * FROM myTable WHERE ;
    (f1>3000) OR ;       && Tag f1 used for optimization.
    (f1<1000) ;      && Tag f1 used for optimization.
    INTO CURSOR tempCursor
  CLOSE DATABASE ALL
  

• If only indexes with NOT(DELETED()) filter expressions are used for Rushmore optimization and SET DELETED is set to ON, then additional optimization using NOT(DELETED()) is not performed as it is unnecessary.

SQL Optimizations for MIN( ) and MAX( ) Aggregate Functions

When appropriate, Visual FoxPro uses FOR DELETED() and FOR NOT(DELETED()) filter expressions to optimize MIN( ) and MAX( ) aggregate functions. Visual FoxPro uses filtered indexes based on DELETED() index expressions when available.

Filtered indexes that use FOR NOT(DELETED()) can provide additional optimization with SQL queries with any SET DELETED setting. However, a filtered index tag with FOR DELETED() provides benefits only when SET DELETED is set to OFF.

Note

These optimizations are not Rushmore-based; therefore, they do not appear in the results returned by SYS(3054) - Rushmore Query Optimization Level.

CLEAR
SET SAFETY OFF
CLOSE DATABASE ALL

CREATE TABLE myTable (f1 I)
INDEX ON f1 TAG f1

SET DELETED OFF
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1 is used to optimize MAX(f1)

SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1
* MAX(f1) is not optimized and slow

SET DELETED ON
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* MAX(f1) is not optimized and slow

SELECT myTable
INDEX ON f1 TAG f1Del FOR DELETED()
INDEX ON f1 TAG f1NotDel FOR NOT(DELETED())

SET DELETED OFF
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1 is used to optimize MAX(f1)

SELECT MAX(f1) FROM myTable WHERE DELETED()INTO CURSOR temp1
* tag f1Del is used by  MAX(f1)

SET DELETED ON
SELECT MAX(f1) FROM myTable INTO CURSOR temp1
* tag f1NotDel is used to optimize MAX(f1)

CLOSE DATABASE ALL

See Also

Tasks

How to: Create Indexes (Visual FoxPro)
How to: Create Less Frequently Used Indexes
How to: Filter Data

Other Resources

Working with Table Indexes