Transcription
CHAPTER 5:SORTING & INDEXINGDB05-1
Need for Ordered Information„Data do not come in any order. It is not unusual tohave the records in a table arranged in arbitraryorder. Keeping data in this way has a number ofdrawbacks.„One obvious drawback is related to record(s)searching, a task often required in data processing.„For records in arbitrary order, there cannot be anyshorter way to search for the record(s) exceptexamine every record of the table sequentially, untilthe record is found or the end of the file is reached.DB05-2
Methods to Order Information„Visual FoxPro provides two different ways to arrangethe records in a table, sorting and indexing.„Sorting changes the physical record position in thetable. So it leads to the creation of another table.„Indexing creates an index file which consists of onlyenough information to determine the logical recordposition. The actual record position does notchange.DB05-3
Sorting„Sorting is used to change the physical order ofrecord in database tables. In Visual FoxPro, sortingcreates a totally new table in the order you specified.„Visual FoxPro can sort the table in ascending orderor descending order based on the field or acombination of fields.„Numeric fields are sorted in the natural order. Forcharacter fields, Visual FoxPro sorts according tothe ASCII codes.DB05-4
SortingSyntax„SORT TO TableName ON FieldName1 [/A /D][/C], ;FieldName2 [/A /D][/C][ASCENDING ;DESCENDING] FIELDS FieldNameList„A new table (TableName) is created.„/A- ascending, /D- descending, /C- case insensitve„USE student.dbfâ SORT TO nt1 ON name /D, dob /Aâ SORT TO nt2 ON name /D/C, dob /A FIELDS addrâ SORT TO nt3 ON name, telno /A, dobASCENDINGâ SORT TO nt4 ON name, dobDB05-5
SortingDisadvantages„Data redundancy occurs since new tables arecreated.„It consumes spaces in the hard disk.„SORT is not automatic, manual process involves.„Process is slow because disk access is usually slow.„It requires extra empty spaces for temporarystorage.„Only fields can be used in sorting. Expressions (e.g.wages*days) are not allowed.DB05-6
IndexingIntroduction„Visual FoxPro’s index is similar to the index at theback of a book. The purpose of a book index is tohelp readers to find information on a certain topicquickly. The index file contains the only information(pointers) enough to determine the logical order ofthe records in the file.„There are two types of index file in Visual FoxPro.They are single index file (independent index file)and compound index file (dependent index file orindependent index file), with extensions IDX and CDXrespectively.DB05-7
Single IndexCreation of Single Index„Independent index files (IDX) would not be updatedautomatically, if they are not activated.„INDEX ON eExpression TO IDXFileName„INDEX ON name TO name.idx„INDEX ON {01/01/2001}-dob TO ages.idx„INDEX ON days*wages TO income.idx„INDEX ON price*0.8 TO discount.idx„INDEX ON class classno TO order.idx„In these examples, files name.idx , ages.idx,income.idx, discount.idx, order.idx would be created.DB05-8
Types of Compound Index„A compound index file may consist of more than oneindex expression, called tags. Each index expressionhas a tag name.„There are two types of compound index file. They arestructural and non-structural.„Non-Structural compound index file does NOT hasthe same name as the table and is dissociated with it.When table is open, the CDX file will NOT also beopen automatically.„Structural compound index file has the same nameas the table and is associated with it. When table isopen, the CDX file will also be open automatically.DB05-9
Non-Structural Compound IndexCreation of Non-Structural Compound Index„INDEX ON eExpression TAG TagName ;OF CDXFileName [ASCENDING DESCENDING]„USE drivers.dbfâ INDEX ON name TAG name OF abc.cdxâ INDEX ON driverid TAG driverid OF abc.cdxâ INDEX ON dob TAG dob OF xyz.cdx DESCâ INDEX ON address TAG address OF xyz.cdx„It is necessary to specify the CDXFileName since it isa non-structural (independent) compound index.„In these examples, files abc.cdx, xyz.cdx would becreated.DB05-10
Structural Compound IndexCreation of Structural Compound Index„INDEX ON eExpression TAG TagName ;[ASCENDING DESCENDING]„USE drivers.dbfâ INDEX ON name TAG nameâ INDEX ON driverid TAG driveridâ INDEX ON YEAR(dob) TAG year DESCâ INDEX ON MONTH(dob) TAG month„It is not necessary to specify the CDXFileName sinceit is a structural (dependent) compound index.„In these examples, only file drivers.cdx would becreated.DB05-11
Structural Compound IndexCreation of Structural Compound Index (cont’d)„Compound index can also be created by anothermethod. Specify the eExpression and TagName inthe Table Designer.DB05-12
Structural Compound IndexCreation of Structural Compound Index (cont’d)„Expression Builder provides choices for you to buildexpression.DB05-13
Structural Compound IndexCreation of Structural Compound Index (cont’d)DB05-14
Structural Compound IndexTypes of Structural Compound Index„Primary, candidate, regular and unique are fourkinds of structural compound indexes.Primary Index„Primary index is used for the key field of a table.„Only unique values can be used. Thus, it is possibleto guard against users to enter duplicate values in afield where a primary index is defined.„Only one primary index can be defined for a table.„Primary index type is only available for databasetables and NOT available for free tables.DB05-15
Structural Compound IndexCandidate Index„Candidate index is similar to Primary index for it alsoenforces unique values.„However, there can be more than one Candidateindex for a table.Regular Index„Regular index does not enforce unique values.„It only determines the ordering of records.Unique Index„Unique index orders only a subset of records basedon the first occurrence of a value in the specifiedfield. It is seldom used in database application.DB05-16
Single IndexAccess of Single Index„Since single index file would not be activated unlessyou request to do so. Hence you need to open itmanually.„USE TableName INDEX IDXFileNameORUSE TableNameSET INDEX TO IDXFileName„USE drivers INDEX name.idx„USE drivers INDEX name.idx, driverid.idx, idno.idx„USE driversSET INDEX TO name.idx, driverid.idx, idno.idxDB05-17
Non-Structural Compound IndexAccess of Non-Structural Compound Index„The non-structural compound index would NOT beactivated when a table is open. Hence you need tospecify where is the index file.„USE TableName INDEX CDXFileNameSET ORDER TO TagName OF CDXFileName ;[ASCENDING DESCENDING]„USE drivers INDEX abc.cdxSET ORDER TO name OF abc.cdx DESCENDINGORUSE driversSET INDEX TO abc.cdxSET ORDER TO name OF abc.cdxDB05-18
Structural Compound IndexAccess of Structural Compound Index„The structural compound index would be activatedwhen a table is open. Hence you NO need to specifywhere to index file.„USE TableNameSET ORDER TO TagName ;[ASCENDING DESCENDING]ORUSE TableName ORDER TagName„USE driversSET ORDER TO nameORUSE drivers ORDER nameDB05-19
.RequirementSpeedSlower as it involves the F a s t e r a s i t i n v o l v e sduplication of the whole duplicating the minimumfile.amount of data to identifythe records.Convenience L o w a s s w i t c h i n g t o High as switching to anyanother order needs to sort order can be done very fast.the table again.Usefulness Low as a sorted table can High as an index file canonly speed up searching for contain many tags whichthe expression used for can speed up searchingsorting.based on those expressionused to build the tags.DB05-20
ComparisonSingle Index vs Non-Structural Compound Index„Both indexes are independent and would NOT beactivated automatically.„Non-structural compound index allows more thanone index tags.„Reindexing is necessary for single index.Structural Compound Index vs Non-StructuralCompound Index„Both indexes allow more than one index tags.„Structural compound index is dependent (associated)with the table and would be activated automaticallywhile non-structural compound index does not.DB05-21
Questions to Think About„Why SORT or INDEX?„Problems of SORT„Types of INDEX, properties of each INDEX„Methods to create and access each INDEXDB05-22
ENDDB05-23
DB05-3 Methods to Order Information —Visual FoxPro provides two different ways to arrange the records in a table, sorting and indexing. —Sorting changes the physical record position in the table. So it leads to the creation of another table. —Indexing creates an index file which consists of only enough information to determine the logical record
