Sample Text

Views vs Materialized Views in R12

Like its predecessor the view, materialized views allow you to store the definition of a query in the database.
TableViewMaterialized View




select * from T ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create view v
as
select *
from   t ;

select * from V ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
create materialized view mv
as
select *
from   t ;

select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
Unlike views, however, materialized views also store the results of the query in the database. In the following queries note how the rowid's for the table and the view are identical, indicating the view returns the exact same data stored in the table. The rowids of the materialized view, on the other hand, differ from those of the table. This indicates the materialized view is returning a physically separate copy of the table data.
TableViewMaterialized View
select rowid
from T
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from V
order by rowid ;

ROWID
------------------
AAAgY9AAEAAAAVfAAA
AAAgY9AAEAAAAVfAAB
AAAgY9AAEAAAAVfAAC
AAAgY9AAEAAAAVfAAD
 
select rowid
from MV
order by rowid ;

ROWID
------------------
AAAgZFAAEAAADyEAAA
AAAgZFAAEAAADyEAAB
AAAgZFAAEAAADyEAAC
AAAgZFAAEAAADyEAAD
 
The difference between views and materialized views becomes even more evident than this when table data is updated.
TableViewMaterialized View
update t set val = upper(val);
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 a
         2 b
         3 c
         4
 
Note how, after the update, the view data matches the table data but the materialized view data does not. Data in materialized views must be refreshed to keep it synchronized with its base table. Refreshing can either be done manually, as below, or automatically by Oracle in some cases.
TableViewMaterialized View
execute dbms_mview.refresh( 'MV' );
 
select * from T ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from V ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
select * from MV ;

       KEY VAL
---------- -----
         1 A
         2 B
         3 C
         4
 
Now that the materialized view has been refreshed its data matches that of its base table.
Ritesh Jain No comments:

Use extract function and xpath function to retrieve xml element data 

SQL> CREATE TABLE R22106(
       id  NUMBER PRIMARY KEY,
       emps XMLType NOT NULL
                         );

Table created.


SQL> INSERT INTO R22106 VALUES (1, xmltype('<?xml version="1.0" standalone="no"
 ?>
    <emps>
       <emp>
            <home_address>address 1</home_address>
        </emp>
    </emps>')
    );

1 row created.


SQL> select extract(emps, '/emps/emp/home_address/text()' ) "Xml Element Value"
    from R22106;

  



Xml Element Value

address 1

Ritesh Jain No comments:

Query to find Suppliers , do not have contact directory

To gather phone, fax, and email in Address Book we can use this query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Author : $ Ritesh Jain
-- -- Creation Date : 25 October 2017 -- -- Description : Sql query to get supplier, who does not have -- Contact directory Information -------------------------------------------------------------------------- SELECT ASS.VENDOR_NAME "SUPPLIER NAME", ASS.SEGMENT1 "VENDOR NUMBER", ASS.VENDOR_ID "VENDOR ID", 'NOT AVAILABLE' AS "CONTACT DIRECTORY" FROM AP_SUPPLIERS ASS, HZ_PARTIES HP WHERE 1=1 AND ASS.PARTY_ID=HP.PARTY_ID AND NOT EXISTS(SELECT * FROM HZ_RELATIONSHIPS HR WHERE HR.SUBJECT_ID =ASS.PARTY_ID AND HR.SUBJECT_TYPE ='ORGANIZATION' AND HR.RELATIONSHIP_CODE ='CONTACT' AND HR.RELATIONSHIP_TYPE ='CONTACT' AND HR.STATUS ='A' AND HR.object_table_name = 'HZ_PARTIES' ) ORDER BY ASS.CREATION_DATE DESC;

Posted By Ritesh Jain
Ritesh Jain No comments:

Query to find supplier's contact information

To gather phone, fax, and email in Address Book we can use this query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
--email
SELECT  HCP.EMAIL_ADDRESS
FROM    HZ_PARTY_SITES HPS,
        HZ_CONTACT_POINTS HCP,
        AP_SUPPLIERS ASS
WHERE   1=1
AND     ASS.VENDOR_NAME=:VENDOR_NAME
AND     HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND     HPS.PARTY_ID=ASS.PARTY_ID
AND     HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND     HCP.CONTACT_POINT_TYPE='EMAIL';
 
--phone
SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE
FROM   HZ_CONTACT_POINTS HCP,
       HZ_PARTY_SITES HPS,
       AP_SUPPLIERS ASS
WHERE  1=1
AND    ASS.VENDOR_NAME=:VENDOR_NAME
AND    HPS.PARTY_ID=ASS.PARTY_ID
AND    HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND    HCP.PRIMARY_FLAG='Y'
AND    HCP_OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND    HCP.STATUS='A'
AND    HCP.CONTACT_POINT_TYPE='PHONE'
AND    HCP.PHONE_LINE_TYPE='GEN';
 
--fax
SELECT ROWNUM NO, HCP.PHONE_AREA_CODE||' '||HCP.PHONE_NUMBER PHONE
FROM   HZ_CONTACT_POINTS HCP,
       HZ_PARTY_SITES HPS,
       AP_SUPPLIERS ASS
WHERE  1=1
AND    ASS.VENDOR_NAME=:VENDOR_NAME
AND    HPS.PARTY_ID=ASS.PARTY_ID
AND    HCP.OWNER_TABLE_NAME='HZ_PARTY_SITES'
AND    HCP.OWNER_TABLE_ID=HPS.PARTY_SITE_ID
AND    HCP.STATUS='A'
AND    HCP.CONTACT_POINT_TYPE='PHONE'
AND    HCP.PHONE_LINE_TYPE='FAX';
To gather phone, fax, and email in Contact Directory we can use this query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
--email
SELECT  HCP.EMAIL_ADDRESS
FROM    HZ_RELATIONSHIPS HR
       ,HZ_CONTACT_POINTS HCP
       ,AP_SUPPLIERS ASS
WHERE  1=1
AND    ASS.VENDOR_NAME=:VENDOR_NAME
AND    HR.SUBJECT_TYPE='ORGANIZATION'
AND    HR.RELATIONSHIP_CODE='CONTACT'
AND    HR.STATUS='A'
AND    HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND    HR.SUBJECT_ID=ASS.PARTY_ID
AND    HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND    HCP.PRIMARY_FLAG='Y'
AND    HCP.STATUS='A'
AND    HCP.CONTACT_POINT_TYPE='EMAIL';
 
--phone
SELECT  HCP.PHONE_AREA_CODE||HCP.PHONE_NUMBER
FROM    HZ_RELATIONSHIPS HR
       ,HZ_CONTACT_POINTS HCP
       ,AP_SUPPLIERS ASS
WHERE  1=1
AND    ASS.VENDOR_NAME=:VENDOR_NAME
AND    HR.SUBJECT_TYPE='ORGANIZATION'
AND    HR.RELATIONSHIP_CODE='CONTACT'
AND    HR.STATUS='A'
AND    HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND    HR.SUBJECT_ID=ASS.PARTY_ID
AND    HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND    HCP.PRIMARY_FLAG='Y'
AND    HCP.STATUS='A'
AND    HCP.CONTACT_POINT_TYPE='PHONE'
AND    HCP.PHONE_LINE_TYPE='GEN';
 
--fax
select hcp.phone_area_code||hcp.phone_number
from    hz_relationships hr
       ,hz_contact_points hcp
       ,AP_SUPPLIERS ASS
where  1=1
AND    ASS.VENDOR_NAME=:VENDOR_NAME
and    hr.SUBJECT_TYPE='ORGANIZATION'
and    hr.RELATIONSHIP_CODE='CONTACT'
and    hr.STATUS='A'
AND    hcp.owner_table_name='HZ_PARTIES'
and    hr.SUBJECT_ID=ASS.PARTY_ID
and    hcp.owner_table_id=hr.party_ID
and    hcp.STATUS='A'
and    hcp.CONTACT_POINT_TYPE='PHONE'
and    hcp.PHONE_LINE_TYPE='FAX';
Hope it help! ðŸ™‚
Ritesh Jain No comments:
Subscribe to: Posts (Atom)

Contact Form

Name

Email *

Message *