Solved

Need help on Index -Oracle -Challenging question

Posted on 2016-10-04
5
43 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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Can you send your query here?
0
 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
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
Comment Utility
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 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 125 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

14 Experts available now in Live!

Get 1:1 Help Now