OrdKey CDX-NTX

[ask]

Try this

Code: Select all  Expand view  RUN

#include "fivewin.ch"

request DBFcdx

FUNCTION MAIN()


SET DELETED On

DBCREATE( "TMPTEST", { { "TEST", "C", 10, 0 } } )

USE TMPTEST via "DBFcdx"

INDEX ON FIELD -> test TO TMPTEST

APPEND BLANK

REPLACE FIELD -> test WITH "Test 1"

APPEND BLANK

REPLACE FIELD -> test WITH "Test 2"

DELETE

APPEND BLANK

REPLACE FIELD -> test WITH "Test 2"

DELETE

APPEND BLANK

REPLACE FIELD -> test WITH "Test 3"

GO TOP

do while !eof()

? RECNO(), ORDKEYNO()

SKIP

enddo

CLOSE


RETURN NIL



The result is 1 1 and 4 2 which is the right one because why delete 2 records

Now try with ntx (remove REQUEST DBFcdx , remove via "DBFcdx")

The result is 1 1 and 4 4 which is the wrong . Using dbfntx the ordkeyno counts deleted records too.

Can someone please give me a clue how i can use dbfntx and ordkeyno to work ?

regards,

A.S.K

[nageswaragunupudi]

Please create the table, index and close.
Reopen and then do the test. Both RDDs count deleted records.

[ask]

Yes but WHY? If i use set deleted on WHY does it counts the records? This is a BIG problem because i cannot move within my indexed records. Any solution?

Thank you
A.S.K

[nageswaragunupudi]

Create index with for !deleted() condition.

[ask]

Thank you NageswaraRao

regards,
A.S.K

[nageswaragunupudi]

There is another way if you are using DBFCDX only.

Create the index on normal way. But when you open the table, set filter to !deleted(). OrdKeyCount() and OrdKeyNo() respect filters and scopes. So you get only 2 keys.

On large tables if you find the filter to make the program slow, you can always create one more index tag 'INDEX ON DELETED() TAG DELETED'

Also it is desirable not to create indexes with for clause, but create separate tag for deleted(), because filter optimizations use only indexes that do not have for clauses.

With DBFNTX only way is the create every index you need with FOR !deleted() clause.

[ask]

On large tables if you find the filter to make the program slow, you can always create one more index tag 'INDEX ON DELETED() TAG DELETED'

Can you explain it because i don't understand it

Thank you
A.S.K

[nageswaragunupudi]

Mr ask

In the olden days of ndx, ntx and initial cdx, filters used to crawl. We used to avoid them like plague. After foxpro introduced Rushmore technology for optimization of filters, Comix and SIX provided similar optimisations for Clipper. Later on Clipper's DBFCDX used comix for optiimization of filters. Now, except DBFNTX all RDDs provide optimized filters. But there is one condition for the RDD to be able to optimize the filters. We need to provide indexes on all the columns we use in the filter expressions. If not, the RDD optimizes the filter either patially or not at all depending on the availability of filters. When we SET DELETED ON, the RDD's require index on the expression 'DELETED()' also to optimize. Then, if we set filter like 'FLD1 = 'SOMETHING' .AND. FLD2 = 'SOMETHING' .AND. !DELETED()' the filter will be fully optimized. Some RDDs may not need us to specify !DELETED() in the filter clause, but they do need an index. I have not used comix and six for ages. You may check their documentation. But explicitly using !DELETED() in the filter clause and having an index on DELETED() will help every RDD ( other than NTX) to optimize the filters fully.

[ask]

Thank you for your answer.I did not know all this things .As i can understand being you seem to know allot about databases and indexes . Can you please tell me which of the 2 RDDs (DBFntx , DBFcdx) is faster and more reliable ?

Thank you very much
A.S.K

[James Bott]

NageswaraRao,

Thanks for that detailed explanation. It was very helpful. I'm sure a lot of us did not know all that.

Regards,
James

[nageswaragunupudi]

Thank you for your answer.I did not know all this things .As i can understand being you seem to know allot about databases and indexes . Can you please tell me which of the 2 RDDs (DBFntx , DBFcdx) is faster and more reliable ?

Thank you very much
A.S.K

Between the two, DBFCDX.
I read in some posts that now there is a new RDD which is even better. I havent tried.
I am using ADS.