The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Thanks for the question, madhan.

Asked: November 27, 2016 - 6:26 am UTC

Last updated: November 27, 2016 - 8:07 am UTC

Version: Oracle 10g XE

Viewed 1000+ times

You Asked

What are the things that result in FULL TABLE SCAN?? Thanks in advance...

and Connor said...

The optimizer makes that decision, and like all queries, it will choose a plan that it has calculated to be most efficient.

That is of course not a guarantee it will always come up with the correct plan - it is just a large mathemetical algorithm to try be right as much as possible.

So anything where the optimizer thinks it is best to scan all of the blocks in the table will yield a full table scan, eg

select count(nullable_column) from T;

must visit every single block in the table, so a full table scan would be a logical choice.

select * from T where indexed_column = 1;

*might* use an index, but what if *every* row for that column was "1". Then it would still make sense to do a full scan.

These are the kinds of things the optimizer tries to ascertain.

Rating

(1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Nullable columns ??

Rajeshwaran, Jeyabal, November 28, 2016 - 4:24 am UTC

....
So anything where the optimizer thinks it is best to scan all of the blocks in the table will yield a full table scan, eg 

select count(nullable_column) from T; 

must visit every single block in the table, so a full table scan would be a logical choice. 
....

demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> set linesize 71
demo@ORA12C> desc t
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 OWNER                               NOT NULL VARCHAR2(128)
 OBJECT_NAME                         NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                               VARCHAR2(128)
 OBJECT_ID                           NOT NULL NUMBER
 DATA_OBJECT_ID                               NUMBER
 OBJECT_TYPE                                  VARCHAR2(23)
 CREATED                             NOT NULL DATE
 LAST_DDL_TIME                       NOT NULL DATE
 TIMESTAMP                                    VARCHAR2(19)
 STATUS                                       VARCHAR2(7)
 TEMPORARY                                    VARCHAR2(1)
 GENERATED                                    VARCHAR2(1)
 SECONDARY                                    VARCHAR2(1)
 NAMESPACE                           NOT NULL NUMBER
 EDITION_NAME                                 VARCHAR2(128)
 SHARING                                      VARCHAR2(13)
 EDITIONABLE                                  VARCHAR2(1)
 ORACLE_MAINTAINED                            VARCHAR2(1)

demo@ORA12C> set linesize 151
demo@ORA12C> create index t_idx1 on t(data_object_id);

Index created.

demo@ORA12C> select count(*),count(data_object_id) from t;

  COUNT(*) COUNT(DATA_OBJECT_ID)
---------- ---------------------
     82033                   359

1 row selected.

demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ gather_plan_statistics */ count(data_object_id) from t;

COUNT(DATA_OBJECT_ID)
---------------------
                  359

1 row selected.

demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID  d08vqmbyt3cpg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(data_object_id) from t

Plan hash value: 780537734

-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |       1 |
|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |       1 |
|   2 |   INDEX FULL SCAN| T_IDX1 |      1 |  82033 |    359 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------


14 rows selected.

demo@ORA12C>

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer