Đầu vào đối số: ownname - giản đồ để phân tích (NULL có nghĩa là sơ đồ hiện tại) estimate_percent - Tỷ lệ phần trăm hàng ước tính (NULL có nghĩa là tính toán) Phạm vi hợp lệ là ) [. block_sample - có hay không sử dụng lấy mẫu khối ngẫu nhiên thay vì lấy mẫu hàng ngẫu nhiên. Lấy mẫu khối ngẫu nhiên là hiệu quả hơn, nhưng nếu dữ liệu | ROBO Books Monograph Data Warehousing and Oracle8i In the first example lets do some summary work against the DBA_ views so we can query about total space total extents etc. without having to place the code into our reports. This will actually be a materialized view based on two example tables TaB_EXAMPLE1 and TAB_EXAMPLE1 that are based on the underlying DBA_ views DBA_TABLES and DBA_EXTENTS. CREATE MATERIALIZED VIEW table_extents TABLESPACE tools STORAGE INITIAL 1M NEXT 1M PCTINCREASE 0 NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE 1 24 AS SELECT owner table_name tablespace_name count extents sum bytes FROM tab_example1 a tab_example2 b WHERE SYSTEM AND AND AND TABLE GROUP BY What does a materialized view buy us as far as performance Let s look at the explain plans for a query on a regular view and then one on the materialized view we just created. First create an identical normal view CREATE VIEW test_view AS SELECT owner table_name tablespace_name count extents sum bytes FROM tab_example1 a tab_example2 b WHERE SYSTEM AND AND AND TABLE GROUP BY Now let s set autotrace on with the explain option and see what happens when we select against each of these objects. Page 71 COPYRIGHT 2003 Rampant TechPress. All Rights Reserved. ROBO Books Monograph Data Warehousing and Oracle8i SQL set autotrace on explain SQL select from test view 2 where extents 1 OWNER TABLE NAME TABLESPACE_NAME EXTENTS BYTES SYS ACCESS SYSTEM 8 536576 SYS ARGUMENT SYSTEM 10 1191936 SYS COM SYSTEM 7 368640 SYS CON SYSTEM 3 45056 SYS DEPENDENCY SYSTEM 7 352256 SYS ERROR SYSTEM 2 24576 SYS EXTENT_TO_OBJECT TAB SYSTEM 3 45056 SYS EXT_TO_OBJ SYSTEM 4 86016
