OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Explain Plan

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Explain Plan [message #149540]

Fri, 02 December 2005 03:14

dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi

Junior Member

I have a table dept

Dept(Table) Total Rows:-1000
Deptno (primary Key)
Description (non unique index)

i am firing a query:-
explain plan for select * from dept
where deptno=10 or description ='Description-1'

and the explain Plan Generated is:

SELECT STATEMENT CHOOSE
TABLE ACCESS DEPT FULL

Q:- why Full table scan not Concatenation(because of using Multiple or containing indexed columns)

Report message to a moderator

Re: Explain Plan [message #149578 is a reply to message #149540]

Fri, 02 December 2005 06:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> Q:- why Full table scan not Concatenation(because of using Multiple or containing indexed columns)

What is "Q" ?
If "Q" somehow means "question" and i do not understand your "Q".
Please rephrase.
Are you asking why your index is not used?
There are many factors.
Did you collect the statistics on tables/indexes?.
Or
may be because of your "OR"
>>or description ='Description-1'

[Updated on: Sat, 03 December 2005 01:20]

Report message to a moderator

Re: Explain Plan [message #149702 is a reply to message #149540]

Sat, 03 December 2005 00:51

bsubbu
Messages: 42
Registered: November 2005
Location: Hyderabad

Member

hi,,
as mahesh sir said..
if u did not collect statistics..
collect statistics..
(1) analyze table dept compute statistics;
(2) explain plan for select * from dept where deptno=10;

Regards
Subbu

Report message to a moderator

Re: Explain Plan [message #149825 is a reply to message #149578]

Mon, 05 December 2005 03:37

dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi

Junior Member

Since the query is involving multiple or with different indexed columns, so it should use concatenation.

All the tables and indexes are already analysed or statistics are computed.

Report message to a moderator

Re: Explain Plan [message #149866 is a reply to message #149825]

Mon, 05 December 2005 08:09

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Depends on actual data distribution and available statistics to the CBO.
Based on the available statistcs, CBO will decide the plan.
I took the regular emp table here.

scott@9i > get emp_loopinsert;
  1   begin
  2   for mag in 1..15 loop
  3       insert into emp (select * from emp);
  4   end loop;
  5   commit;
  6*  end;
scott@9i > @emp_loopinsert

PL/SQL procedure successfully completed.

scott@9i > select count(*) from emp;

  COUNT(*)
----------
    458752

scott@9i > create index deptno_index on emp(deptno);

Index created.

scott@9i > create index job_index on emp(job);

Index created.

scott@9i > analyze table emp compute statistics for all indexed columns;

Table analyzed.

scott@9i > set autotrace traceonly exp
scott@9i > select ename from emp where job='CLERK' or DEPTNO=10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONCATENATION
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       INDEX (RANGE SCAN) OF 'DEPTNO_INDEX' (NON-UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   5    4       INDEX (RANGE SCAN) OF 'JOB_INDEX' (NON-UNIQUE)


scott@9i >  exec dbms_stats.set_table_stats('SCOTT','EMP',NUMROWS=>100);

PL/SQL procedure successfully completed.

scott@9i > select ename from emp where job='CLERK' or DEPTNO=10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=44 Bytes=704
          )

   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=11 Card=44 Bytes=704)

Report message to a moderator