The Start Section of 'Sparse Index'
DROP TABLE t
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
CREATE TABLE t
( key NUMBER NOT NULL
, status VARCHAR2(1)
, other VARCHAR2(1000)
, CONSTRAINT t_pk PRIMARY KEY(key)
)
Table created.
Using a hint 'Append' to quickly insert some data to table 't'
INSERT /*+ APPEND */ INTO t
SELECT rownum
, CASE WHEN rownum <= 1e5-42 THEN NULL /*common status*/
WHEN rownum <= 1e5-10 THEN 'A'
ELSE 'R'
END CASE
, DBMS_RANDOM.string ('p', 6)
FROM dual
CONNECT BY level <= 1e5
100000 row(s) inserted.
CREATE INDEX t_status ON t (status)
Index created.
EXEC SYS.DBMS_STATS.gather_table_stats(ownname => null, tabname => 'T', method_opt => 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 status')
Statement processed.
SELECT status, COUNT(*)
FROM t
GROUP BY status
| STATUS | COUNT(*) | A | 32 | R | 10 | - | 99958 |
|---|
SELECT index_name, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T'
| INDEX_NAME | NUM_ROWS | LEAF_BLOCKS | T_PK | 100000 | 187 | T_STATUS | 42 | 1 |
|---|
CREATE INDEX t_status2 ON t (status, other)
Index created.
SELECT index_name, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T' ORDER BY 1
| INDEX_NAME | NUM_ROWS | LEAF_BLOCKS | T_PK | 100000 | 187 | T_STATUS | 42 | 1 | T_STATUS2 | 100000 | 266 |
|---|
SELECT status, COUNT(*)
FROM t
WHERE status != 'A'
GROUP BY status
| STATUS | COUNT(*) | R | 10 |
|---|
The End Section of 'Sparse Index'
SELECT status, COUNT(*)
FROM t
WHERE status != 'A' OR status IS NULL
GROUP BY status
| STATUS | COUNT(*) | R | 10 | - | 99958 |
|---|
The Start Section of 'Function-Based Index'
DROP TABLE t1
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
CREATE TABLE t1
( key NUMBER NOT NULL
, status VARCHAR2(1) NOT NULL
, other VARCHAR2(1000)
, CONSTRAINT t1_pk PRIMARY KEY(key)
)
Table created.
INSERT /*+ APPEND */ INTO t1
SELECT rownum
, CASE WHEN rownum <= 1e4-42 THEN 'C'
WHEN rownum <= 1e4-10 THEN 'A'
ELSE 'R' END CASE
, DBMS_RANDOM.string ('p', 6)
FROM dual
CONNECT BY level <= 1e4
10000 row(s) inserted.
EXEC SYS.DBMS_STATS.gather_table_stats(ownname => null, tabname => 'T1', method_opt=>'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 254 status')
Statement processed.
SELECT status, COUNT(*)
FROM t1
GROUP BY status
| STATUS | COUNT(*) | C | 9958 | A | 32 | R | 10 |
|---|
CREATE INDEX t1_status ON t1 (status)
Index created.
CREATE INDEX t1_status_fn ON t1 (DECODE(status,'C',NULL,status))
Index created.
SELECT index_name, index_type, num_rows, leaf_blocks
from user_indexes WHERE table_name = 'T1' ORDER BY 1
| INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | T1_PK | NORMAL | 10000 | 18 | T1_STATUS | NORMAL | 10000 | 19 | T1_STATUS_FN | FUNCTION-BASED NORMAL | 42 | 1 |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='C'
| COUNT(OTHER) | 9958 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 7q73d4bnfytqp, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | status='C' | Plan hash value: 3724264953 | ------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 27 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 27 | | |* 2 | TABLE ACCESS FULL| T1 | 1 | 9958 | 9958 |00:00:00.01 | 27 | | ------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("STATUS"='C') |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='R'
| COUNT(OTHER) | 10 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 79yuv5qxzvbg6, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | status='R' | Plan hash value: 2131837856 | --------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | | --------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 | 1 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 10 | 10 |00:00:00.01 | 3 | 1 | | |* 3 | INDEX RANGE SCAN | T1_STATUS | 1 | 10 | 10 |00:00:00.01 | 2 | 1 | | --------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("STATUS"='R') |
|---|
ALTER INDEX t1_status INVISIBLE
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE status='R'
| COUNT(OTHER) | 10 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 79yuv5qxzvbg6, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | status='R' | Plan hash value: 3724264953 | ------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | ------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 27 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 27 | | |* 2 | TABLE ACCESS FULL| T1 | 1 | 10 | 10 |00:00:00.01 | 27 | | ------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("STATUS"='R') |
|---|
ALTER INDEX t1_status VISIBLE
Statement processed.
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE DECODE(status,'C',null,status) = 'R'
| COUNT(OTHER) | 10 |
|---|
The End Section of 'Function-Based Index'
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 8axft6m4xxmxg, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | DECODE(status,'C',null,status) = 'R' | Plan hash value: 847448750 | --------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | --------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 100 | 10 |00:00:00.01 | 2 | | |* 3 | INDEX RANGE SCAN | T1_STATUS_FN | 1 | 40 | 10 |00:00:00.01 | 1 | | --------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T1"."SYS_NC00004$"='R') |
|---|
The Start Section of 'Virtual Column'
SELECT * FROM user_stat_extensions WHERE table_name = 'T1'
| TABLE_NAME | EXTENSION_NAME | EXTENSION | CREATOR | DROPPABLE | T1 | SYS_NC00004$ | (DECODE("STATUS",'C',NULL,"STATUS")) | SYSTEM | NO |
|---|
SELECT SYS_NC00004$, COUNT(*) FROM t1 group by SYS_NC00004$
| SYS_NC00004$ | COUNT(*) | A | 32 | R | 10 | - | 9958 |
|---|
ALTER TABLE t1 ADD virtual_status VARCHAR2(1) INVISIBLE
GENERATED ALWAYS AS (DECODE(status, 'C', null, status))
ORA-54015: Duplicate column expression was specifiedMore Details: https://docs.oracle.com/error-help/db/ora-54015
DROP INDEX t1_status_fn
Index dropped.
ALTER TABLE t1 ADD virtual_status VARCHAR2(1) INVISIBLE
GENERATED ALWAYS AS (DECODE(status, 'C', null, status))
Table altered.
CREATE INDEX t1_status_virtual ON t1 (virtual_status)
Index created.
SELECT index_name, index_type, num_rows, leaf_blocks FROM user_indexes WHERE table_name = 'T1' ORDER BY 1
| INDEX_NAME | INDEX_TYPE | NUM_ROWS | LEAF_BLOCKS | T1_PK | NORMAL | 10000 | 18 | T1_STATUS | NORMAL | 10000 | 19 | T1_STATUS_VIRTUAL | FUNCTION-BASED NORMAL | 42 | 1 |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE virtual_status = 'R'
| COUNT(OTHER) | 10 |
|---|
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 759hpfwkrv3z3, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | virtual_status = 'R' | Plan hash value: 2882731673 | -------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | -------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 100 | 10 |00:00:00.01 | 2 | | |* 3 | INDEX RANGE SCAN | T1_STATUS_VIRTUAL | 1 | 40 | 10 |00:00:00.01 | 1 | | -------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("VIRTUAL_STATUS"='R') |
|---|
SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE DECODE(status,'C',null,status) = 'R'
| COUNT(OTHER) | 10 |
|---|
The End Section of 'Virtual Column'
SELECT * FROM table(DBMS_XPLAN.display_cursor(:LIVESQL_LAST_SQL_ID, NULL, 'ALLSTATS LAST'))
| PLAN_TABLE_OUTPUT | SQL_ID 8axft6m4xxmxg, child number 0 | ------------------------------------- | SELECT /*+ gather_plan_statistics */ COUNT(other) FROM t1 WHERE | DECODE(status,'C',null,status) = 'R' | Plan hash value: 2882731673 | -------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | -------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 1 | 100 | 10 |00:00:00.01 | 2 | | |* 3 | INDEX RANGE SCAN | T1_STATUS_VIRTUAL | 1 | 40 | 10 |00:00:00.01 | 1 | | -------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("T1"."VIRTUAL_STATUS"='R') |
|---|