Main Area and Open Discussion > General Software Discussion

Reindexing in the background software for databases

<< < (3/6) > >>

CWuestefeld:
Shades, your description of Oracle doing index maintenance offline isn't quite right. Any real relational database (Oracle, SQL Server, etc.) requires that the database be entirely consistent at any point in time (see ACID). Thus, index maintenance is occurring in real time, as the data to which they refer is being updated.

This is crucial to some more advanced DBA optimization techniques. In particular, covering indexes allow a query to see data that would otherwise be locked by another process's locking operation, by taking the data out of the index rather than the real data tables.

Keeping any indexes up-to-date is indeed a resource drain on your system, but it's not really correct to say that indexes should be avoided as a result. Rather, you need to consider how your system is being used -

* When SELECTing data, what are the most common patterns (e.g., are you looking for "all the orders submitted yesterday", or "all the orders from this customer"?)
* When INSERTing new data, will it tend to be appended onto the end of the index structure, or must it be stuffed into the middle?
* Does the kind of multi-process access going on suggest that you'll need covering indexes?
* How much can you trade off additional storage (or faster more expensive storage devices) to improve overall system speed?...and so on

An increasingly common way to get around some of these problems is to have two distinct databases, once that's updated in realtime for the live transaction processing, and a second that gets updated periodically, and has different indexing structures (maybe even different table structures) to better facilitate reporting on the data.

Shades:
@ CWuestefeld:
If my posts indicated that one should not index, I'm sorry. I was trying to convey that you better let the database software itself take care of it, that continuous indexing is a bad idea and that compromises have to be made, no matter what database software you choose.

Since Oracle 10 automatic maintenance is done quite well in my experience. MS SQL Server is a bit more work to get indexing right. I have only played with the 2012 version and reading through the manuals, both off-and online, a lot of people (incl. Microsoft) mention that making and maintaining indexes within SQL Server can be tricky. There used to be a page that described how to design indexes, but for some reason it is inaccessible at the MSDN site. That was the page where I read about the do's and dont's with indexes on SQL Server (I think).

Contro:
Nice indeed.
so background reindexing is not necessary, but a way to talk.......

The limit in my databases is 2 Gb per file. I have almost 900.000 kb in the biggest one.

What i need is a free tool to import eventually my DBF to another platform easy for dummies like me.
Then i will index in the new platform . This is for me enough to do eventually one a week or a month.

What free desktop platform do you recommend me for this purpose ?

Best Regards


Note : I know postgreSQL (the forums are for professional people and are distribution lists i think)
I know mySQL. I would only for desktop purposes. It has forums in the way of donationcoder.
The database management better is have the appearance of my Visual dBASE from Borland or dBASE IV for DOS . These platforms don't go well in windows 7 64 bit.
The ultimate dBASE is not free. I would like a free tool.
When we had the databases imported we can index on all fields to have any character string under control.

Best Regards

Ath:
There are, obviously, 2 types of indexing discussed in this thread: database indexes, and application independent textsearch indexes. The OP is imho meant to point at the latter, so for this request, dtsearch could be a viable solution. It supports a whole bunch of file-types, including dbf files, and can be found at http://dtsearch.com (payware, no affiliation, found using a google search)

anandcoral:
Reindexing in the background software for databases

I have DBF files to organize.
I would like an application to reindex continually in the background even when these databases are in use.
Sort by all fields to be able to find everything. Even in the memo fields (notes).

Best Regards

 :-*
-Contro (July 02, 2014, 09:33 AM)
--- End quote ---

Hi Contro,

I use DBF files as database for my applications. I started with dBASEII and reached Xbase++ now.

A dbf file can be indexed by a foxpro/clipper application even if opened in shared mode, but is not advisable. One should have exclusive lock on the dbf file before creating idx/ntx.

Memo field values are stored in fpt/dbt files which can not be indexed.

For searching a dbf file you can use locate command of foxpro/clipper program, without any index.

I have made DBU8 utility based on DBU of Clipper, for Win 64 bit. It uses Xbase++ dlls, which are available with the demo version of our accounting program Ace9. DBU8 can be used to open a DBF/DBT file and create index NTX files. Search and Locate can be performed.
I made this utility for our in house staff as we have to view and manipulate data of our application.

You can rename a dbf file as xls and Excel will open it. In excel you can filter and search normally. A dbt file can not be opened in excel.

In case you need any further information, please tell me.

Regards,

Anand

Navigation

[0] Message Index

[#] Next page

[*] Previous page