Re: What means my execution plan?

grrey_at_mail.ru wrote:
> There is a tables:
>
> create table stat -- about 2,000,000 records and 500,000 records with
> finish_date is null
> (
> User_id number,
> Start_Date date,
> Finish_Date date
> )
>
> create table log --about 15,000,000 records
> (
> Msg_id number primary key,
> User_id number,
> Kind_id number(1),
> Date_time date,
> Rec_type
> )
>
> There is an index on log table:
>
> Create index Log_indx on log(User_id,Kind_id,Rec_type,Date_time)
>
> I'm starting folowing query:
>
> select count(1) from stat_phone a
> where finish_date is null
> and not exists
> (
> select 1 from general_log
> where phoneto=a.phone
> and phonefrom=9152
> and Date_time>a.start_date
> )
>
> And Explane plan shows me plan:
>
> SELECT STATEMENT, GOAL = CHOOSE
> SORT AGGREGATE
> FILTER
> TABLE ACCESS FULL STAT
> INDEX RANGE SCAN LOG_INDX 4
>
> The question is - What's the hell oracle doing here? This should be
> fast query, but it makes already 2 hours. And another question - How to
> make it faster?

I have no idea what Oracle is or is not doing but I can comment with clarity on what you are doing and not doing.

1. Not providing Oracle version number.
2. Not providing information as to whether optimizer stats are current.
3. Not providing information with respect to column data types.

Can you provide more information?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)