Solved

Need help on Index -Oracle -Challenging question

Posted on 2016-10-04
5
75 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 28

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 77

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 28

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
form builder not starting 3 72
upgrading Oracle 10g/ 11g / 11g R2 to Oracle 12c 25 88
ORA-02288: invalid OPEN mode 2 81
Oracle cursor lifecycle inside procedure. 2 25
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

756 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