1.Which two statements are true about space-saving features in an Oracle Database?
A.An index created with the UNUSABLE attribute has no segment.
B.Private Temporary Tables(PTTs) store metadata in memory only.
C.Private Temporary Tables(PTTs) when used, are always dropped at the next commit statement.
D.An index that is altered to be UNUSABLE will retain its segment
E. A table that is truncated will always have all of its extents removed

针对答案A:In case of UNUSABLE index,the index segment gets dropped.

SQL> CREATE TABLE sales
  2  (
  3    id INT,
  4    order_id INT,
  5    product_code VARCHAR2(3),
  6    amount INT,
  7    date_of_sale DATE
  8  );

Table created.

SQL> INSERT INTO sales
  2  SELECT ROWNUM,FLOOR(DBMS_RANDOM.VALUE(90,9000)),
  3  DBMS_RANDOM.STRING('U',3),FLOOR(DBMS_RANDOM.VALUE(90,9000)),
  4  TRUNC(SYSDATE) - FLOOR(DBMS_RANDOM.VALUE(10,900))
  5  FROM dual
  6  CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> CREATE UNIQUE INDEX idx_sales_id ON sales(id);

Index created.

SQL> ANALYZE INDEX idx_sales_id VALIDATE STRUCTURE;

Index analyzed.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

SEGMENT_NAME   SEGMENT_TYPE    TABLESPACE_NAME  BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID     INDEX            SYSTEM         256         17        65536     1048576

SQL> SET AUTOTRACE ON EXPLAIN

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    51 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SALES        |     1 |    51 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_SALES_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)   

SQL> ALTER INDEX idx_sales_id UNUSABLE;

Index altered.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

no rows selected

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 781590677

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   337 | 17187 |   115   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| SALES |   337 | 17187 |   115   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=1000)

SQL> ALTER INDEX idx_sales_id REBUILD;

Index altered.

SQL> SELECT segment_name,segment_type,tablespace_name,blocks,extents,initial_extent,next_extent FROM user_segments WHERE segment_name = 'IDX_SALES_ID';

SEGMENT_NAME   SEGMENT_TYPE    TABLESPACE_NAME  BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
---------------- ------------------ ------------------------------ ---------- ---------- ---
IDX_SALES_ID     INDEX            SYSTEM         256         17        65536     1048576

SQL> SELECT * FROM sales WHERE id = 1000;

        ID   ORDER_ID PRO     AMOUNT DATE_OF_S
---------- ---------- --- ---------- ---------
      1000       8794 ZQR       2282 22-JAN-18

Execution Plan
----------------------------------------------------------
Plan hash value: 1439439247

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    51 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| SALES        |     1 |    51 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_SALES_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1000)

A．index状态为unusable，则相应的segment将会被删除
B.私有临时表是数据库的临时对象，私有临时表在事务或者会话结束后被自动drop掉。私有临时表定义在内存中，只能被创建他的会话看见该表的定义和数据。

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle