select * from test
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
CREATE TABLE TEST
(pk number,
MF VARCHAR2(10),
COLOR VARCHAR2(10),
WEIGHT NUMBER)
PARTITION BY LIST(MF)
(PARTITION MF_A
VALUES('A'),
PARTITION MF_B
VALUES('B')
)
Table created.
INSERT INTO TEST
VALUES(1,'A','R',2)
1 row(s) inserted.
SELECT * FROM TEST WHERE MF='A'
PK | MF | COLOR | WEIGHT | 1 | A | R | 2 |
---|
INSERT INTO TEST
VALUES(2,'A','R',2)
1 row(s) inserted.
CREATE TABLE TEST
(pk number,
MF VARCHAR2(10),
COLOR VARCHAR2(10),
WEIGHT NUMBER)
PARTITION BY LIST(MF)
(PARTITION MF_A
VALUES('A'),
PARTITION MF_B
VALUES('B')
)
ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
INSERT INTO TEST
VALUES(3,'A','R',2)
SELECT * FROM TEST WHERE MF='A'
ORA-00933: SQL command not properly endedMore Details: https://docs.oracle.com/error-help/db/ora-00933
INSERT INTO TEST
VALUES(3,'A','R',2)
1 row(s) inserted.
INSERT INTO TEST
VALUES(4,'A','Y',20)
1 row(s) inserted.
INSERT INTO TEST
VALUES(5,'A','Y',21)
1 row(s) inserted.
INSERT INTO TEST
VALUES(6,'A','Y',221)
1 row(s) inserted.
INSERT INTO TEST
VALUES(7,'A','Y',221)
1 row(s) inserted.
CREATE TABLE TEST
(pk number,
MF VARCHAR2(10),
COLOR VARCHAR2(10),
WEIGHT NUMBER)
PARTITION BY LIST(MF)
(PARTITION MF_A
VALUES('A'),
PARTITION MF_B
VALUES('B')
)
ORA-00955: name is already used by an existing objectMore Details: https://docs.oracle.com/error-help/db/ora-00955
INSERT INTO TEST
VALUES(8,'A','Y',221)
SELECT * FROM TEST WHERE MF='A'
ORA-00933: SQL command not properly endedMore Details: https://docs.oracle.com/error-help/db/ora-00933
INSERT INTO TEST
VALUES(8,'A','Y',221)
1 row(s) inserted.
INSERT INTO TEST
VALUES(9,'A','Y',221)
1 row(s) inserted.
INSERT INTO TEST
VALUES(10,'A','Y',221)
1 row(s) inserted.
INSERT INTO TEST
VALUES(11,'B','Y',221)
1 row(s) inserted.
INSERT INTO TEST
VALUES(12,'B','Y',221)
1 row(s) inserted.
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='A' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Error: cannot fetch last explain plan from PLAN_TABLE |
---|
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Error: cannot fetch last explain plan from PLAN_TABLE |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='A' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
CREATE INDEX IDX_TEST ON TEST(pk)
Index created.
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='A' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Error: cannot fetch last explain plan from PLAN_TABLE |
---|
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Error: cannot fetch last explain plan from PLAN_TABLE |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='A' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 4 | 160 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='B' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 4 | 160 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE MF='B' AND pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 4 | 160 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 4 | 160 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST partition mf_a WHERE pk>0
ORA-00933: SQL command not properly endedMore Details: https://docs.oracle.com/error-help/db/ora-00933
EXPLAIN PLAN FOR SELECT * FROM TEST partition mf_a WHERE pk>0
ORA-00933: SQL command not properly endedMore Details: https://docs.oracle.com/error-help/db/ora-00933
EXPLAIN PLAN FOR SELECT * FROM TEST partition MF_A WHERE pk>0
ORA-00933: SQL command not properly endedMore Details: https://docs.oracle.com/error-help/db/ora-00933
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 4170016888 | ------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 12 | 480 | 5 (0)| 00:00:01 | | | | | 1 | PARTITION LIST ALL| | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | |* 2 | TABLE ACCESS FULL| TEST | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | ------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 4170016888 | ------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 12 | 480 | 5 (0)| 00:00:01 | | | | | 1 | PARTITION LIST ALL| | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | |* 2 | TABLE ACCESS FULL| TEST | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | ------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 2 | TABLE ACCESS FULL | TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
DROP INDEX IDX_TEST
Index dropped.
CREATE INDEX IDX_TEST ON TEST(pk) LOCAL
Index created.
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 2 | TABLE ACCESS FULL | TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 2 | TABLE ACCESS FULL | TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 4170016888 | ------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 12 | 480 | 5 (0)| 00:00:01 | | | | | 1 | PARTITION LIST ALL| | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | |* 2 | TABLE ACCESS FULL| TEST | 12 | 480 | 5 (0)| 00:00:01 | 1 | 2 | | ------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 2025021737 | ----------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ----------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE | | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | | 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | | 1 (0)| 00:00:01 | 2 | 2 | | ----------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - access("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
DROP INDEX IDX_TEST
Index dropped.
CREATE INDEX IDX_TEST ON TEST(pk)
Index created.
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='B'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 2 | 80 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | |* 2 | TABLE ACCESS FULL | TEST | 2 | 80 | 3 (0)| 00:00:01 | 2 | 2 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|
EXPLAIN PLAN FOR SELECT * FROM TEST WHERE pk>0 AND MF='A'
Statement processed.
select * from TABLE(DBMS_XPLAN.display())
PLAN_TABLE_OUTPUT | Plan hash value: 213508695 | ---------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | | ---------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 10 | 400 | 3 (0)| 00:00:01 | | | | | 1 | PARTITION LIST SINGLE| | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | |* 2 | TABLE ACCESS FULL | TEST | 10 | 400 | 3 (0)| 00:00:01 | 1 | 1 | | ---------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 2 - filter("PK">0) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
---|