can you tell me any mechanism to store thousands of words in my table?

Question

i am developing a dictionary that converts from english language to amharic language.i am using vb.net.Then i create a table with three fields that are id ,english and amharic .
the i want to store more than 13,000 english and their crosponding meaning in amharic. i think the number of pages may be so large. so do you have any comment or hint about this?
what mechanism should i use to reduce the number of pages as much as possible?

Answer 1

Using Large Objects

First you need to set up your database schema, which will contain the tables used in this article. You must first create a user named lob_user and grant the required privileges to that user as follows. (You must first log into the database as a user with database administrator privileges to create a user and grant privileges):

CREATE USER lob_user IDENTIFIED BY lob_password;
GRANT CONNECT, RESOURCE, CREATE ANY DIRECTORY TO lob_user;

You will find the two previous statements and the others shown in this section to set up the store schema in the sample code file lob_db.sql.

The following statement creates a directory named SAMPLE_FILES_DIR that points to the C:\sample_files directory on the hard drive of the server; you must create the sample_files directory in the C: partition of your hard drive and copy the textContent.txt and binaryContent.doc files to C:\sample_files.

CREATE OR REPLACE DIRECTORY SAMPLE_FILES_DIR AS 'C:\sample_files';

Note: The textContent.txt and binaryContent.doc files contain quotes from Shakespeare's play Macbeth. You will see the content from these files copied to the database shortly.

The next statement grants the read permission to the public, so that all users can read the contents of SAMPLE_FILES_DIR:

GRANT READ ON DIRECTORY SAMPLE_FILES_DIR TO PUBLIC;

The next statement connects as lob_user:

CONNECT lob_user/lob_password;

You will see the use of three tables in this article:

* clob_content: contains a CLOB column named clob_column. This column is used to store the character data contained in the textContent.txt file.
* blob_content: contains a BLOB column named blob_column. This column is used to store the binary data stored in the binaryContent.doc file.
* bfile_content: contains a BFILE column named bfile_column. This column is used to store pointers to the two external files.

The following statements create the clob_content, blob_content, and bfile_content tables:

CREATE TABLE clob_content (
id INTEGER PRIMARY KEY,
clob_column CLOB NOT NULL
);

CREATE TABLE blob_content (
id INTEGER PRIMARY KEY,
blob_column BLOB NOT NULL
);

CREATE TABLE bfile_content (
id INTEGER PRIMARY KEY,
bfile_column BFILE NOT NULL
);

If you create the tables in a different schema to lob_user, you will need change the schema name in the sample programs that you will see later.

The next two statements add an empty CLOB and BLOB to the clob_content and blob_content tables:

INSERT INTO clob_content (
id, clob_column
) VALUES (
1, EMPTY_CLOB()
);

INSERT INTO blob_content (
id, blob_column
) VALUES (
1, EMPTY_BLOB()
);

The following PL/SQL statements load the text from the file textContent.txt into the clob_content table and load the binary from the file binaryContent.doc into the blob_content table:

DECLARE
my_clob CLOB;
my_blob BLOB;
my_bfile BFILE;
BEGIN
-- load the CLOB
my_bfile := BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt');
SELECT clob_column
INTO my_clob
FROM clob_content
WHERE id = 1 FOR UPDATE;
DBMS_LOB.FILEOPEN(my_bfile, dbms_lob.file_readonly);
DBMS_LOB.LOADFROMFILE(my_clob, my_bfile, DBMS_LOB.GETLENGTH(my_bfile), 1, 1);

-- load the BLOB
my_bfile := BFILENAME('SAMPLE_FILES_DIR', 'binaryContent.doc');
SELECT blob_column
INTO my_blob
FROM blob_content
WHERE id = 1 FOR UPDATE;
DBMS_LOB.FILEOPEN(my_bfile, dbms_lob.file_readonly);
DBMS_LOB.LOADFROMFILE(my_blob, my_bfile, DBMS_LOB.GETLENGTH(my_bfile), 1, 1);

DBMS_LOB.FILECLOSEALL();
COMMIT;
END;
/

The next statement makes the BFILE in the bfile_content table point to the textContent.txt file located in the SAMPLE_FILES_DIR directory:

INSERT INTO bfile_content (
id,
bfile_column
) VALUES (
1,
BFILENAME('SAMPLE_FILES_DIR', 'textContent.txt')
);

Reading Data from a LOB Using C# and VB.NET

There are two ways to retrieve a LOB:

1. Defer LOB retrieval using LOB locators: ODP.NET retrieves a pointer to the LOB on the database server. No actual LOB data is retrieved until the application begins reading the LOB. For a CLOB, you use an object of the Oracle.DataAccess.Types.OracleClob class to store the locator that you read using the GetOracleClob() method of an OracleDataReader object, and then use the Read() method to access the data stored in the OracleClob. For a BLOB, you use an Oracle.DataAccess.Types.OracleBlob object and use the GetOracleBlob() method.
2. Retrieve all or much of the LOB data immediately: ODP.NET will retrieve LOB data for all LOBs as soon as the SELECT statement executes. For a CLOB, you read the data using the GetString(), GetChars(), GetValue(), or GetOracleString() methods of an OracleDataReader. For a BLOB, you use the GetBytes(), GetValue(), GetValues(), or GetOracleBinary() methods.

In the second scenario, the amount of data read from the LOB for the first round trip to the database depends on the setting of the InitialLOBFetchSize property of the OracleDataReader object, whose value is inherited from the OracleCommand object. The default value for InitialLOBFetchSize is 0—meaning that retrieval of the data in the LOB is deferred until the program explicitly requests the data (i.e. the first scenario). If you change InitialLOBFetchSize to a value greater than zero, then the LOB data is immediately retrieved in one round trip up to the number of bytes or characters you specify in InitialLOBFetchSize.

All the LOBs in the SELECT statement execution are affected by this parameter. For example, if you set InitialLOBFetchSize to 5K and 10 LOBs will be retrieved in your SELECT statement execution, then the first 5K of each of the 10 LOBs will be retrieved in one database round trip. Currently, the maximum setting in release 10.1.0.2.0 of ODP.NET for InitialLOBFetchSize is 32KB. Oracle will increase this maximum size to 2GB in a future release.

Note: If you change InitialLOBFetchSize from 0, then you can only use the accessors in method 2 mentioned earlier to read from a CLOB—although Oracle plans to remove that restriction. In a future ODP.NET release, you will be able to use both methods to retrieve LOBs.

If the amount of data in all the LOBs you are selecting is not large and you are selecting many LOBs, then you will likely see better performance retrieving LOB data immediately by changing InitialLOBFetchSize from its default 0 value. If you use InitialLOBFetchSize, then you should set it to a value slightly larger than the size of 80% of the LOBs selected. For example, if 80% of the LOBs in the rows are 1KB or less, then you should set InitialLOBFetchSize to 1KB. You should experiment with your setup to find the optimum setting for InitialLOBFetchSize since your results will depend on things like network performance, latency, size of data, and so on.

Immediate LOB retrieval occurs when you change InitialLOBFetchSize from its default value of 0. Deferred LOB retrieval occurs when you leave InitialLOBFetchSize in its default value of 0. The following table provides some principles to consider when deciding between using deferred LOB retrieval and immediate LOB retrieval.

More with the link below.

Answer 2

First off, you would use some type of web scripting(php, asp, jsp) etc and you would embed just the portions of the screen that are dynamic with the scripting code.

Next, I would look into using SoundEx to store the value of the words, this would allow people to do a "fuzzy" search on English words to get a list of choices that is phoenetically similiar. The algorithm is pretty simple, you can write it in about 50 lines of code.

This allows you to perform a search, get a list of possible choices in English, and then display the actualy result. You would only store the text in the database, I am not sure the need or value of Blobs, you do not want to store the entire HTML page in the DB, as that has lots of problems with respect to changing formatting, size, etc.

Lastly, since you are storing Amaric, make sure the database you use can store Unicode and make sure you get the encoding correct on your web pages.

Answer 3

The answer to this question may vary based on how you are outputting the data to be visible. I'd include one or more additional fields that include stuff like part of speech (i.e. noun, adjective, etc.) and the definition.

First, create a class that has all the fields. Next create a collection of the words.

Iterate through the collection and output using your chosen method. If you contact me via y! email, I can bang up some code for you. Whatever the final answer, I'll post it here.