分页语句去掉distinct由hash改为nl

最新推荐文章于 2024-12-13 08:08:06 发布

原创最新推荐文章于 2024-12-13 08:08:06 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

改写优化专栏收录该内容

28 篇文章

订阅专栏

/*
我们知道当数据量大时，如果进行分页查询，plan最好走nl。
该环境是根据原sql模拟的案例.
因为构建的环境数据量不大，在执行速度上看不出来，大家只看plan就可以了
*/

DROP TABLE t_objects PURGE;
DROP TABLE t_columns PURGE;
DROP TABLE t_tables PURGE;
DROP TABLE t_users PURGE;

CREATE TABLE t_objects AS SELECT t2.user_id,t1.* FROM dba_objects t1 INNER JOIN All_Users t2 ON t2.username = t1.OWNER;
CREATE TABLE t_columns AS
SELECT b.object_id,
       tc.owner,
       tc.table_name,
       tc.column_name,
       tc.data_type,
       tc.data_type_mod,
       tc.data_type_owner,
       tc.data_length
  FROM all_tab_columns tc
 INNER JOIN t_objects b
    ON (b.owner = tc.owner AND b.object_name = tc.table_name);
CREATE TABLE t_tables AS SELECT * FROM dba_tables;
CREATE TABLE t_users AS SELECT * FROM all_users;

CREATE INDEX idx_t_tables ON t_tables(owner,table_name);
CREATE INDEX idx_t_columns ON t_columns(object_id,owner,table_name);
CREATE INDEX idx_t_users ON t_users(user_id,username);
CREATE INDEX idx_t_objects ON t_objects(created DESC,user_id,object_id);

原语句如下

EXPLAIN PLAN FOR SELECT *
  FROM (SELECT a.*, rownum rn
          FROM (SELECT DISTINCT o.owner,
                                o.object_name,
                                o.object_type,
                                o.created,
                                o.status
                  FROM t_objects o
                 INNER JOIN t_columns tc
                    ON (tc.object_id = o.object_id)
                 INNER JOIN t_tables t
                    ON (t.owner = tc.owner AND t.table_name = tc.table_name)
                  LEFT JOIN t_users tu
                    ON (tu.user_id = o.user_id)
                 WHERE tu.username IN ('HR', 'SCOTT', 'OE','SYS')
                 ORDER BY o.created DESC) a
         WHERE rownum <= 15)
 WHERE rn > 0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3766376180
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes |TempSpc| Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |    15 |  1815 |       |  1
|*  1 |  VIEW                       |               |    15 |  1815 |       |  1
|*  2 |   COUNT STOPKEY             |               |       |       |       |
|   3 |    VIEW                     |               |  3622 |   382K|       |  1
|*  4 |     SORT UNIQUE STOPKEY     |               |  3622 |   866K|   944K|
|*  5 |      HASH JOIN              |               |  3622 |   866K|       |
|*  6 |       INDEX FULL SCAN       | IDX_T_USERS   |     4 |   120 |       |
|*  7 |       HASH JOIN             |               | 11772 |  2471K|  1072K|
|*  8 |        HASH JOIN            |               | 11772 |   931K|       |
|   9 |         INDEX FAST FULL SCAN| IDX_T_TABLES  |  2702 | 91868 |       |
|  10 |         INDEX FAST FULL SCAN| IDX_T_COLUMNS | 94178 |  4322K|       |
|  11 |        TABLE ACCESS FULL    | T_OBJECTS     | 35114 |  4594K|       |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   1 - filter("RN">0)
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   5 - access("TU"."USER_ID"="O"."USER_ID")
   6 - filter("TU"."USERNAME"='HR' OR "TU"."USERNAME"='OE' OR "TU"."USERNAME"='S
              "TU"."USERNAME"='SYS')
   7 - access("TC"."OBJECT_ID"="O"."OBJECT_ID")
   8 - access("T"."OWNER"="TC"."OWNER" AND "T"."TABLE_NAME"="TC"."TABLE_NAME")
Note
-----
   - dynamic sampling used for this statement (level=2)
34 rows selected

以上这个查询仅取了t_objects中的列，而且包含了唯一列o.owner,o.object_name，这样我们就可以改写为exists语句并去掉distinct。

去掉distinct后就可以走nl了

EXPLAIN PLAN SELECT *
  FROM (SELECT a.*, rownum rn
          FROM (SELECT /*+ index(o,idx_t_objects) leading(o) */
                 o.rowid AS rid,
                 o.owner,
                 o.object_name,
                 o.object_type,
                 o.created,
                 o.status
                  FROM t_objects o
                 WHERE EXISTS
                 (SELECT /*+ nl_sj qb_name(@inner) */
                         NULL
                          FROM t_users tu
                         WHERE (tu.user_id = o.user_id)
                           AND tu.username IN ('HR', 'SCOTT', 'OE', 'SYS'))
                   AND EXISTS (SELECT /*+ nl_sj use_nl(tc,t) */
                         NULL
                          FROM t_columns tc
                         INNER JOIN t_tables t
                            ON (t.owner = tc.owner AND
                               t.table_name = tc.table_name)
                         WHERE tc.object_id = o.object_id)
                 ORDER BY o.created DESC) a
         WHERE rownum <= 15) b
 WHERE rn > 0;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4125912022
--------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |    15 |  1995 |  1038K
|*  1 |  VIEW                           |               |    15 |  1995 |  1038K
|*  2 |   COUNT STOPKEY                 |               |       |       |
|   3 |    VIEW                         |               |  3696 |   433K|  1038K
|   4 |     NESTED LOOPS SEMI           |               |   540 | 95040 |   501
|   5 |      TABLE ACCESS BY INDEX ROWID| T_OBJECTS     |  1756 |   250K|    21
|*  6 |       INDEX FULL SCAN           | IDX_T_OBJECTS |   143 |       |    15
|   7 |        NESTED LOOPS             |               |   942 | 76302 |   946
|*  8 |         INDEX RANGE SCAN        | IDX_T_COLUMNS |   942 | 44274 |     3
|*  9 |         INDEX RANGE SCAN        | IDX_T_TABLES  |     1 |    34 |     1
|  10 |      INLIST ITERATOR            |               |       |       |
|* 11 |       INDEX RANGE SCAN          | IDX_T_USERS   |     1 |    30 |     1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
   1 - filter("RN">0)
   2 - filter(ROWNUM<=15)
   6 - filter( EXISTS (SELECT /*+ NL_SJ USE_NL ("T") USE_NL ("TC") */ 0 FROM "T_
              "T","T_COLUMNS" "TC" WHERE "TC"."OBJECT_ID"=:B1 AND "T"."TABLE_NAM
              AND "T"."OWNER"="TC"."OWNER"))
   8 - access("TC"."OBJECT_ID"=:B1)
   9 - access("T"."OWNER"="TC"."OWNER" AND "T"."TABLE_NAME"="TC"."TABLE_NAME")
  11 - access("TU"."USER_ID"="SYS_ALIAS_1"."USER_ID" AND ("TU"."USERNAME"='HR' O
              "TU"."USERNAME"='OE' OR "TU"."USERNAME"='SCOTT' OR "TU"."USERNAME"
Note
-----
   - dynamic sampling used for this statement (level=2)
35 rows selected