Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Categories

index tuning help

3193178
3193178 Member Posts: 17
edited Apr 8, 2016 1:57PM in SQL & PL/SQL

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.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • CarlosDLG
    CarlosDLG Member Posts: 1,378 Gold Trophy
    edited Apr 8, 2016 11:38AM 
    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.

    · Share on TwitterShare on Facebook
  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Apr 8, 2016 11:43AM 
    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,

    · Share on TwitterShare on Facebook
  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited Apr 8, 2016 1:00PM

    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.

    · Share on TwitterShare on Facebook
  • jihuyao
    jihuyao Member Posts: 462
    edited Apr 8, 2016 1:43PM

    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.

    · Share on TwitterShare on Facebook
  • Mac_Freak_Rahul
    Mac_Freak_Rahul Member Posts: 427
    edited Apr 8, 2016 1:57PM

    @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

    · Share on TwitterShare on Facebook
This discussion has been closed.