Forum Stats

  • 3,783,346 Users
  • 2,254,760 Discussions
  • 7,880,370 Comments

Discussions

indexing in oracle 11g

984148
984148 Member Posts: 58
edited Mar 25, 2014 3:50AM in SQL & PL/SQL

Hi friends,

Very Good morning.

I am using oracle 11g with plsql developer. I am created a materialized view which copy the data from remote database through database link and copy into our own shema. we have also scheduled a job to refresh completely on weekly basis. But the problem we are facing is when an application connecting to the database to access the created materialized view it is taking hell lot of time. There i heard of indexing conecept which will speed up tha process. But i am not sure , on what column i have to create index.

I have a MV with following sample  structure and it is aggregated to sub team level. In such scenario what would be the column we could use for indexing. Please help me on this.

Deptteam sub teamNo resources
AA1A111
AA1A122
AA2A213
AA2A224
BB1B115
BB1B126
BB2B217
BB2B228

Thanks in advance.

Tagged:
984148

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Did you try to identify why the SQL on the MV is taking time? Don't jump into conclusions. Index is not some magic wand, use it and get the performance boosted. I would suggest you go through this FAQ

  • Moazzam
    Moazzam Member Posts: 1,356

    Share the query and the explain plan for it to help you find the root cause.

  • 984148
    984148 Member Posts: 58

    Hi ,

    Thanks for the reply. The query is working fine it is taking more than 2 min for executing but it is  having about 70 lakh rows. So while the application is reading the data it is taking so much. There I want to improve the performance,

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    Your following two statements contradict each other.

    Statement 1:

    The query is working fine it is taking more than 2 min for executing but it is  having about 70 lakh rows

    Statement 2:

    So while the application is reading the data it is taking so much. There I want to improve the performance,

    I don't understand what you are looking for. Please read the provided link and give the necessary details. If you are looking for silver bullet you are out of luck

  • onkar.nath
    onkar.nath Member Posts: 733

    If you mean to say that query is fine and taking expected time then be happy. There could be some issue with the application like integration part or ui itself, we dont know and I think everyone here including yourself would be more interested in db related issues. If you query is slow or taking more than expected then please do post your query and execution plan and anyone can provide some help. But application side, I doubt.

    Onkar

    984148
This discussion has been closed.