OraFAQ Forum: Performance Tuning » Oracle index creation

Home » RDBMS Server » Performance Tuning » Oracle index creation (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle index creation [message #647066]

Mon, 18 January 2016 14:29

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.

Report message to a moderator

Re: Oracle index creation [message #647067 is a reply to message #647066]

Mon, 18 January 2016 14:48

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Kevin Meade
Messages: 2103
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

Attachment: Oracle SQL Performance Tuning and Optimization v26 chapter 1.pdf
(Size: 783.27KB, Downloaded 2815 times)
Attachment: scripts.rar
(Size: 21.94KB, Downloaded 2335 times)
Attachment: SQL Tuning Worksheet 3.docx
(Size: 128.56KB, Downloaded 2485 times)
Attachment: Information needed in Tuning a SQL Query.docx
(Size: 24.87KB, Downloaded 2504 times)

Report message to a moderator

Re: Oracle index creation [message #647642 is a reply to message #647601]

Thu, 04 February 2016 08:54

donep
Messages: 6
Registered: January 2016

Junior Member

Thanks for the reply. Much appreciated. Unfortunately the last 2 links don't work.

Report message to a moderator

Re: Oracle index creation [message #647643 is a reply to message #647642]

Thu, 04 February 2016 08:57

Kevin Meade
Messages: 2103
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

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.

Report message to a moderator