Home » RDBMS Server » Performance Tuning » Explain Plan
Explain Plan [message #149540] Fri, 02 December 2005 03:14 Go to next message
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)

Re: Explain Plan [message #149578 is a reply to message #149540] Fri, 02 December 2005 06:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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 Go to previous messageGo to next message
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
Re: Explain Plan [message #149825 is a reply to message #149578] Mon, 05 December 2005 03:37 Go to previous messageGo to next message
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.

Re: Explain Plan [message #149866 is a reply to message #149825] Mon, 05 December 2005 08:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
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)
Re: Explain Plan [message #149905 is a reply to message #149540] Mon, 05 December 2005 12:05 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Good demo.
Re: Explain Plan [message #150014 is a reply to message #149866] Tue, 06 December 2005 00:38 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Nice one Mahesh!
Previous Topic: Script Needed -- SQL Statements - Performance Tuning
Next Topic: tkprof different types of sort
Goto Forum:
  


Current Time: Fri Mar 29 04:44:18 CDT 2024