Re: How to optimize query's execution?

"uf" <uf_at_one.com> wrote:
> > 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;

How many rows in j have currentstate='X'? How many rows have state='postponed'? How many rows have both simultaneously?

What fraction of the rows in k satisfy the in-list?

>
> 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)
...
> Anything else?

What column(s) (and in what order) are included in the my_state_idx index?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB