【MVS】Oracle IM使用示例

使用Oracle IM进行表查询示例操作

Before completing this example, the IM column store must be enabled for the database.

1. Log in to the database as an administrator, and then query the customers table as follows:

   SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960;

    
    

2. Display the execution plan for the query:

   SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS')); SQL_ID frgk9dbaftmm9, child number 0 ------------------------------------- SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960 Plan hash value: 2008213504 ------------------------------------------------------------------------------- | Id| Operation | Name |Starts|E-Rows|A-Rows| A-Time |Buffers| ------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1| | 6 |00:00:00.01 | 1523| |* 1| TABLE ACCESS FULL| CUSTOMERS | 1| 6 | 6 |00:00:00.01 | 1523| ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%'))

    
    

3. Enable the sh.customers table for population in the IM column store:

   ALTER TABLE sh.customers INMEMORY;

    
    

   The preceding statement uses the default priority of NONE. A full scan is required to populate objects with no priority.

4. To determine whether data from the sh.customers table has been populated in the IM column store, execute the following query (sample output included):

   SELECT SEGMENT_NAME, POPULATE_STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; no rows selected

    
    

   In this case, no segments are populated in the IM column store because the sh.customers table has not yet been scanned.

5. Query sh.customers using the same statement as in Step 1:

   SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960;

    
    

6. Querying the cursor shows that the database performed a full scan and accessed the IM column store:

   SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS')); SQL_ID frgk9dbaftmm9, child number 0 ------------------------------------- SELECT cust_id, cust_last_name, cust_first_name FROM sh.customers WHERE cust_city = 'Hyderabad' AND cust_income_level LIKE 'C%' AND cust_year_of_birth > 1960 Plan hash value: 2008213504 --------------------------------------------------------------------------------- | Id| Operation | Name |Starts|E-Rows|A-Rows|A-Time|Buffers| --------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 1| | 6 |00:00:00.02| 1523 | |* 1| TABLE ACCESS INMEMORY FULL| CUSTOMERS | 1| 6| 6 |00:00:00.02| 1523 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - inmemory(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%')) filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND "CUST_INCOME_LEVEL" LIKE 'C%'))

    
    

7. Query V$IM_SEGMENTS again (sample output included):

   COL SEGMENT_NAME FORMAT a20 SELECT SEGMENT_NAME, POPULATE_STATUS FROM V$IM_SEGMENTS WHERE SEGMENT_NAME = 'CUSTOMERS'; SEGMENT_NAME POPULATE_STATUS -------------------- --------------- CUSTOMERS COMPLETED

    
    

   The value COMPLETED in POPULATE_STATUS means that the table is populated in the IM column store.

8. The DBA_FEATURE_USAGE_STATISTICS view confirms that the database used the IM column store to retrieve the results:

   COL NAME FORMAT a25 SELECT ul.NAME, ul.DETECTED_USAGES FROM DBA_FEATURE_USAGE_STATISTICS ul WHERE ul.VERSION= (SELECT MAX(u2.VERSION) FROM DBA_FEATURE_USAGE_STATISTICS u2 WHERE u2.NAME = ul.NAME AND ul.NAME LIKE '%Column Store%'); NAME DETECTED_USAGES ------------------------- --------------- In-Memory Column Store 1