Solved

how to reduce query time for un indexed table

Posted on 2014-12-26
15
222 Views
Last Modified: 2015-01-05
hi
we purchased POS , where they provided a solution that daily basis every point of sales will send its data as text file
then to append it all into one table where it is un indexed and have too large records til now , and every day it is increase day by day
i don't like to touch the table , but when i do any query it takes minimum 30 minutes to finish
how i do to reduce time of query ?
0
Comment
Question by:NiceMan331
  • 6
  • 5
  • 2
  • +1
15 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40518561
Create indexes.
0
 
LVL 23

Assisted Solution

by:David
David earned 150 total points
ID: 40518605
As with Oracle eBS and some other ERP applications, there may be reasons that adding indexes is not viable.  Likewise, partitioning.  However, this is what partitioning was designed for.  Prior data sets, perhaps by transaction date, remain in the master table but can be excluded from data manipulation in the current period.  When I want to see month to date figures it's foolish to include anything outside of that boundary.

At some architected point, the old data must and should be purged out.

If you are querying with an Oracle function, only a function-based index will prevent a full-table scan.  Searching on TO_DATE(sysdate -30) requires a moment of processing.

If the new records are always APPENDED, you might consider adding a range constraint to your query. If you can track the max(rownum) from the prior load, perhaps add the clause where rownum > previous day's value.

Lastly for now, such a volatile table (daily adds) will adversely affect the cost-based-optimizer statistics,  There are manual and automated ways to update daily schema stats, but that's another topic.
0
 

Author Comment

by:NiceMan331
ID: 40518628
Thanx dvz for explanation , but first I cannot do any modification to the table , second , when I query , it is not necessary to query the new records only , I might need some data in the middle , or let me say , I can set fixed creteria to my search from that big table I always need to search within fixed specicifi records , any su to do it ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 150 total points
ID: 40518672
Create materialized view (or index-organized table) with the criteria you wish to query and re-build it daily?
If you cannot do any modification to the table, get someone that can and tell him/her about the issue.
0
 
LVL 23

Expert Comment

by:David
ID: 40518783
Nice, this thread brings up many of the better ways to improve query performance, as you requested.  You cannot modify the table, which I presume to include you cannot affect indexes, block storage, partitioning, materialized views, nor partial data sets.  The table increases daily, but is not purged of old data.  The status of the table, schema, and system statistics are unknown, and their refresh, if any, may not be compensating for the volatility.

In short, you're stuck.

What is your role if you cannot modify the architecture, and what is your relationship to those who can?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 200 total points
ID: 40519129
If the company will void your support contract by adding indexes, find a new vendor!!!

I can see them not supporting you if you change the physical model but an index is normal operations.

There is only one way to affect database performance without somehow altering the database:
Throw hardware at the problem.

The question as asked is:
I want to win more races and cannot change the car or driver in any way.  The only thing you can change is the other drivers or the track.  Sort of an impossible task don't you think?

Work with the vendor.  Many of them will initially resist enhancements to their design but will tend to bend once presented with actual data/facts.

Ask them how to get their software to work within your constraints.

Many software products put most of their time and money into the product and don't spend a lot of time and money into the database.  If the product supports multiple database platforms many companies will not hire top-notch DBAs for each database product they support so at times, overall performance suffers.

Convincing them you know more about Oracle than their design staff can let you add indexes and make subtle changes.

Just get their signatures on any change to prove they authorized it.

FYI:  Partitioning was mentioned above and I agree that it sounds like something you might benefit from but it requires additional licensing.
0
 

Author Comment

by:NiceMan331
ID: 40519351
thanx again
actually , the company proffessions only in the pos solution , but when we asked them about head quarter data analyses , they provides this solution only , i mean to upload the data set from text file to the oracle table
and they also provided a solution for data analyses ( analyses tools similiar to excell sheet ) based on copied tables
i understood the process they dod like this :
they upload daily data into many tables
full data to the main table mensioned in my question which become very large
and specific fields only uploaded in same time to another tables ( smaller size , easy to run , fast to query)
those tables were the base of the data anlyses solution they provided by them , but for me it is not enough , i require additional fields for expended analyse .
anyhow now the question is : if i want to use always fixed specific creteria with full fields from the main table , any way to do it ?
thanx
0
 

Author Comment

by:NiceMan331
ID: 40519353
mike
Create materialized view (or index-organized table) with the criteria you wish to query and re-build it daily?
is it normal view , or special craeted ?

If you cannot do any modification to the table, get someone that can and tell him/her about the issue
i can , but i don't like to do because no body knows that i'm using database tables
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40519892
>>is it normal view , or special craeted ?

Normal views store a query and when you select from the view, the stored query is executed.  A Materialized View (MV) executes the query and stores the results as a regular table.  The MV gets refreshed as the base tables change.  When and how often depends on how you set up the MV.

The online docs will tell you everything you want to know about them.

>>but i don't like to do because no body knows that i'm using database tables

This can get you fired or even arrested pretty quick if you are accessing data you are not supposed to be accessing.

>>if i want to use always fixed specific creteria with full fields from the main table , any way to do it ?

A MV will create a table based on whatever query you tell it to use so it may work for what you want.
0
 

Author Comment

by:NiceMan331
ID: 40520198
ok , let me take a look for the MV , then i will be back to here
anyhow , let me say something
when i do
select * from mytable
it result it in less than second
but when i used single condition , it takes such like 30 minutes
i think this is because no index , right ?
thanx
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40521007
>>select * from mytable it result it in less than second ... i think this is because no index , right ?

I think it is because of the tool you are using to run the query.  GUI tools like Toad and SQL Developer buffer start displaying rows as soon as it can.  It does not return ALL the rows at once so it may appear the query executes fast when it just returns the first few rows fast.

Run it in sqlplus and wait for the entire thing to return:
SQL> set timing on
SQL> select * from mytable;

Then wait...  setting timing on will tell you how long the query actually ran.
0
 

Author Comment

by:NiceMan331
ID: 40521574
SELECT WITH CONDITION result the following
real  828459
it takes exactly 13 minutes in sqlplus
same time in Toad
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40522787
So what does the query do in sqlplus without the condition?  That is what you were supposed to test since you stated "select * from mytable it result it in less than second".

I was wanting to see it your 'fast' results were due to Toad buffering the results.
0
 

Author Comment

by:NiceMan331
ID: 40523184
So what does the query do in sqlplus without the condition
withour condition it takes more than 35 minutes as per the big of data
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40524457
>>withour condition it takes more than 35 minutes as per the big of data

That is what I would expect not the "less than second" you mentioned above.

Anyway, the question has been answered:
Add indexes, change to partitioning or add hardware.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question