Solved

Need help on Index -Oracle -Challenging question

Posted on 2016-10-04
5
53 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
5 Comments
 
LVL 24

Expert Comment

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

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 125 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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
null value 15 93
Oracle DATE Column Space 11 62
Get FileTypes from a list of FileNames  in a Table field 18 43
Oracle Listener Not Starting 11 23
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now