Re: Index and query help needed

Hi.

1. If index on PAY_END_DT for table PS_PAY_TAX exists ( or any index with PAY_END_DT as leading field ) check following:
2. What optimizer you are using - COST BASED or RULE BASED?
3. If you are using COST BASED OPTIMIZER - make ANALYZE TABLE PS_PAY_TAX COMPUTE STATISTICS; ANALYZE TABLE PS_PAY_CHECK COMPUTE STATISTICS;
4. If you are using RULE BASED OPTIMIZER - make shure that no statistics exist for both tables: ANALYZE TABLE PS_PAY_TAX DELETE STATISTICS; ANALYZE TABLE PS_PAY_CHECK DELETE STATISTICS;
5. Make shure PAY_END_DT is DATE column.
6. If no such index exists:

   Don't forget to add STORAGE clause to CREATE INDEX.

   CREATE INDEX PS_PAY_TAX$PAY_END_DT ON PS_PAY_TAX ( PAY_END_DT );    If you are using COST BASED OPTIMIZER - execute

       ANALYZE TABLE PS_PAY_TAX COMPUTE STATISTICS;  3. You are not supposed to see

    TABLE ACCESS FULL PS_PAY_TAX afterwards.     Instead you must get:
    TABLE ACCESS BY ROWID PS_PAY_TAX Cost Cardinality      INDEX RANGE SCAN <Index with PAY_END_DT as leading field> ...

 4. If everything fails - post info :

1. How many rows you have in both tables.
2. How many rows are selected from PS_PAY_TAX table.
3. What indexes you have there ( SELECT table_name, index_name, column_name FROM user_ind_columns WHERE table_name IN ( 'PS_PAY_TAX','PS_PAY_CHECK') ORDER BY table_name, index_name, column_position;

 HTH. Michael.

In article <8cdbpb$etk$1_at_nnrp1.deja.com>,   jamora_at_my-deja.com wrote:
> Hi
> Can someone help me with a Query we have that is taking too long?
> If someone can tell me how to improve the response time on this query
I
> will greatly appreciate it. Mainly I want suggestion on the indexes
> needed for the query to work
> Our programmer has added indexes but cannot avoid a full table scan
for
> this query and I think this is why it is taking too long, I am
> appending also the explain plan for the query
> The query goes like this:
> SELECT U.LOCATION, T.TAX_CLASS, T.TAX_CUR
> FROM PS_PAY_TAX T, PS_PAY_CHECK U
> WHERE t.PAY_END_DT between to_date('20-DEC-1999','DD-MON-YYYY')
> and to_date('26-DEC-1999','DD-MON-YYYY')
> and T.COMPANY = U.COMPANY
> and T.PAYGROUP = U.PAYGROUP
> and T.PAY_END_DT = U.PAY_END_DT
> and T.OFF_CYCLE = U.OFF_CYCLE
> and T.PAGE# = U.PAGE#
> and T.LINE# = U.LINE#
> and T.sepchk = U.sepchk
> The explain plan is:
> SELECT STATEMENT Cost Cardinality
> NESTED LOOPS Cost Cardinality
> TABLE ACCESS FULL PS_PAY_TAX Cost Cardinality
> TABLE ACCESS BY ROWID PS_PAY_CHECK Cost Cardinality
> INDEX UNIQUE SCAN PS_PAY_CHECK Cost Cardinality
> Any suggestions on what indexes to set?
> Thanks
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 15:48:44 CDT