البحث : use materialized views

[Star5]

أخوتي العزاء أنا مشترك جديد أتمنى الإستمرار معلكم لانه تخصصي إدارة نظم المعلومات وآخر سنة في الدراسة ولدي سؤالين لم أفهم المطلوب من الإجابة ولكم جزيل الشكر


As a DBA, justify the following statement: “Concurrent execution of transactions is more important when data must be fetched from (slow) disk or when transactions are long, and is less important when data is in memory and transactions are very short.”

Amri textiles are a popular manufacturer in the gulf region. Amri textiles deal with the manufacture and sale of various garments categorizing various types of customers and users. It has a large database. Many complex queries are performed during the daily operations. Users are complaining about the slow response time of the system. As the DBA of the company would you advise to use materialized views to speed up the queries? Explain your answer.

يرجى المساعدة ولكم جزيل الشكر

[airtifa]

query tuning is one of the major DBA tasks

[Mahmoud mohamed gaber]

--MATERIALIZED VIEW : IS ADATABASE OBJECT  THAT CONTAINS THE RESULTS OF QUERY
--MATERIALIZED VIEW: (also known as snapshots in prior releases) to replicate data to
--                   non-master sites in a replication environment and to cache expensive queries in a data warehouse environment.
--NOTE: YOU SHOULD HAVE (CREATE materialize VIEW) privilege
   --------------------------------------------
 --REFRESH TYPE:
    -- COMPLETE >>> truncate MV AND RE-FILL AGAIN
    -- FAST>>>      UPDATED OR NEW INSRT DATA REFRESH
    --FORCE>>>      TRAYING FAST IF CANNOT MAKEING COMPLETE
   --------------
 -- ON (TIME) :
    -- COMMIT     >> REFRESH THE MV WHENE COMMIT ON BASE TABLE WHICH MV BUILD ON IT
    -- DEMAND     >> REFRESH THE DATA WHEN REQUEST
    -- START WITH >> DETERMINE THE TIME OF DATA REFRESHING
   -------------------------------------------
--ex1 USING( COMPLETE & COMMIT):
  --STEP 1:

CREATE  MATERIALIZED VIEW MV_EMP
   REFRESH COMPLETE  -- FAST / FORCE
   ON COMMIT -- DEMAND -- START WITH
   AS
   SELECT E.EMPNO,E.ENAME,E.SAL
   FROM
   EMP E
   WHERE E.DEPTNO = 10;
   ----
   --STEP 2 :

SELECT * FROM MV_EMP;

   ----
   --STEP 3:

UPDATE EMP
   SET SAL = SAL + 100
 WHERE E.DEPTNO = 7788;

   ------
  -- STEP 4:

SELECT * FROM MV_EMP;

  -----
   --STEP 5 :
COMMIT;
   -----
   --STEP 6:

SELECT * FROM MV_EMP;
--------------------------------------------------------------------
--ex2 USING( FAST & DEMAND):
  --STEP 2:
   CREATE MATERIALIZED VIEW MV1_EMP
   REFRESH FAST
   ON DEMAND
   AS
   SELECT E.EMPNO,E.ENAME,E.SAL
   FROM
   EMP E
   WHERE E.DEPTNO = 20;
   ---- NOTE :IT WILL GIVE ERORR BECAUSE WH HAVE TO CREATE FIRST  MV LOG ON THE TABLE
   ----
   --STEP 1 :
   CREATE MATERIALIZED VIEW LOG ON EMP;
   -----
   --STEP 3 :

SELECT * FROM MV1_EMP;

   ----
   --STEP 4:

UPDATE EMP
   SET SAL = SAL + 100
 WHERE E.DEPTNO = 7788;

   ------
    --STEP 5 :
COMMIT;
   ---------
  -- STEP 6:

SELECT * FROM MV1_EMP;-- NO UDATE FOUND WHY >> BECAUSEWE DETRMINE TYPE ON DEMAND
  -----
  --STEP 7 :
  EXECUTE DBMS_MVIEW.REFRESH('MV1_EMP');
   -----
   --STEP 8:

SELECT * FROM MV_EMP;

   -----
   --STEP 9:
   --WE CAN CHICK THE LOG FRON THIS SELECT STATMENT

SELECT * FROM USER_MVIEW_LOGS; -- RETURN MV LOG TABLES
   -- THEN

SELECT * FROM MLOG$EMP;  -- FAST TYPE IS  DEPEND ON THIS TABEL FOR DATA REFRESHING

----------------------------------------------------------------

--ex3 USING( COMPLETE & START WITH):
  --STEP1:

CREATE TABLE TEMP (N NUMBER);

SELECT TO_CHAR (SYSDATE, 'DD-MM-YYY HH:MI:SS'),
       TO_CHAR (SYSDATE + 10 / 86400, 'DD-MM-YYY HH:MI:SS')
  FROM DUAL;

-----------
--STEP 2:
CREATE MATERIALIZED VIEW MV_TMP
   REFRESH COMPLETE
   START WITH (SYSDATE) NEXT (SYSDATE+10/86400) -- HERE WE CREATE A JOB THIS JOB OUTO START AFTER 10 SECOND , SO THE NEXT REFRESH WILL BE AFTER 10 S'
   AS
   SELECT * FROM TEMP;
  --------------------

   --STEP 3:

SELECT * FROM TEMP;

   ------------------
   --STEP 4:

INSERT INTO TEMP
     VALUES (1);

   ---------------
   --STEP 5:
COMMIT;

SELECT * FROM TEMP;

   ------------
   --STEP 6:

SELECT * FROM MV_TMP;-- NO DATA JUST WAITE 10 S' AND THEN RUN SELECT AGAIN