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 table.
My SELECT is:
SELECT NAME FROM EMP WHERE DEPARTMENT='5' and EMPNUM='456' and SALARY='12345';
My Question is:
How I should design the table and database (and what DB is better to use?) in order for SELECT to return the results with maximal speed?
Thanks a lot,
The ideal solution would be to use DRI (Declarative Referential Integrity). Assuming that your EMPNUM values are unique (no two employees could ever have the same EMPNUM value)
CREATE TABLE EMP ( NAME VARCHAR2(256) , DEPARTMENT VARCHAR2(10) , EMPNUM VARCHAR2(10) CONSTRAINT XPKEMP PRIMARY KEY (EMPNUM) , SALARY VARCHAR2(10) )
Using DRI would provide thedatabase engine with the maximum possible information to speed your query.
A workable second best would be to create an index on the column.
CREATEUNIQUE INDEX XPKEMP ON EMP (EMPNUM)
This isn't as good as DRI, but it should be a drastic improvement over nothing at all.
If some of your columns are Foreign Keys (they are the Primary Key or PK in another related table), it will help your overall performance if you declare them in your EMP table too. In your example, the DEPARTMENT column would be a likely candidate for being a Foreign Key.
Any of the major database engines should handle this task nicely. My personal preference for this kind of job is Microsoft SQL Server, but Oracle, DB2, and others would work nicely. As long as there were very few (better yet only one) users, even desktop databases like Jet (used by Access) could handle this problem.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.