This query is taking over 6 minutes to run, and is doing a full table scan. The only indexes are SA_ID for both tables. Both tables have millions of records. Unfortunately I'm a programmer, not a DBA so I don't have privelages to use all the optimizing tools.. and I have to jump through some hoops to get indexes created. Is that the only solution in this case?
SELECT A.ACCT_ID, A.SA_TYPE_CD, B.SA_ID, SUM( B.CUR_AMT), A.SA_STATUS_FLG, TO_CHAR(A.START_DT,'YYYY-MM-DD')
FROM SA A, FT B
WHERE A.SA_ID = B.SA_ID
AND A.SA_TYPE_CD = 'E-WO'
AND A.BUSINESS_UNIT = 'COT'
AND A.SA_STATUS_FLG = '20'
AND A.START_DT > TO_DATE('2002-01-01','YYYY-MM-DD')
GROUP BY A.ACCT_ID, A.SA_TYPE_CD, B.SA_ID, A.SA_STATUS_FLG, TO_CHAR(A.START_DT,'YYYY-MM-DD')
HAVING SUM( B.CUR_AMT) < 0
8 2709
This query is taking over 6 minutes to run, and is doing a full table scan. The only indexes are SA_ID for both tables. Both tables have millions of records. Unfortunately I'm a programmer, not a DBA so I don't have privelages to use all the optimizing tools.. and I have to jump through some hoops to get indexes created. Is that the only solution in this case?
SELECT A.ACCT_ID, A.SA_TYPE_CD, B.SA_ID, SUM( B.CUR_AMT), A.SA_STATUS_FLG, TO_CHAR(A.START_DT,'YYYY-MM-DD')
FROM SA A, FT B
WHERE A.SA_ID = B.SA_ID
AND A.SA_TYPE_CD = 'E-WO'
AND A.BUSINESS_UNIT = 'COT'
AND A.SA_STATUS_FLG = '20'
AND A.START_DT > TO_DATE('2002-01-01','YYYY-MM-DD')
GROUP BY A.ACCT_ID, A.SA_TYPE_CD, B.SA_ID, A.SA_STATUS_FLG, TO_CHAR(A.START_DT,'YYYY-MM-DD')
HAVING SUM( B.CUR_AMT) < 0
Unfortunately, if there are no indexes on those other columns then oracle has no choice but to do a full table scan on those tables. as for why it takes 6 minutes, there are several factors. the full scans are obvious hits, but its also doing a hash join and then a hash group by. Is the process resource limited at all or does it get to use all the resources it wants?
if it is limited it could be doing swapping and stuff. what does the stats pack and tkprof say on it.
as for if it is the only choice, we have no idea... what are the requirements?
I'm pretty sure it doesn't have unlimited resources. As for tkprof, that's something I don't have permissions to. The criteria in the where claus don't have much room to change, they're all required for the query.
Here's the explain plan
[HTML]
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 36 K 79513
FILTER
SORT GROUP BY 36 K 2 M 79513
TABLE ACCESS BY INDEX ROWID SYSADM.FT 29 464 2
NESTED LOOPS 726 K 41 M 48942
TABLE ACCESS FULL SYSADM.SA 25 K 1 M 3677
INDEX RANGE SCAN SYSADM.FT 29 1
[/HTML]
Ouch... ya that full table scan is the big hit on performance, and im sure the sort group by isnt helping either.
You are going to have to create an index on the FT table so oracle can restrict the data coming back and find it.
Im trying to re create the issue at home but the only index i created is on the SA.SA_ID field. what are the indexes you have on the tables? I keep getting full table scans on both.
For the FT Table the only index is SA_ID. For the SA table there is SA_ID and SA_STATUS_FLG. I've changed the optimizer hint to RULE and it sped things up in dev, but not in production. Tried a few others but none helped. Not sure how to get around the group by, since I'm using SUM. Here's also something weird I discovered:
If I do
select a.sa_id from sa a; // it will do an index fast full scan, runs quickly
but
select a.sa_id from sa a, ft b; // does a merge join cartesian -> index fast full scan, buffer sort -> index fast full scan .. runs slowly.
Wondering if that has anything to do with my query.
We can Index Hint on the particular table and also make a exact conditions on that table to reduce the cost as well as we can see improvement in performance.
please create index for all of field, then you can search with all of field as search key
For the FT Table the only index is SA_ID. For the SA table there is SA_ID and SA_STATUS_FLG. I've changed the optimizer hint to RULE and it sped things up in dev, but not in production. Tried a few others but none helped. Not sure how to get around the group by, since I'm using SUM. Here's also something weird I discovered:
If I do
select a.sa_id from sa a; // it will do an index fast full scan, runs quickly
but
select a.sa_id from sa a, ft b; // does a merge join cartesian -> index fast full scan, buffer sort -> index fast full scan .. runs slowly.
Wondering if that has anything to do with my query.
the index on the sa table, are the two fields part of the same index or are they separate indexes (one for each field).
also, without any join information for the 2 queries the merge join cartesian makes sense right, you have not told oracle how to join the two tables. so if both tables have millions of rows... then its returning millions * millions = HUGE number of rows.
so you need to add the "where a.sa_id = b.sa_id" part. then it becomes a nested loops using indexes on both tables.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alex Vinokur |
last post by:
For instance, we need to measure performance
of assignment 'ch1 = ch2' where ch1 and ch2 are of char type.
We need to do that for different optimization levels of the same compiler.
Here is...
|
by: Rune |
last post by:
Is it best to use double quotes and let PHP expand variables inside strings,
or is it faster to do the string manipulation yourself manually?
Which is quicker?
1)
$insert = 'To Be';...
|
by: AC Slater |
last post by:
Whats the simplest way to change a single stored procedures query
optimization level?
In UDB8 that is.
/F
|
by: Eugene |
last post by:
I am trying to set query optimization class in a simple SQL UDF like
this:
CREATE FUNCTION udftest ( in_item_id INT )
SPECIFIC udftest
MODIFIES SQL DATA
RETURNS TABLE( location_id INT,...
|
by: WantedToBeDBA |
last post by:
Hi all,
db2 => create table emp(empno int not null primary key, \
db2 (cont.) => sex char(1) not null constraint s_check check \
db2 (cont.) => (sex in ('m','f')) \
db2 (cont.) => not enforced...
|
by: Kunal |
last post by:
Hello,
I need help in removing if ..else conditions inside for loops. I have
used the following method but I am not sure whether it has actually
helped.
Below is an example to illustrate what I...
|
by: mjbackues at yahoo |
last post by:
Hello.
I'm having a problem with the Visual Studio .net (2003) C++ speed
optimization, and hope someone can suggest a workaround.
My project includes many C++ files, most of which work fine...
|
by: wkaras |
last post by:
I've compiled this code:
const int x0 = 10;
const int x1 = 20;
const int x2 = 30;
int x = { x2, x0, x1 };
struct Y
{
|
by: db2admin |
last post by:
hi,
I have query which runs great when optimization level is changed to 3
but does not run fine with default optimization level of 5. since this
is a query in java code, i do not know how can i...
|
by: Ravikiran |
last post by:
Hi Friends,
I wanted know about whatt is ment by zero optimization and
sign optimization and its differences....
Thank you...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |