Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 398 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
index tuning help
we have a large table that i need to index.
here are the columns:
phone_number,
state,
revision_number,
data_month_year,
account_number,
File_date
I am assuming I shouldn't create an index for each column, should I?
The users will use any combination of these or maybe just one column in their where clause.
If i try to group them together, it may be something like:
date_month_year
or date_month_year & state & revision_number,
or date_month_year & phone_number
or date_month_year & account_number
State & file_date
or maybe all of them together?
thoughts on indexing, thanks.
Answers
-
If i try to group them together, it may be something like:
What do you mean by that? Does that mean that those are the possible combinations of conditions in your queries? If not, what does it mean?
Anyway, without much more information about the data in the table, we have no way to know what indexes you should create. For example, if you have only 2 possible states, creating an index on it is not a good idea, but we know nothing about the data by now.
-
3193178 wrote: we have a large table that i need to index. here are the columns: phone_number, state, revision_number, data_month_year, account_number, File_date I am assuming I shouldn't create an index for each column, should I? The users will use any combination of these or maybe just one column in their where clause. If i try to group them together, it may be something like: date_month_year or date_month_year & state & revision_number, or date_month_year & phone_number or date_month_year & account_number State & file_date or maybe all of them together? thoughts on indexing, thanks.
Do something called "requirements analysis" and figure out what the users actually need for their application. Assuming this is an OLTP type system the users must have some front end which they are using to query this information and presumably there will be a fixed set of circumstances in which they query this data. Having a user tell you they query by only account_number and later finding out that only 1 user does that 1% of the time would likely change your approach regarding indexing for that specific use case.
Cheers,
-
Hello,
searching the Web with your favorite search engine and "Oracle indexing strategy" will give you many links, some from Oracle Documentation -> have a look at these.
Also: I guess that your application is not in production yet, so have a look at the activity in the DEV/TEST environments, look at "heavy statements" (AWR reports) against the table that probably cause Full Table Scans. For those you can look at the benefit of adding "the useful index(es)".
Keep in mind that an index can be composite (built on multiple columns), and keep in mind that each index has a cost: the space it occupies, the maintenance cost when a row is inserted, deleted, or when the indexed columns are modified...
Best regards,
Bruno Vroman.
-
Also perform business analysis as to how the application and associated database objects is run and maintained.
But generally Oracle provides table/local/global index partition/sub-partition which means big table/index can be narrowed down for search. Most likely you will need some indexes but not all of them or some indexes can be used for multiple ad-hoc queries.
-
@3193178 :
Well Dump rows from V$Sql_Plan view every 30 mins, schedule a job and you will get fair amount of idea when it comes to making composite indexes.
PS:
1) you need to join these 3 tables rather:
v$sql_plan , all_indexes and V$sqlstats(will contain a column sql_text, hunt the where clause from this), make a query(select relevent columns) and dump the results in a table every 30 mins.
2) You need to monitor this data for some days and for sure you will get some idea.
3) And I have a mini tut for this activity, let me know if you need it.
Regards
Rahul