SQL Optimization

[xhermit]

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

[Dave44]

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?

[xhermit]

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.

[xhermit]

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]

[Dave44]

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.

[xhermit]

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.

[bvbalaganesh]

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.

[imsoft]

please create index for all of field, then you can search with all of field as search key

[Dave44]

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.