Q
Manage Learn to apply best practices and optimize your operations.

How to design a table and database to return the quickest results?

I have table EMP with following fields:

NAME              VARCHAR2(256)
DEPARTMENT        VARCHAR2(10)
EMPNUM            VARCHAR2(10)
SALARY            VARCHAR2(10)

There are 2,000,000 entries in the table.

My Select is:

SELECT NAME FROM EMP WHERE DEPARTMENT='5' and EMPNUM='456' and SALARY='12345';

My Question is:

How should I design the table and database, and what DB is better to use so that select will return the results with maximal speed? I am using OracleLite8i.

Thanks a lot,
Jonathan Kukin


Which database you use all depends on a number of factors. Each vendor will say that their database is the fastest. And each vendor's database has their pros and cons. For most people, the decision is made taking into account cost, availability, scalability, performance, and current skill set. If you already have a shop filled with Oracle experts, why try to employ a DB2 solution? But no matter which database is chosen, each of the big players (Oracle, Microsoft, IBM, etc.) in the database market has good products.

How should you design the database? This is normally driven by the business rules. For instance, in many companies, the employee id is a unique identifier and determines the employee's name, address, and position. There is an entire discipline devoted to good database design--too much, in fact, to discuss in this short response.

Taking your table into consideration, it logically follows that the EMPNUM determines the NAME, DEPARTMENT and SALARY information. So unless there is other information that I am missing, the EMPNUM column is a good choice for the primary key.

Your query,

SELECT NAME FROM EMP WHERE DEPARTMENT='5' and EMPNUM='456' and 
SALARY='12345';

has a lot to search through if you let Oracle search through all 2 million rows. To aid your search, you might want to use an index. You have two choices here. One, if the EMPNUM is the primary key, then you can use that index alone to aid your search, but you must rewrite your query to use that index. Make the EMPNUM condition the first condition in your WHERE clause by rewriting your query as follows:

   SELECT NAME
   FROM EMP
   WHERE EMPNUM='456' and DEPARTMENT='5' and SALARY='12345';

Or, you could employ a concatenated index made of multiple columns. First, create the index:

   CREATE INDEX table_emp_dept_sal
   ON table (empnum,salary,department);

In order to use this index, the conditions in the WHERE clause must be in the same order that the columns are specified in the index. So you'd have to rewrite your query as:

   SELECT NAME
   FROM EMP
   WHERE EMPNUM='456' and SALARY='12345' and DEPARTMENT='5';

But be warned that if this table experiences a high number of inserts, updates, or deletes, then the cost of maintaining the index may outweigh the gains of this single query.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close