从 Oracle View 获取数据并在 QueryTable 中加载

Question

我创建了一个400+列的视图&从其中的4个不同的表(最多15,000行)中获取数据。我已经单独指定了所有列的名称。

,

CREATE OR REPLACE FORCE VIEW "MAIN"."VIEW_NAME" 
   ("col1", "col2".... ,"col400") 
AS 
SELECT "col1", "col2".... ,"col400" 
FROM tab1, tab2, tab3, tab4 
where col=col1, col=col2, col=col3, col=col4)

在Excel Macro的QueryTable中使用SELECT语句从这个视图中获取数据需要更多的时间。关于优化宏/调优SQL语句性能的任何输入

Answer 1

1. 查看视图底层查询的解释计划
2. 正确连接表。不要做笛卡尔连接。
3. 确保表具有所需的约束。如果有适当的主键和外键约束，可以避免在子表上进行不必要的表扫描。

例如,

SQL> column constraint_name format a20
SQL> column constraint_type format a20
SQL> column table_name format a15
SQL> column r_constraint_name format a20
SQL> SELECT constraint_name,
  2    constraint_type,
  3    table_name,
  4    r_constraint_name
  5  FROM all_constraints
  6  WHERE owner     ='SCOTT'
  7  AND table_name IN ('EMP', 'DEPT')
  8  and constraint_type = 'R';
CONSTRAINT_NAME      CONSTRAINT_TYPE      TABLE_NAME      R_CONSTRAINT_NAME
-------------------- -------------------- --------------- -----------------
FK_DEPTNO            R                    EMP             PK_DEPT
SQL>

因此，我们应该看到表扫描只在EMP表上，而不是在DEPT表上:

SQL> EXPLAIN PLAN FOR
  2  SELECT e.empno, d.deptno
  3  FROM emp e, dept d
  4  WHERE e.deptno = d.deptno
  5  /
Explained.
SQL>
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 |    98 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter("E"."DEPTNO" IS NOT NULL)
13 rows selected.

根据过滤器谓词创建所需的索引。但是，请记住基数估计值将产生很大的差异。

索引并不总是好的，全表扫描也不总是坏的。例如,

SQL> EXPLAIN PLAN FOR
  2  SELECT e.empno, d.deptno
  3  FROM emp e, dept d
  4  WHERE e.deptno = d.deptno
  5  AND e.empno in (7369, 7699)
  6  /
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2355049923
---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    14 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    14 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("E"."DEPTNO" IS NOT NULL)
   3 - access("E"."EMPNO"=7369 OR "E"."EMPNO"=7699)
16 rows selected.

查看如何创建和显示Explain Plan