Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help on Index -Oracle -Challenging question

Posted on 2016-10-04
5
Medium Priority
?
89 Views
Last Modified: 2016-10-07
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
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 30

Expert Comment

by:Pawan Kumar
ID: 41829326
Can you send your query here?
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 41829351
It depends on how you would run your script. Always ensure 1st column of EACH table on Where clause is INDEXED
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 41830335
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
 
LVL 30

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 500 total points
ID: 41830421
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 500 total points
ID: 41830667
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question