mysql - 解释查询在 oracle 数据库中不起作用

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;

参见 How to create and display explain plan in 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 |
--------------------------------------------------------------------------