not in (null) 条件下 count(*)与*的执行时间？

Question

有没有高手知道为什么以下两个查询的时间差很多？
oracle打不开trace信息(set autotrace on)，所以看不到执行计划。SQL> select count(id) from tps_transaction where id not in (null); COUNT(ID)
----------
         0Elapsed: 00:00:01.32
SQL> select * from tps_transaction where id not in (null);no rows selectedElapsed: 00:01:46.25

Answer 1

最好给出执行计划,可以用这种方法看执行计划:
EXPLAIN PLAN FOR 
 SELECT * FROM select count(id) from tps_transaction where id not in (null); SELECT plan_table_output
  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

Answer 2

貌似也不可以耶！ 我用的oracle 10gSQL> explain plan for select * from tps_transaction where id not in (null); 
explain plan for select * from tps_transaction where id not in (null)
                               *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4000 bytes of shared memory ("shared pool","select con#,type#,condlength...","sql area","seg:kggfaAllocSeg")
Elapsed: 00:00:01.43

Answer 3

换了个server, set autotrace on工作了，下面是execution plan.为什么第二个要“TABLE ACCESS FULL”？SQL> select count(id) from tps_transaction where id not in (null); COUNT(ID)
----------
         0Elapsed: 00:00:01.25Execution Plan
----------------------------------------------------------
Plan hash value: 589267130-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                       |     1 |     6 |    63   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                       |     1 |     6 |            |          |
|*  2 |   INDEX FAST FULL SCAN| TPS_TRANSACTION_PK_ID |   102K|   602K|    63   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - filter("ID"<>TO_NUMBER(NULL))
   
   
   
SQL> select * from tps_transaction where id not in (null);no rows selectedElapsed: 00:00:51.31Execution Plan
----------------------------------------------------------
Plan hash value: 1382894494-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   102K|  9446K| 27940   (1)| 00:05:36 |
|*  1 |  TABLE ACCESS FULL| TPS_TRANSACTION |   102K|  9446K| 27940   (1)| 00:05:36 |
-------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - filter("ID"<>TO_NUMBER(NULL))