explain查询在oracledb中不起作用

Question

我在MySQL中使用以下查询，它运行良好：

explain SELECT COUNT(userid) FROM tableone where userid='abc'

我在Oracle中尝试了同样的操作，但我得到了以下错误：

SQL Error [905] [42000]: ORA-00905: missing keyword
 ORA-00905: missing keyword

当我在mysql中执行解释查询时，它的结果类型、possible_keys、key、key_len、ref等……我如何从oracle 中获得结果

Answer 1

explain SELECT COUNT(userid) FROM tableone where userid='abc'

这在Oracle中是语法错误的。正确的语法是：

EXPLAIN PLAN FOR sql_statement;

请参阅如何在Oracle中创建和显示解释计划。

例如，

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.

或者，您也可以在SQL*Plus:中实现相同的功能

SQL> set autot on explain
SQL> SELECT empno FROM emp;
     EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    56 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |    56 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------