Hi,
I have recently got some sql statements that is not performing well.
select * from v where a=? and b not in(,,,,....) and c =? and rownum<-100
where v is a view.
Original sql statement is similar to above statement. From explain plan, it seems that a table partition(used to create view v) is not using the index. The index is
GLOBAL NON PARTITIONED.
The partition (used by v) got only half a million record but the total records in that table is at least 8 million.
When i directly queried that partitioned table(table as a whole and not just a partition), the index is used but when i performed same on the partition, full table scan is being performed.
According to an article, a full table scan is likely to occur if we are selecting around 20 % of rows in a table.
So, i have come to the conclusion that since the result set will be around 20 % of the records in that partition, it is performing full table scan instead of using INDEX.
But, when a
local partitioned index is created, for some reason ORACLE optimizer is using LOCAL partitioned index and not performing full table scan.
Can you please tell me why the optimizer didnt use global non partitioned index when a sql statement was run against a partition but used local partitioned index when executed same query on same partition?
With a global non-partitioned index, the database first searches the index for entries matching your search. Then filters these to just those in the partition you're interested in.
The key point here is there could be many entries in other partitions that satisfy your where clause.
Using your example:
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')
);
INSERT INTO TEST
VALUES(8,'A','Y',221);
INSERT INTO TEST
VALUES(9,'A','Y',221);
INSERT INTO TEST
VALUES(10,'A','Y',221);
INSERT INTO TEST
VALUES(11,'B','Y',221);
INSERT INTO TEST
VALUES(12,'B','Y',221);
commit;
exec dbms_stats.gather_table_stats ( user, 'test' ) ;
set serveroutput off
alter session set statistics_level = all;
CREATE INDEX IDX_TEST ON TEST(pk);
SELECT * FROM TEST WHERE MF='A' AND pk>0;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3 |
|* 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TEST | 1 | 3 | 1 | 1 | 3 |
|* 2 | INDEX RANGE SCAN | IDX_TEST | 1 | 5 | | | 5 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MF"='A')
2 - access("PK">0)Notice how the plan reads 5 rows from the index (A-rows for line 2). Then filters this down to the three in the relevant partition at line 1.
With a local index, the database can read
ONLY those entries in the partition you're accessing.
Continuing the example:
DROP INDEX IDX_TEST;
CREATE INDEX IDX_TEST ON TEST(pk) LOCAL;
SELECT * FROM TEST WHERE MF='A' AND pk>0;
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +PARTITION'));
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 3 |
| 1 | PARTITION LIST SINGLE | | 1 | 3 | 1 | 1 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TEST | 1 | 3 | 1 | 1 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_TEST | 1 | 3 | 1 | 1 | 3 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PK">0) See how the plan only ever reads three rows? It's already found
exactly the rows it needs. No need for further filtering.
So with a global index, the database is (potentially) reading entries for every partition. Then throwing all but those for the partition you want away. At some point this is more work than reading all the rows in one partition and discarding the ones you don't need.
In your real query the optimizer has decided you've reached that point where a single partition full scan is cheaper than using the global index. But with a local index, it can narrow down its search to just one partition. Which it's decided is cheaper than a full scan.
For more on how the optimizer decides on using an index vs. full table scan, watch this video series I created:
https://www.youtube.com/watch?v=Rlgb7LwOiHk&list=PL78V83xV2fYlLA-bjMU2ZvUKQOZNrqLEa&index=3