Home » RDBMS Server » Performance Tuning » Oracle index creation (Oracle 10g)
Oracle index creation [message #647066] Mon, 18 January 2016 14:29 Go to next message
donep
Messages: 6
Registered: January 2016
Junior Member
I have an Oracle 10g installed and one of our accounting system tables are setup in that. I have a crystal report that accesses the financial data and displays it to the user. Sometime back I was asked to enable security in the report, so that when a user runs the report, they will only see the data (cost centres) that has been assigned to them.
Since this system is done by a third party, to enable the security, I created a table that has 3 fields (userid, facility, cost centre). In the report my table is linked to the main GL table with facility and cost centre fields. In my selection formula, I pass the logged in user id in Crystal.
Everything was good when I initially did this report only took about 400 seconds to run. After 3 years (with new data being added to the tables), it now takes over 30 mins to run the report.
When I created the table, I didn't create any indexes to it. I am thinking this is the reason for the slowness.
I was wondering if anyone could help me with creating indexes and how to make this run faster as 30 mins for a report is not acceptable.
Any help is greatly appreciated.
Re: Oracle index creation [message #647067 is a reply to message #647066] Mon, 18 January 2016 14:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes we can help you.
Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

[Updated on: Mon, 18 January 2016 14:49]

Report message to a moderator

Re: Oracle index creation [message #647601 is a reply to message #647067] Wed, 03 February 2016 20:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is some free material to help you get started with SQL Tuning, and to help you interact here with the team at ORAFaq better. These materials all are related to my book on SQL Tuning. If after previewing the materials you want the book, it is available on Amazon (now with a cheap Kindle version).

Chapter #3 discusses indexing and how to index properly for a query. I am allowed to offer some free items related to the book, though they are limited in what I can do due to publishing restrictions. Unfortunately chapter #3 is not part of the package.

Provided below are:

1. the first chapter of the book.  Reading this will help you decide if the book is something you are interested in before you spend money on it.
2. the scripts from the book.  You can use these in your tuning work regardless of it you purchase the book or not later.  These are free.
3. an organizational document that will help you record the significant events of a SQL Tuning session so you can explain to someone else later how you solved a problem.
4. a brief description of the kind of info you will want to provide to ORAFaq, when you are looking for detailed tuning help.


These artifacts are free, you do not need to buy the book to use them, and you can give them to others freely as well. Good luck. Kevin
Re: Oracle index creation [message #647642 is a reply to message #647601] Thu, 04 February 2016 08:54 Go to previous messageGo to next message
donep
Messages: 6
Registered: January 2016
Junior Member
Thanks for the reply. Much appreciated. Unfortunately the last 2 links don't work.
Re: Oracle index creation [message #647643 is a reply to message #647642] Thu, 04 February 2016 08:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
The last two are Word documents. When you say "don't work" do you mean they will not download? or that you cannot open them? or something else? You will need access to MS-WORD to open and edit them.

I was able to download and open them using a different compute and they work fine for me. Please provide more detail of your problem.

[Updated on: Thu, 04 February 2016 08:58]

Report message to a moderator

Re: Oracle index creation [message #647644 is a reply to message #647643] Thu, 04 February 2016 08:59 Go to previous messageGo to next message
donep
Messages: 6
Registered: January 2016
Junior Member
When I click on the link, I get a page with message "no data received".
I even tried to right click on it and save as, I don't even get the file save dialog box.
Re: Oracle index creation [message #647645 is a reply to message #647644] Thu, 04 February 2016 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe you have some restrictions at your place about downloading Word document.

Re: Oracle index creation [message #647646 is a reply to message #647644] Thu, 04 February 2016 09:40 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
Are you using Chrome?
Re: Oracle index creation [message #647647 is a reply to message #647646] Thu, 04 February 2016 10:18 Go to previous messageGo to next message
donep
Messages: 6
Registered: January 2016
Junior Member
Yes.
Re: Oracle index creation [message #647648 is a reply to message #647647] Thu, 04 February 2016 10:19 Go to previous messageGo to next message
donep
Messages: 6
Registered: January 2016
Junior Member
Doesn't work with IE either.
Re: Oracle index creation [message #647649 is a reply to message #647648] Thu, 04 February 2016 10:21 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
I suspect that Michel is right then. Have a word with your network people.
Re: Oracle index creation [message #647652 is a reply to message #647649] Thu, 04 February 2016 10:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
Later tonight, I will see if I can send you the files directly via your email.

Kevin
Re: Oracle index creation [message #647653 is a reply to message #647652] Thu, 04 February 2016 10:47 Go to previous messageGo to next message
donep
Messages: 6
Registered: January 2016
Junior Member
Thanks a lot. I will also try to open it once I go home from work.
Re: Oracle index creation [message #647847 is a reply to message #647653] Wed, 10 February 2016 00:26 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
I can't find a record of you sending me your email. Did you? Did I send you the requested files?

Kevin
Previous Topic: Please help for Improving Query Performance
Next Topic: Please help for Improving the Explain plan
Goto Forum:
  


Current Time: Fri Jan 19 23:53:51 CST 2018

Total time taken to generate the page: 0.08439 seconds