Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

Need help on Index -Oracle -Challenging question

Hi Team,


Below is the structure of the table , It contains around 9 million records . The query takes a long time If i have to fetch a record for a condition on emailAddr or workid or batchid or subid.



Structure of Tmp_email
WORKID  NUMBER
 BatchID NUMBER
 SubID  NUMBER
 EmailAddr VARCHAR2(80)
 FirstName VARCHAR2(80)
 LastName VARCHAR2(80)
 pinCode NUMBER
 Senddate timestamp

I need an help on indexes which  type of index would best suit this scenario.
0
sam_2012
Asked:
sam_2012
3 Solutions
 
Pawan KumarDatabase ExpertCommented:
Can you send your query here?
0
 
HuaMinChenBusiness AnalystCommented:
It depends on how you would run your script. Always ensure 1st column of EACH table on Where clause is INDEXED
0
 
slightwv (䄆 Netminder) Commented:
Order of column in the where clause doesn't matter.

If you have any combination of columns in the query and they are 'or' then I would probably look at a single index on each individual column.
0
 
Pawan KumarDatabase ExpertCommented:
Each table should have a Clustered Index [usually id column of integer type] which sort the data physically. It will also provides the unique information to the engine.

Other columns the where clause - Consider creating Non clustered indexes. But these one depends on the query you have written. What columns we can choose..how many times this query is going to run against the Db, etc.

How many indexes you can create - because they are not free , they take space , you will have to maintain them and if DML happens on the table , then restructuring of the tree and in return you will get data quickly.

So overall we can say IT Depends..
0
 
Mark GeerlingsDatabase AdministratorCommented:
Without seeing your query (or queries?) it is very difficult for us to make recommendations.  Even if you post your queries, you may also have to tell us something about the number of distinct values in these columns: emailAddr, worked, batchid and subid.

If the number of distinct values in these columns is close to the number of records, then a separate index on each of them may be helpful *IF* your query or queries refer to each of them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now