SET ECHO ON

ALTER SYSTEM FLUSH SHARED_POOL;
pause
ALTER SYSTEM FLUSH BUFFER_CACHE;
pause

DROP TABLE my_indexes;
pause
DROP TABLE my_tables;
pause

CREATE TABLE my_tables AS
SELECT dba_tables.*
  FROM dba_tables;
pause

CREATE TABLE my_indexes AS
SELECT dba_indexes.*
  FROM dba_tables, dba_indexes
 WHERE dba_tables.owner = dba_indexes.table_owner
   AND dba_tables.table_name = dba_indexes.table_name;
pause

SELECT count(*)
  FROM my_tables;
pause

SELECT count(*)
  FROM my_indexes;
pause

SELECT count(*)
  FROM my_indexes
 WHERE index_type = 'BITMAP';
pause

select   distinct index_type,
                  count(*)
    from my_indexes
group by index_type
order by 2;
pause

VARIABLE index_type VARCHAR2(27);
pause

EXEC :index_type := 'BITMAP';
pause

ALTER SESSION SET statistics_level=ALL;
pause
SET AUTOTRACE on statistics
pause
SET LINESIZE 160
pause
SET TAB off
pause
SET PAGESIZE 1000

COLUMN owner FORMAT a30
COLUMN table_name FORMAT a40
COLUMN tablespace_name FORMAT a20
pause

/* First execution */
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Second execution: flush the shared pool and buffer cache*/
pause

ALTER SYSTEM FLUSH SHARED_POOL;
pause
ALTER SYSTEM FLUSH BUFFER_CACHE;
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Third execution: flush the buffer cache */
pause

ALTER SYSTEM FLUSH BUFFER_CACHE;
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Fourth execution */
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

/* Create indexes and collect statistics */
pause

ALTER TABLE my_tables
ADD (CONSTRAINT my_tables_pk PRIMARY KEY (owner, table_name));
pause

ALTER TABLE my_indexes
ADD (CONSTRAINT my_indexes_pk PRIMARY KEY (owner, index_name));
pause

ALTER TABLE my_indexes
ADD (CONSTRAINT my_indexes_fk1 FOREIGN KEY (table_owner, table_name) REFERENCES my_tables);
pause

CREATE INDEX my_indexes_i1 ON my_indexes (index_type);
pause

CREATE INDEX my_indexes_fk1 ON my_indexes (table_owner, table_name);
pause

EXEC DBMS_STATS.gather_table_stats(ownname=>'IFERNANDEZ',tabname=>'MY_TABLES');
pause
EXEC DBMS_STATS.gather_table_stats(ownname=>'IFERNANDEZ',tabname=>'MY_INDEXES');
pause
EXEC DBMS_STATS.gather_index_stats(ownname=>'IFERNANDEZ',indname=>'MY_TABLES_PK');
pause
EXEC DBMS_STATS.gather_index_stats(ownname=>'IFERNANDEZ',indname=>'MY_INDEXES_I1');
pause
EXEC DBMS_STATS.gather_index_stats(ownname=>'IFERNANDEZ',indname=>'MY_INDEXES_FK1');
pause

COLUMN table_name format a20
COLUMN index_name format a20
COLUMN column_name format a20
pause

SELECT table_name,
       index_name
  FROM user_indexes
 WHERE table_name in ('MY_TABLES', 'MY_INDEXES');
pause

SELECT table_name,
       index_name,
       column_name,
       column_position
  FROM user_ind_columns
 WHERE table_name in ('MY_TABLES', 'MY_INDEXES');
pause

EXEC :index_type := 'BITMAP';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Repeat the query again */
pause
/
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

/* get recommendations from SQL Access Advisor */
pause

VARIABLE tuning_task VARCHAR2(32);
pause
EXEC :tuning_task := dbms_sqltune.create_tuning_task (sql_id => '&sqlID');
pause

EXEC dbms_sqltune.execute_tuning_task(task_name => :tuning_task);
pause

SET LONG 100000;
SET PAGESIZE 1000
SET LINESIZE 160
COLUMN recommendations FORMAT a160
pause

SELECT DBMS_SQLTUNE.report_tuning_task (:tuning_task) AS recommendations
  FROM DUAL;
pause

/* Use hints */
pause

EXEC :index_type := 'BITMAP';
pause

SELECT          /*+ INDEX(MY_INDEXES (INDEX_TYPE))
                    INDEX(MY_TABLES (OWNER TABLE_NAME))
                    LEADING(MY_INDEXES MY_TABLES)
                    USE_NL(MY_TABLES)
                */
       DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Repeat the query again */
/
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

/* materialized views in conjunction with clusters */
pause

DROP CLUSTER my_cluster;
pause
DROP MATERIALIZED VIEW my_mv;
pause

CREATE CLUSTER my_cluster (index_type VARCHAR2(27))
SIZE 8192 HASHKEYS 5;
pause

CREATE MATERIALIZED VIEW LOG ON my_tables WITH ROWID;
pause

CREATE MATERIALIZED VIEW LOG ON my_indexes WITH ROWID;
pause

CREATE MATERIALIZED VIEW my_mv
CLUSTER my_cluster (index_type)
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT t.ROWID AS table_rowid,
       t.owner AS table_owner,
       t.table_name,
       t.tablespace_name,
       i.ROWID AS index_rowid,
       i.index_type
  FROM my_tables t,
       my_indexes i
 WHERE t.owner = i.table_owner
   AND t.table_name = i.table_name;
pause

CREATE INDEX my_mv_i1 ON my_mv (table_rowid);
pause
CREATE INDEX my_mv_i2 ON my_mv (index_rowid);
pause

EXEC DBMS_STATS.gather_table_stats(ownname=>'IFERNANDEZ',tabname=>'MY_MV');
pause

VARIABLE index_type VARCHAR2(27);
pause

EXEC :index_type := 'BITMAP';
pause

ALTER SESSION SET statistics_level=ALL;
SET AUTOTRACE on statistics
SET LINESIZE 160
SET TAB off
SET PAGESIZE 1000
pause

COLUMN owner FORMAT a20
COLUMN table_name FORMAT a40
COLUMN tablespace_name FORMAT a20
pause

EXEC :index_type := 'BITMAP';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Repeat the query again */
/
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

/* Use the result cache */
pause

SELECT          /*+ RESULT_CACHE */
       DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* Repeat the query again */
/
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

/* Repeat the query again for different values of index_type */

select   distinct index_type,
                  count(*)
    from my_indexes
group by index_type
order by 2;
pause

SET linesize 132
SET pagesize 10000
COLUMN owner 
EXEC :index_type := 'DOMAIN';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

EXEC :index_type := 'FUNCTION-BASED NORMAL';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause


EXEC :index_type := 'IOT - TOP';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause


EXEC :index_type := 'LOB';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause


EXEC :index_type := 'NORMAL';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

/* test whether commit on refresh is working */
pause

drop table t1;

create table t1 as select * from my_tables t where (owner, table_name) in
(select table_owner, table_name from my_indexes where index_type='BITMAP');

update t1 set table_name='XXX'||table_name;

commit;

drop table t2;

create table t2 as select * from my_indexes i where index_type='BITMAP';

update t2 set table_name='XXX'||table_name;

update t2 set index_name='XXX'||index_name;

commit;
pause

/* insert into my_tables and my_indexes */

insert into my_tables select * from t1;
pause

insert into my_indexes select * from t2;
pause

commit;
pause

/* test whether commit on refresh is working */
pause

EXEC :index_type := 'BITMAP';
pause

SELECT DISTINCT my_tables.owner,
                my_tables.table_name,
                my_tables.tablespace_name
           FROM my_tables, my_indexes
          WHERE my_tables.owner = my_indexes.table_owner
            AND my_tables.table_name = my_indexes.table_name
            AND my_indexes.index_type = :index_type;
pause

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (NULL, NULL, 'TYPICAL IOSTATS LAST'));
pause

exit