[AlteredState]

I am having some substatial performance issues with a fairly straightforward join in an Oracle 8.1.7 data warehouse. I am joining a small table (41,000 rows) with a huge table (>9TB). The large table is indexed on fiscal week number, and order number. The small table is indexed on order number. Here is my SQL:

SELECT AAS.EMAIL_ADDRESS,
POSA.ORDER_NUM,
POSA.CUSTOMER_NUM,
POSA.LOCAL_CHANNEL,
POSA.ORDER_ADDRESS_SEQ_NUM,
POSA.PO_NUM,
POSA.SEGMENT_ID,
POSA.SHIP_CODE,
POSA.SHIP_DATE
FROM BIG_TABLE POSA,
LITTLE_TABLE AAS
WHERE ((AAS.ORDER_NUM = POSA.ORDER_NUM)
AND (POSA.FISCAL_WEEK_NUM > 200237)
AND (POSA.SOME_COLUMN1=29 AND POSA.SOME_COLUMN2=1 AND POSA.SOME_COLUMN3='I' AND POSA.SOME_COLUMN4=11 AND (POSA.STATUS='IN' OR POSA.STATUS='PD' OR POSA.STATUS='MN' OR POSA.STATUS='SC')))

This query currently takes approx. 80 minutes. If you have any suggestions on how I might speed this up, please reply.

TIA,

Jason Woosley
DBA

[BSchol]

You will have to find out what execution plan the optimizer is using. To do this start sqlplus and enter 'set autotrace on'. You might get an error about a table (explain_plan) that doesn't exist. If so run the script utlxplan.sql from ORACLE_HOME/rdbms/admin to create it.
After executing the query you will get information about how the query was executed. Look at this to see whether the index was used.

As a very good alternative to the above you could download TOAD (freeware version or trial ) from www.quest.com. This can give you the same info without having to wait until the query is finished. This makes it easier to modify and check the result.

[AlteredState]

Thanks for the quick reply.

Unfortunately I don't have the necessary permissions to view the execution plan -- they lock down the data warehouse pretty tight. I will give TOAD a look though. Thanks again for the advice.

[clfclfc]

Download an evaluation copy of LECCO DB Expert from the following web site:
http://www.leccotech.com

They have a SQL Optimizer tool that rewrites SQL statements in every way possible with unique execution plan and same result set.
Copy and paste your problematic SQL statement there, one click in optimize will give you every possible rewrite. Then you can benchmark in the tool the alternatives and you'll find the solution for your SQL statement.

This is the only tool that I have seen that automatically transforms your SQL statement syntax to solve SQL performance problems. There are other tools that you can use to try different Oracle hints, but with this tool from LECCO your SQL statement syntax will be automatically transformed in every way possible.

If there is any way to rewrite your SQL to run faster, this tool will automatically give you the answer.

Originally posted by AlteredState
I am having some substatial performance issues with a fairly straightforward join in an Oracle 8.1.7 data warehouse. I am joining a small table (41,000 rows) with a huge table (>9TB). The large table is indexed on fiscal week number, and order number. The small table is indexed on order number. Here is my SQL:

SELECT AAS.EMAIL_ADDRESS,
POSA.ORDER_NUM,
POSA.CUSTOMER_NUM,
POSA.LOCAL_CHANNEL,
POSA.ORDER_ADDRESS_SEQ_NUM,
POSA.PO_NUM,
POSA.SEGMENT_ID,
POSA.SHIP_CODE,
POSA.SHIP_DATE
FROM BIG_TABLE POSA,
LITTLE_TABLE AAS
WHERE ((AAS.ORDER_NUM = POSA.ORDER_NUM)
AND (POSA.FISCAL_WEEK_NUM > 200237)
AND (POSA.SOME_COLUMN1=29 AND POSA.SOME_COLUMN2=1 AND POSA.SOME_COLUMN3='I' AND POSA.SOME_COLUMN4=11 AND (POSA.STATUS='IN' OR POSA.STATUS='PD' OR POSA.STATUS='MN' OR POSA.STATUS='SC')))

This query currently takes approx. 80 minutes. If you have any suggestions on how I might speed this up, please reply.

TIA,

Jason Woosley
DBA