Re: How to optimize query's execution?

From: uf <uf_at_one.com>
Date: Tue, 30 Aug 2005 15:57:19 +0200
Message-ID: <df1ok8$gpt$1@domitilla.aioe.org>

> Who could possibly answer that question? You've provided no Oracle
> version information, no operating system information, no query text, no
> table definitions, no samples of the table data or any information as
> to the configuration of your database (file locations, init.ora
> parameters) so of what good is the explain plan?

Ooops, sorry, I didn't know that. Well, question is coming from newbie, so please be patient.

Oracle: 9.2.0
OS: HP-UX B.11.11 U 9000/800 803930681 unlimited-user license

Query: select k.internal_id, k.keyname, k.keyvalue from my_keys k, my_jstate j where k.internal_id = j.internal_id and k.keyname in ('REQUEST_ID', 'SN_CODE', 'SP_CODE', 'CO_ID', 'CUSTOMER_ID', 'NUMBER', 'MAIN_NAME', 'MESSAGE_ID1', 'MESSAGE_ID2', 'MSN', 'SYSTEM','') and j.currentstate = 'X' and j.state = 'postponed' order by k.internal_id, k.keyname;

Execution Plan:

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'MY_KEYS'
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'MY_JSTATE'
   5    4           INDEX (RANGE SCAN) OF 'MY_STATE_IDX' (NON-UNIQUE)
   6    3         INDEX (RANGE SCAN) OF 'MY_KEYS_IDX' (NON-UNIQUE)

Tables:

decribe MY_KEYS;

Name
Null? Type

-------- -------------------------------------------------------- INTERNAL_ID NOT NULL VARCHAR2(30) KEYNAME NOT NULL VARCHAR2(60) KEYVALUE

VARCHAR2(100) describe MY_JSTATE;

Name

Null? Type

-------- -------------------------------------------------------- INTERNAL_ID NOT NULL VARCHAR2(30) STATE NOT NULL VARCHAR2(30) CURRENTSTATE CHAR(1) ENTRYDATE DATE TNAME VARCHAR2(32) JNAME VARCHAR2(256) MY_ID VARCHAR2(64) MY_STATUS VARCHAR2(256) MY_ERROR_CODE VARCHAR2(256) MY_ERROR_LTEXT VARCHAR2(256) MY_ERROR_STEXT VARCHAR2(256) PROGRAM VARCHAR2(32) USERNAME VARCHAR2(32) SKIP_NAME VARCHAR2(32) SKIP_RULE NUMBER(8) WAIT_TIME NUMBER(8)

Anything else? Received on Tue Aug 30 2005 - 08:57:19 CDT