tuning

[dngaya]

hello,
I have a database of data oracle 8.1.7 installed of UNIX HP 11.
I have a request below of which I launched a tkprof of which here results.


select Ta1.o__num, Ta1.o__st, Ta1.o__edn, Ta1.o__kdn, Ta1.o__edx, Ta1.o__kdx,
Ta1.o__kdxa, Ta1.nm_st, Ta1.strt, Ta1.end_all, Ta2.o__num, Ta2.o__st
from
gtaia31d.cp_org Ta1, gtaia31d.cp_org_tk Ta2 where (((Ta2.token like :1) and
(Ta2.l_src=Ta1.o__num)) and ((Ta1.o__edn <= :2) and ((Ta1.o__kdn <= :3)
and (((Ta1.o__edx > :4) or (Ta1.o__kdx > :5)) and (Ta1.o__kdxa > :6)))))
order by Ta1.nm_sorting ASC


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 5.48 5.50 232 60128 5 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 5.48 5.50 232 60128 5 6

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 41 (JBU)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 SORT (ORDER BY)
0 NESTED LOOPS
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'CP_ORG'
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'CP_ORG_TK'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'CP_ORG_TK_TOKEN_L_SRC' (NON-UNIQUE)


here structures of the tables:
table cp_org:
O__NUM CHAR(13) NOT NULL,
O__ST NUMBER(4) NOT NULL,
O__EDN NUMBER(6) NOT NULL,
O__KDN NUMBER(6) NOT NULL,
O__EDX NUMBER(6) NOT NULL,
O__KDX NUMBER(6) NOT NULL,
O__KDXA NUMBER(6) NOT NULL,
TYPE_FLD NUMBER(4) NULL,
TRADE_REGISTER_NMB VARCHAR2(34) NULL,
TRADE_NM VARCHAR2(50) NULL,
STRT NUMBER(6) NULL,
RGSTRTN_NMBR VARCHAR2(14) NULL,
PNONETIC_TRADE_NM VARCHAR2(80) NULL,
PHONETIC_NM VARCHAR2(80) NULL,
ORGNSTN_INITIALS VARCHAR2(20) NULL,
NM_ST VARCHAR2(50) NULL,
NM_SORTING VARCHAR2(100) NULL,
NATIONALITY VARCHAR2(20) NULL,
LUMP_SUM_IND NUMBER(11,2) NULL,
LANG0 NUMBER(2) NULL,
L_RELATION CHAR(13) NULL,
L_AREA_CRTN CHAR(13) NULL,
KEY_MANUAL_ST VARCHAR2(30) NULL,
IDNTFR VARCHAR2(10) NULL,
END_REASON NUMBER(4) NULL,
END_ALL NUMBER(6) NULL,
CREATE_CNTRY_CODE VARCHAR2(2) NULL,
CNVRSN_COLLECTVE_A VARCHAR2(30) NULL,
BUSINESS_CODE VARCHAR2(5) NULL,
ACTVTY_1 VARCHAR2(80) NULL,
TYPE VARCHAR2(1) NULL,
SLCTN_KEY VARCHAR2(2) NULL,
POSTAL_CODE VARCHAR2(10) NULL,
OCCPTN_TYPE NUMBER(2) NULL,
L_LIVE_COUNTRY CHAR(13) NULL,
L_RSDNT_AREA CHAR(13) NULL,
L_DSTRBTR CHAR(13) NULL,
IDNTFR_PERSON VARCHAR2(50) NULL,
GRP_NMBR VARCHAR2(6) NULL,
GRND_CMPTE NUMBER(1) NULL,
ENTRPRS_NMBR VARCHAR2(6) NULL,
ENTER_DT NUMBER(6) NULL,
CNTR_CNTRCT NUMBER(3) NULL,
BRNCH_NMBR VARCHAR2(6) NULL


table cp_org_tk:

O__NUM CHAR(13) NOT NULL,
O__ST NUMBER(4) NOT NULL,
TOKEN VARCHAR2(80) NULL,
L_SRC CHAR(13) NULL

here indiexes on table cp_org:
UNIQUE INDEX GTAIA31D.DI$CP_ORG
ON GTAIA31D.CP_ORG(O__NUM ASC,O__ST ASC,O__EDN ASC,O__KDN ASC,O__EDX ASC,O__KDX ASC,O__KDXA ASC)


here indiexes on table cp_org_tk:
INDEX GTAIA31D.CP_ORG_TK_TOKEN_L_SRC
ON GTAIA31D.CP_ORG_TK(TOKEN ASC,L_SRC ASC)

why the plan of execution shows a full scan on the table cp_org and yet the columns of the clause where of this table were indexed.
thank you for your assistance.

[Volker Borowski]

Hi,

bad news! All "non-equal" comparisons can not be index supported.
So no real luck for accessing CP_ORG other than FTS.

(Ta2.l_src=Ta1.o__num) is the only one that might be worth something, but your index is on "token" first, so the RANGE-Scan given on this index will be for the
(Ta2.token like :1) expression only.
In addition, this will only support the join, not the select.

Try index L_SRC,TOKEN on CP_ORG_TK to give the join a better support. This may only help if CP_ORG_TK has a lot of rows.

May be for "<",">","<=" ... a function-based Index will help, but I never dealt with these yet.

Good hunting
Volker

[dngaya]

hello,
how can I make for resoudre this problem?. thank you for your assistance

[Yogeeraj_1]

hi,

step 1:

make sure the statistics on tables cp_org and cp_org_tk are up to date.

Step 2:
Check if the status of the indexes are VALID.
(user_indexes.status)

step 3:
...

will spend some more time tomorrow at the office..and update this post...

regards
Yogeeraj