Skip to Main Content
  • Questions

  • will 'where 1=1' make execution plan full table scan

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ezio.

Asked: November 27, 2015 - 6:02 am UTC

Last updated: November 27, 2015 - 6:16 am UTC

Version: 9i

Viewed 1000+ times

You Asked

Hi tom:
In my database there is a SQL: SELECT MAX("A1"."PRINTTIME") FROM FWFT."FWCATNS_LABEL_UNIQUE" "A1" WHERE 1=1
the execution plan is 'TABLE ACCESS FULL'
When i delete the where 1=1 ,the execution plan is 'index full scan(min/max)'
I had analyzed the table it is still the same

So the 'where 1=1' will make the oracle goes the wrong execution plan?

and Connor said...

It is possible that this is due to the version being 9i (and hence the optimizer not being as feature-rich as more recent versions).

For example, in 12c, the presence of 1=1 does not make any difference

SQL> create table T ( x int not null, y date not null) ;

Table created.

SQL>
SQL> insert /*+ APPEND */ into T
  2  select rownum, sysdate + rownum/1000
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create index IX on T ( y ) ;

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select max(y) from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1223533863

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |     8 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX   |     1 |     8 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

SQL>
SQL> select max(y) from t where 1=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1223533863

-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     8 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |      |     1 |     8 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IX   |     1 |     8 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


Just out of interest, can you repeat the script I've done above on your database and post the results.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the situation on my database

ezio, November 27, 2015 - 9:58 am UTC

Here is the result on my database,it seems that it is still goes the full table scan.
SQL> create table T ( x int not null, y date not null) ;

Table created.



SQL> insert /*+ APPEND */ into T
2 select rownum, sysdate + rownum/1000
3 from dual
4 connect by level <= 100000;


100000 rows created.

SQL> SQL> commit
2 ;

Commit complete.

SQL> create index IX on T ( y ) ;

Index created.

SQL> set autotrace traceonly explain
SQL> select max(y) from t;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN (MIN/MAX)) OF 'IX' (NON-UNIQUE)



SQL> select max(y) from t where 1=1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'

To: ezio | may be CBO no kicking in ?

Rajeshwaran, Jeyabal, November 27, 2015 - 10:21 am UTC

0 SELECT STATEMENT Optimizer=CHOOSE

Can we have stats gathering on table/index after the data load, before running queries ?

just run this " exec dbms_stats.gather_table_stats(user,'T',cascade=>true); " after the insert is done.

Connor, is on 12c database and stats gathering done as part of Direct path loads, but that is not the case with 9i database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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