BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PORTAL_HIS',
tabname => 'YS_ZY_JZJL',
estimate_percent => 100,
method_opt => 'for all columns size skewonly for columns(KSDM,CYBZ)',
no_invalidate => FALSE,
cascade => TRUE);
END;
/
PORTAL_HIS.YS_ZY_JZJL
alter index PORTAL_HISIDX_YS_ZY_JZJL_CYBZ usable;
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col table_owner for a15
SQL> set line 150
SQL> col table_name for a40
SQL> col column_name for a30
SQL> col index_name for a30
SQL> select table_owner, table_name, column_name, index_name from dba_ind_columns where table_owner = 'PORTAL_HIS' and table_name = 'YS_ZY_JZJL';
TABLE_OWNER TABLE_NAME COLUMN_NAME INDEX_NAME
--------------- ---------------------------------------- ------------------------------ ------------------------------
PORTAL_HIS YS_ZY_JZJL JZHM PK_YS_ZY_JZJL
PORTAL_HIS YS_ZY_JZJL KSDM IDX_YS_ZY_JZJL_KSDM
PORTAL_HIS YS_ZY_JZJL YSDM IDX_YS_ZY_JZJL_YSDM
PORTAL_HIS YS_ZY_JZJL RYRQ IDX_YS_ZY_JZJL_RYRQ
PORTAL_HIS YS_ZY_JZJL CYRQ IDX_YS_ZY_JZJL_CYRQ
PORTAL_HIS YS_ZY_JZJL CYBZ IDX_YS_ZY_JZJL_CYBZ
PORTAL_HIS YS_ZY_JZJL ZLXZ IDX_YS_ZY_JZJL_ZLXZ
PORTAL_HIS YS_ZY_JZJL JZXH IDX_YS_ZY_JZJL_JZXH
8 rows selected.
SQL> select owner,table_name,object_type,stale_stats,num_rows,last_analyzed from dba_tab_statistics where table_name='YS_ZY_JZJL';
OWNER TABLE_NAME OBJECT_TYPE STALE_STA NUM_ROWS LAST_ANALYZED
-------------- ----------------------------------------
PORTAL_HIS YS_ZY_JZJL TABLE
SQL> select count(*),count(distinct KSDM) from PORTAL_HIS.YS_ZY_JZJL;
COUNT(*) COUNT(DISTINCTKSDM)
---------- -------------------
14487 6
SQL> select count(*),count(distinct KSDM),count(distinct CYBZ) from PORTAL_HIS.YS_ZY_JZJL;
COUNT(*) COUNT(DISTINCTKSDM) COUNT(DISTINCTCYBZ)
---------- ------------------- -------------------
14487 6 2
SQL> select KSDM,count(1) from PORTAL_HIS.YS_ZY_JZJL group by KSDM;
KSDM COUNT(1)
------------------------------ ----------
100 461
135 259
8 11278
7 2404
91 77
119 8
6 rows selected.
SQL> select CYBZ,count(1) from PORTAL_HIS.YS_ZY_JZJL group by CYBZ;
CYBZ COUNT(1)
---------- ----------
1 14410
0 77
SQL> select index_name,STATUS from dba_indexes where index_name='IDX_YS_ZY_JZJL_CYBZ';
INDEX_NAME STATUS
------------------------------ ------------------------
IDX_YS_ZY_JZJL_CYBZ UNUSABLE
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PORTAL_HIS',
3 tabname => 'YS_ZY_JZJL',
4 estimate_percent => 100,
5 method_opt => 'for all columns size skewonly for columns(KSDM,CYBZ)',
6 no_invalidate => FALSE,
7 cascade => TRUE);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> set line 150
SQL>
SQL>
SQL> select * from table(dbms_xplan.DISPLAY_CURSOR('2wkpmjj7v2dw3', null, 'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2wkpmjj7v2dw3, child number 0
-------------------------------------
Select V_EMR_YW_JZJL.JZHM, V_EMR_YW_JZJL.BRBH, V_EMR_YW_JZJL.BRXM,
V_EMR_YW_JZJL.BRXB, V_EMR_YW_JZJL.CSRQ, V_EMR_YW_JZJL.BRXZ,
V_EMR_YW_JZJL.KSDM, V_EMR_YW_JZJL.BQDM, V_EMR_YW_JZJL.CWHM,
V_EMR_YW_JZJL.YSDM, V_EMR_YW_JZJL.ZYYS, V_EMR_YW_JZJL.ZRYS,
V_EMR_YW_JZJL.HLJB, V_EMR_YW_JZJL.RYZD, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYQK, V_EMR_YW_JZJL.RYRQ, V_EMR_YW_JZJL.CYRQ as
outdata, V_EMR_YW_JZJL.CYBZ as outflag, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYNL AS BRNL, V_EMR_YW_JZJL.ZKZT , V_EMR_YW_JZJL.ZGQK
, V_EMR_YW_JZJL.ZLXZ as TeamId, V_EMR_YW_JZJL.JLLX AS VisitType,
' ' AS OtherFlag, ' ' AS ChargeTypeName,
0 AS XQBZ, V_EMR_YW_JZJL.BRQK, V_EMR_YW_JZJL.ZYCS,
V_EMR_YW_JZJL.BAHM, V_EMR_YW_JZJL.BRID, 0 AS JRRY, 0 AS QCBZ,
0 AS YGBZ, 0 AS SSQ, 0 AS SSH, 0 as QFBR, 0 AS LCLJ, 0 AS
WTYZ, 0 AS YZYTS, 0 AS YZKBR, 0 AS PatientLevel, '
' AS BRTX, '
Plan hash value: 1733410705
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | | | | |
| 1 | VIEW | V_EMR_YW_JZJL | 52 | | | |
| 2 | SORT UNIQUE | | 52 | 15360 | 15360 |14336 (0)|
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 35 | | | |
|* 5 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 11447 | | | |
| 6 | NESTED LOOPS | | 17 | | | |
| 7 | NESTED LOOPS | | 81M| | | |
| 8 | TABLE ACCESS FULL | BQ_XSEDJ | 7082 | | | |
|* 9 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 11447 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| YS_ZY_JZJL | 1 | | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("YS_ZY_JZJL"."CYBZ"=0)
5 - access("YS_ZY_JZJL"."KSDM"='8')
9 - access("YS_ZY_JZJL"."KSDM"='8')
10 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18))))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
51 rows selected.
SQL> /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2wkpmjj7v2dw3, child number 0
-------------------------------------
Select V_EMR_YW_JZJL.JZHM, V_EMR_YW_JZJL.BRBH, V_EMR_YW_JZJL.BRXM,
V_EMR_YW_JZJL.BRXB, V_EMR_YW_JZJL.CSRQ, V_EMR_YW_JZJL.BRXZ,
V_EMR_YW_JZJL.KSDM, V_EMR_YW_JZJL.BQDM, V_EMR_YW_JZJL.CWHM,
V_EMR_YW_JZJL.YSDM, V_EMR_YW_JZJL.ZYYS, V_EMR_YW_JZJL.ZRYS,
V_EMR_YW_JZJL.HLJB, V_EMR_YW_JZJL.RYZD, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYQK, V_EMR_YW_JZJL.RYRQ, V_EMR_YW_JZJL.CYRQ as
outdata, V_EMR_YW_JZJL.CYBZ as outflag, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYNL AS BRNL, V_EMR_YW_JZJL.ZKZT , V_EMR_YW_JZJL.ZGQK
, V_EMR_YW_JZJL.ZLXZ as TeamId, V_EMR_YW_JZJL.JLLX AS VisitType,
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
' ' AS OtherFlag, ' ' AS ChargeTypeName,
0 AS XQBZ, V_EMR_YW_JZJL.BRQK, V_EMR_YW_JZJL.ZYCS,
V_EMR_YW_JZJL.BAHM, V_EMR_YW_JZJL.BRID, 0 AS JRRY, 0 AS QCBZ,
0 AS YGBZ, 0 AS SSQ, 0 AS SSH, 0 as QFBR, 0 AS LCLJ, 0 AS
WTYZ, 0 AS YZYTS, 0 AS YZKBR, 0 AS PatientLevel, '
' AS BRTX, '
Plan hash value: 1733410705
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | | | | |
| 1 | VIEW | V_EMR_YW_JZJL | 52 | | | |
| 2 | SORT UNIQUE | | 52 | 15360 | 15360 |14336 (0)|
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 35 | | | |
|* 5 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 11447 | | | |
| 6 | NESTED LOOPS | | 17 | | | |
| 7 | NESTED LOOPS | | 81M| | | |
| 8 | TABLE ACCESS FULL | BQ_XSEDJ | 7082 | | | |
|* 9 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 11447 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| YS_ZY_JZJL | 1 | | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("YS_ZY_JZJL"."CYBZ"=0)
5 - access("YS_ZY_JZJL"."KSDM"='8')
9 - access("YS_ZY_JZJL"."KSDM"='8')
10 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18))))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
51 rows selected.
SQL> /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2wkpmjj7v2dw3, child number 0
-------------------------------------
Select V_EMR_YW_JZJL.JZHM, V_EMR_YW_JZJL.BRBH, V_EMR_YW_JZJL.BRXM,
V_EMR_YW_JZJL.BRXB, V_EMR_YW_JZJL.CSRQ, V_EMR_YW_JZJL.BRXZ,
V_EMR_YW_JZJL.KSDM, V_EMR_YW_JZJL.BQDM, V_EMR_YW_JZJL.CWHM,
V_EMR_YW_JZJL.YSDM, V_EMR_YW_JZJL.ZYYS, V_EMR_YW_JZJL.ZRYS,
V_EMR_YW_JZJL.HLJB, V_EMR_YW_JZJL.RYZD, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYQK, V_EMR_YW_JZJL.RYRQ, V_EMR_YW_JZJL.CYRQ as
outdata, V_EMR_YW_JZJL.CYBZ as outflag, V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYNL AS BRNL, V_EMR_YW_JZJL.ZKZT , V_EMR_YW_JZJL.ZGQK
, V_EMR_YW_JZJL.ZLXZ as TeamId, V_EMR_YW_JZJL.JLLX AS VisitType,
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
' ' AS OtherFlag, ' ' AS ChargeTypeName,
0 AS XQBZ, V_EMR_YW_JZJL.BRQK, V_EMR_YW_JZJL.ZYCS,
V_EMR_YW_JZJL.BAHM, V_EMR_YW_JZJL.BRID, 0 AS JRRY, 0 AS QCBZ,
0 AS YGBZ, 0 AS SSQ, 0 AS SSH, 0 as QFBR, 0 AS LCLJ, 0 AS
WTYZ, 0 AS YZYTS, 0 AS YZKBR, 0 AS PatientLevel, '
' AS BRTX, '
Plan hash value: 2971752397
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | | | | |
| 1 | VIEW | V_EMR_YW_JZJL | 64 | | | |
| 2 | SORT UNIQUE | | 64 | 15360 | 15360 |14336 (0)|
| 3 | UNION-ALL | | | | | |
|* 4 | TABLE ACCESS FULL | YS_ZY_JZJL | 43 | | | |
|* 5 | HASH JOIN | | 21 | 883K| 883K| 1264K (0)|
|* 6 | TABLE ACCESS FULL| YS_ZY_JZJL | 43 | | | |
| 7 | TABLE ACCESS FULL| BQ_XSEDJ | 7082 | | | |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."KSDM"='8'))
5 - access("YS_ZY_JZJL"."JZHM"=CAST("MQZYH" AS VARCHAR(18)))
6 - filter(("YS_ZY_JZJL"."CYBZ"=0 AND "YS_ZY_JZJL"."KSDM"='8'))
Note
-----
- dynamic sampling used for this statement (level=2)
- Warning: basic plan statistics not available. These are only collected when:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
47 rows selected.
SQL> set pagesize 0
