Solved

how to reduce query time for un indexed table

Posted on 2014-12-26
15
216 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
 
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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now