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

来源：互联网发布：丽晶软件股份编辑：程序博客网时间：2024/05/16 10:12

/*
我们知道当数据量大时，如果进行分页查询，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 ASSELECT 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