hi all

i have the below query which takes a very long time to execute.
the explain plan shows that there is a full table scan on both the tables.
the query seems to ignore any hints i use to force use index. HOW TO IMPROVE THE QUERY PERFORMANCE.WHAT IS THE REASON THAT INDEX IS NOT GETTING USED, I EVEN TRIED PUTTING A EXTRA CONDITION ac_gl_no = :B1, STILL THE INDEX FOR AC_GL_NO IS NOT GETTING USED.

i have tried the following without any performance improvement
1. use not exists condition instead of not in
2.

table details
sttbs_account - 3 million records
gltm_glmaster - 2k records

select ac_gl_no,
ac_gl_ccy,
IBAN_AC_NO,
decode(ac_or_gl,'A',decode(:g,'Y',cust_name1||' - '))||
ac_gl_desc ac_gl_desc
from sttbs_account where
branch_code is not null or
(branch_code is null and
nvl(branch_code, nvl(:b,'***'))
= nvl(:b,'***'))
and auth_stat = 'A' and ac_gl_rec_status = 'O'
and ac_gl_no not in (select gl_code from gltm_glmaster
where posting_res = 'N')
order by ac_or_gl, ac_gl_no



SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT ORDER BY | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | STTB_ACCOUNT | | | |
|* 4 | TABLE ACCESS FULL | GLTM_GLMASTER | | | |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("SYS_ALIAS_1"."BRANCH_CODE" IS NOT NULL OR
"SYS_ALIAS_1"."BRANCH_CODE" IS NULL AND
NVL("SYS_ALIAS_1"."BRANCH_CODE",NVL(:Z,'***'))=NVL(:Z,'***') AND
"SYS_ALIAS_1"."AUTH_STAT"='A' AND "SYS_ALIAS_1"."AC_GL_REC_STATUS"
AND NOT EXISTS (SELECT 0 FROM "GLTM_GLMASTER" "GLTM_GLMASTER" WHE
LNNVL("GLTM_GLMASTER"."GL_CODE"<>:B1) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"GLTM_GLMASTER"."POSTING_RES"='N'))
4 - filter(LNNVL("GLTM_GLMASTER"."GL_CODE"<>:B1) AND
"GLTM_GLMASTER"."POSTING_RES"='N')

Note: rule based optimization

INDEX INFORMATION FOR STTBS_ACCOUNT

1 IDX_IBAN_AC IBAN_AC_NO 1
2 IND_BRN BRANCH_CODE 1
3 IND_CCY_BRN AC_GL_CCY 2
4 IND_CCY_BRN AC_OR_GL 1
5 IND_CCY_BRN BRANCH_CODE 3
6 IND_CUST_NO_ACC CUST_NO 1
7 I_AC_GL_NO AC_GL_NO 1
8 I_ALT_AC_NO ALT_AC_NO 1
9 PK_STTB_ACCOUNT PKEY 1

- make sure stats are latested and updated on the tables.
dbms_stats.gather_table_stats('owner','table_name',cascade=>true);

-try /*+first_rows_n*/ hint.

letus know if it helps (or not)...

What causes a full table scan? You have a few conditions which lead to one. First, if you don't know what causes or leads to FTS, research that. Then, look at your statement and see where that may be occurring. Also, what is the selectivity of your indexed columns?

"the query seems to ignore any hints i use to force use index. "

Then you may be entering it wrong. If not exact, it;s treated as a comment.

You have a NOT IN Subquery. Can you re-write it as an outer join with a NOT NULL test?

http://www.dba-oracle.com/oracle_tips_subq_rewrite.htm

That will run MUCH faster.

Your hint suggestion does not work. There is no such hint as first_rows_n. first_rows(n), yes, but yours, no. See how 50 is shown in the rows column when the correct format/syntax is used, versus yours in the second output. What you're referring to is a parameter setting, not a hint, the format for each is slightly different.

thanks for the replies

1. the database is running on 9i, rule based optimization. hence there are no statistics update.

2. i understand the ""or"" condition in the query forces the full table scan. is it true that or condition in my situation is forcing a full table scan.

is there any way the sql could be rewritten to avoid the or condition.

3.i am working parallely on few queries, which i am successfully able to use hints.

thanks again

"rule based optimization. hence there are no statistics update."

The RBO is very simple. However, you can hint the RBO, provided that you have optimizer statistics.

"is there any way the sql could be rewritten to avoid the or condition."

Did you try replacing the NOT IN subquery with the outer join?